11 DAX Fundamentals (Data Analysis Expressions)

What Is DAX?

DAX (Data Analysis Expressions) is the formula language of Power BI. It is a collection of functions, operators, and constants that you write to define calculations in your data model. DAX is what transforms a collection of connected tables into an analytical engine capable of answering complex business questions dynamically, across any combination of filters and slicers that a report consumer applies.

Every measure, every calculated column, and every calculated table in Power BI is defined using DAX. Learning DAX is the single most impactful skill you can develop as a Power BI practitioner. A report built without DAX is limited to what the source data already contains. A report powered by DAX can answer questions the source data alone cannot: year-to-date comparisons, percentage contributions, running totals, dynamic rankings, and much more.

Where You Write DAX

DAX formulas are written in the formula bar at the top of the Power BI Desktop canvas. The formula bar appears when you are creating or editing a measure or calculated column. As you type, Power BI’s IntelliSense suggests function names, table names, and column names automatically, helping you write formulas faster and with fewer errors.

[Insert screenshot of the Power BI Desktop formula bar showing a DAX measure being typed with IntelliSense suggestions appearing in a dropdown below the formula bar]

flowchart LR
    A[DAX Formula] --> B{Used As}
    B --> C[Measure\nDynamic aggregation\nChanges with filters]
    B --> D[Calculated Column\nRow-by-row computation\nStored in model]
    B --> E[Calculated Table\nNew table from DAX\nStored in model]
    C --> F[Report Visuals\nSlicers, Filters]
    D --> F
    E --> F
    classDef default fill:#004466,color:#ffffff,stroke:#ffcc00,stroke-width:3px,rx:10px,ry:10px;

11.1 Understanding DAX

11.1.1 DAX Syntax and Structure

How a DAX Formula Is Written

Every DAX formula follows the same basic structure. For a measure, you write:

Code

Measure Name = DAX Expression

For a calculated column, you write the formula in the context of the table it belongs to:

Code

Column Name = DAX Expression

DAX expressions can be simple single-function calls or deeply nested combinations of multiple functions. Regardless of complexity, the structure is always: a name on the left of the equals sign, and the formula on the right.

Example:

Code

Total Sales = SUM(FactSales[Revenue])

Here, Total Sales is the measure name, SUM is the DAX function, FactSales is the table name, and Revenue is the column being summed. The table and column are always referenced together inside square brackets.

Referencing Tables and Columns

In DAX, columns are always referenced using the format TableName[ColumnName]. The table name comes first, followed by the column name in square brackets. Measures are referenced using just [MeasureName] without a table name prefix, though including the table name is also valid and improves readability.

Code

-- Referencing a column
FactSales[Revenue]

-- Referencing a measure
[Total Sales]

-- Using both in a formula
Profit Margin % = DIVIDE([Total Profit], [Total Sales])

Comments in DAX

DAX supports two styles of comments, which are useful for documenting complex formulas:

Code

-- This is a single-line comment

/* This is a
   multi-line comment */

Gross Profit =
    -- Revenue minus cost of goods sold
    SUM(FactSales[Revenue]) - SUM(FactSales[COGS])

Format Long Formulas for Readability

For measures that span multiple functions, break the formula across multiple lines and use indentation. Power BI’s formula bar accepts multi-line input. A well-formatted formula is far easier to debug and maintain than a single long line.

Code

Sales YTD =
    CALCULATE(
        SUM(FactSales[Revenue]),
        DATESYTD(DimDate[Date])
    )

11.1.2 Filter Context and Row Context

The Two Types of Context in DAX

Context is the most important concept in DAX. It is what makes DAX dynamic. Every DAX calculation does not produce a fixed result. It produces a result that depends on the context in which it is evaluated. There are two types of context: filter context and row context. Understanding both is the foundation for writing correct DAX.

Filter Context

What Is Filter Context?

Filter context is the set of filters currently active when a DAX expression is evaluated. These filters come from slicers on the report page, filters in the Filters pane, values selected in other visuals, row and column headers in a matrix, and any CALCULATE-based filter modifications in the formula itself.

When you place a measure in a visual, Power BI evaluates that measure once for every cell in the visual, each time applying a different filter context. In a matrix showing Revenue by Year and Region, the measure below is evaluated once for each Year/Region combination, with the filter context for each cell restricting the data to that specific year and region.

Code

[Total Sales]

Example: Imagine a card visual with no filters. The measure above returns the grand total of all revenue. The same measure placed in a bar chart grouped by Region returns a different value for each bar, because the filter context for each bar restricts data to one region at a time.

Row Context

What Is Row Context?

Row context exists when DAX evaluates a formula row by row. This happens automatically in calculated columns, where the formula is evaluated once for each row in the table and has access to the values in that specific row.

Example: In a calculated column that computes net revenue after discount:

Code

Net Revenue = FactSales[Revenue] * (1 - FactSales[DiscountRate])

DAX evaluates this formula for each row in FactSales. When evaluating row 1, FactSales[Revenue] returns the revenue value from row 1 specifically. When evaluating row 2, it returns the revenue from row 2. This row-by-row awareness is the row context.

Row context does not exist in measures by default. Measures operate in filter context. The iterator functions (SUMX, AVERAGEX, etc.) are the way to introduce row context inside a measure.

Filter Context and Row Context Are Independent

A common source of confusion in DAX is mixing up the two contexts. Measures operate in filter context. Calculated columns operate in row context. When you write a measure, it does not automatically know “which row” it is on. It knows only the current set of active filters. Always ask: is this formula being evaluated row by row (calculated column or iterator) or across a filtered set (measure)?

11.2 Core Functions

11.2.1 Basic Aggregation Functions

Aggregation Functions Overview

Aggregation functions are the most commonly used DAX functions. They take a column of values and return a single summary number. All aggregation functions operate in the current filter context, meaning the result changes automatically as filters and slicers are applied.

SUM

SUM adds all numeric values in a column.

Code

Total Revenue = SUM(FactSales[Revenue])

Practical example: A bar chart grouped by Region uses the measure above to display the sum of all revenue transactions for each region. When a Year slicer is set to 2025, only 2025 revenue rows are included in the sum.

AVERAGE

AVERAGE returns the arithmetic mean of all numeric values in a column.

Code

Average Order Value = AVERAGE(FactSales[OrderAmount])

Practical example: Placed in a card visual, this shows the average transaction size across all orders. In a matrix broken down by product category and month, it shows the average order amount for each category/month combination.

COUNT and COUNTA

COUNT counts the number of rows in a column that contain a numeric value. COUNTA counts rows containing any non-blank value (numbers, text, dates).

Code

Number of Orders = COUNT(FactSales[OrderID])

Number of Customers = COUNTA(DimCustomer[CustomerName])

Practical example: The Number of Orders measure placed in a visual alongside Total Revenue allows the reader to see both the volume and the value of transactions side by side.

DISTINCTCOUNT

DISTINCTCOUNT counts the number of unique values in a column, ignoring duplicates.

Code

Unique Customers = DISTINCTCOUNT(FactSales[CustomerID])

Practical example: A fact table may have thousands of rows, with the same CustomerID appearing many times, once per order. The measure above returns the number of individual customers who made at least one purchase, not the total number of orders.

MIN and MAX

MIN returns the smallest value in a column. MAX returns the largest.

Code

Earliest Order Date = MIN(FactSales[OrderDate])

Highest Single Sale = MAX(FactSales[Revenue])

Practical example: The Highest Single Sale measure placed in a card visual with a Region slicer shows the largest single transaction made in the selected region.

11.2.2 Logical Functions

IF evaluates a condition and returns one value if the condition is true and another if it is false.

Code

IF(<condition>, <value_if_true>, <value_if_false>)

Code

Performance Flag =
    IF(
        [Total Sales] >= [Sales Target],
        "On Target",
        "Below Target"
    )

Practical example: This measure returns “On Target” for regions or products meeting the sales target and “Below Target” for those that are not. Used in a table visual, it creates a readable status column alongside the numeric values.

AND and OR

AND returns TRUE only if all conditions are true. OR returns TRUE if at least one condition is true. Both can also be written using the && and || operators respectively.

Code

High Value Customer =
    IF(
        [Total Sales] > 100000 && [Number of Orders] > 10,
        "High Value",
        "Standard"
    )

Practical example: This classifies customers who have both high total spend and high order frequency as “High Value”, requiring both conditions to be met simultaneously.

SWITCH

SWITCH evaluates an expression against a list of values and returns the result for the first match. It is a cleaner alternative to nested IF statements when multiple conditions map to different outcomes.

Code

SWITCH(<expression>, <value1>, <result1>, <value2>, <result2>, ..., <else_result>)

Code

Region Label =
    SWITCH(
        DimRegion[RegionCode],
        "N", "North",
        "S", "South",
        "E", "East",
        "W", "West",
        "Unknown"
    )

Practical example: A source system stores regions as single-character codes. This calculated column converts each code into its full descriptive name. “Unknown” serves as the fallback for any code not in the list.

SWITCH can also use TRUE() as the expression to evaluate independent conditions, similar to a series of IF/ELSE statements:

Code

Sales Band =
    SWITCH(
        TRUE(),
        [Total Sales] >= 1000000, "Platinum",
        [Total Sales] >= 500000,  "Gold",
        [Total Sales] >= 100000,  "Silver",
        "Bronze"
    )

11.3 Working with Text and Dates

11.3.1 Text Functions

CONCATENATE and the Ampersand Operator

CONCATENATE joins two text strings into one. The & operator achieves the same result and is more flexible, as it can join more than two strings in a single expression.

Code

Full Name =
    DimCustomer[FirstName] & " " & DimCustomer[LastName]

Practical example: A customer table stores first and last names separately. This calculated column combines them with a space in between for display in report visuals.

LEFT, RIGHT, and MID

LEFT extracts a defined number of characters from the start of a string. RIGHT extracts from the end. MID extracts from a specified position within the string.

Code

Country Code  = LEFT(DimCustomer[PhoneNumber], 3)

File Extension = RIGHT(DimFiles[FileName], 4)

Month Abbrev  = MID(DimDate[MonthName], 1, 3)

Practical example: A phone number column stores values like “+91-9876543210”. The formula below extracts “+91”, the country dialling code, which can be used to derive a customer’s country.

Code

Country Code = LEFT(DimCustomer[PhoneNumber], 3)

LEN

LEN returns the number of characters in a text string.

Code

Description Length = LEN(DimProduct[ProductDescription])

Practical example: Used in a data quality check to identify product descriptions that are too short (below a minimum character count) or too long (exceeding a maximum).

UPPER, LOWER, and TRIM

UPPER converts text to all uppercase. LOWER converts to all lowercase. TRIM removes leading and trailing spaces from a text string.

Code

Standardized City =
    UPPER(TRIM(DimCustomer[City]))

Practical example: A customer table imported from multiple systems has city names in mixed cases and with stray spaces. This calculated column standardizes every city name to uppercase with no surrounding spaces, ensuring that “hyderabad”, ” Hyderabad “, and”HYDERABAD” all group together correctly in visuals.

FORMAT

FORMAT converts a value to a text string using a specified format pattern. It is used to display numbers, dates, and other values in a specific human-readable style.

Code

FORMAT(<value>, <format_string>)

Code

Revenue Display  = FORMAT([Total Sales], "₹ #,##0.00")

Month Year Label = FORMAT(DimDate[Date], "MMM YYYY")

Practical example: The Month Year Label formula converts a date column into strings like “Jan 2025”, “Feb 2025”, and so on. These labels can then be used as axis categories in visuals where the full date format would be too detailed.

Common format strings include:

Format String	Example Output
`"#,##0"`	1,250,000
`"#,##0.00"`	1,250,000.00
`"0%"`	75%
`"0.00%"`	74.83%
`"DD/MM/YYYY"`	09/04/2026
`"MMM YYYY"`	Apr 2026
`"MMMM"`	April

11.3.2 Date and Time Functions

TODAY and NOW

TODAY returns the current date as a Date value with no time component. NOW returns the current date and time.

Code

Days Since Last Order =
    DATEDIFF(MAX(FactSales[OrderDate]), TODAY(), DAY)

Practical example: This measure calculates how many days have passed since the most recent order. Placed in a card visual, it gives the operations team an at-a-glance indicator of recency.

YEAR, MONTH, and DAY

YEAR, MONTH, and DAY extract the respective component from a date value as a whole number.

Code

Order Year         = YEAR(FactSales[OrderDate])

Order Month Number = MONTH(FactSales[OrderDate])

Order Day          = DAY(FactSales[OrderDate])

Practical example: These are typically used in calculated columns to add year, month, and day number columns to a fact table or Date dimension when the source data does not already include them.

DATEDIFF

DATEDIFF calculates the difference between two dates in a specified unit: DAY, WEEK, MONTH, QUARTER, or YEAR.

Code

DATEDIFF(<start_date>, <end_date>, <interval>)

Code

Days to Ship =
    DATEDIFF(FactSales[OrderDate], FactSales[ShipDate], DAY)

Customer Tenure Years =
    DATEDIFF(DimCustomer[JoinDate], TODAY(), YEAR)

Practical example: The Days to Ship formula calculates the number of days between order placement and shipment for each transaction. A histogram of this calculated column immediately reveals whether shipping times are consistent or have high variance.

DATEADD

DATEADD shifts a column of dates by a specified number of intervals forward or backward in time. It is commonly used inside CALCULATE to shift the filter context to a different time period.

Code

DATEADD(<dates_column>, <number_of_intervals>, <interval>)

Code

Sales Same Period Last Year =
    CALCULATE(
        [Total Sales],
        DATEADD(DimDate[Date], -1, YEAR)
    )

Practical example: This measure returns the total sales for the same period one year ago. When used alongside the Total Sales measure in a line chart, the two lines show the current year and the prior year trend together, enabling direct year-over-year comparison.

11.4 Advanced Function Techniques

11.4.1 The CALCULATE Function

Why CALCULATE Is the Most Important DAX Function

CALCULATE is the function that makes DAX uniquely powerful. It evaluates a DAX expression in a modified filter context that you define. In plain terms: CALCULATE lets you override, extend, remove, or completely replace the filters that are currently active when a measure is evaluated. Every advanced DAX calculation ultimately relies on CALCULATE.

Code

CALCULATE(<expression>, <filter1>, <filter2>, ...)

The first argument is the expression to evaluate, usually a measure. The remaining arguments are filter conditions that modify the filter context for that evaluation only. After CALCULATE finishes, the original filter context is restored.

CALCULATE: Filtering to a Specific Value

Code

North Region Sales =
    CALCULATE(
        [Total Sales],
        DimRegion[RegionName] = "North"
    )

Practical example: This measure always returns the total sales for the North region only, regardless of what region is selected in a slicer. Placed in a card visual alongside the Total Sales measure (which reflects the current slicer selection), it lets readers compare the selected region’s performance against the fixed North region benchmark.

CALCULATE: Removing Filters with ALL

Wrapping a table or column in ALL inside CALCULATE removes all filters from that table or column, allowing the expression to evaluate across the full dataset regardless of the active filter context.

Code

% of Total Sales =
    DIVIDE(
        [Total Sales],
        CALCULATE([Total Sales], ALL(FactSales))
    )

Practical example: In a matrix showing sales by Region, the Total Sales measure gives the sales for each region. The formula below ignores the region filter and returns the grand total:

Code

CALCULATE([Total Sales], ALL(FactSales))

Dividing the two gives the percentage contribution of each region to the total, which updates correctly as other filters such as Year or Product are applied.

CALCULATE: Year-to-Date with DATESYTD

Code

Revenue YTD =
    CALCULATE(
        [Total Sales],
        DATESYTD(DimDate[Date])
    )

Practical example: DATESYTD (Dates Year To Date) returns all dates from the first day of the current year up to the current date in context. Wrapped in CALCULATE, this shifts the filter context to include all dates from the year start, producing a running cumulative total that resets at the beginning of each year. Placed alongside the Total Sales measure in a line chart by month, it creates the classic YTD revenue trend chart.

CALCULATE: Multiple Filter Arguments

CALCULATE accepts multiple filter arguments, all of which are applied simultaneously using AND logic.

Code

North Electronics Sales 2025 =
    CALCULATE(
        [Total Sales],
        DimRegion[RegionName]  = "North",
        DimProduct[Category]   = "Electronics",
        DimDate[Year]          = 2025
    )

Practical example: This measure returns total sales restricted to the North region, Electronics category, and the year 2025 simultaneously, regardless of what slicers or filters the report consumer has applied. Useful for hardcoded benchmark comparisons in a report.

CALCULATE Is Not Optional for Advanced DAX

Any DAX formula that needs to evaluate data in a different filter context than the one currently active requires CALCULATE. Time intelligence, percentage of total, same-period-last-year, running totals, and benchmarking measures all depend on CALCULATE. Mastering CALCULATE is the single most important step in progressing from basic to advanced Power BI development.

11.4.2 Iterator Functions

What Are Iterator Functions?

Iterator functions loop over every row in a table, evaluate an expression row by row (introducing row context), and then aggregate the row-level results into a single value. They follow a consistent naming pattern: the function name ends in X (SUMX, AVERAGEX, COUNTX, MAXX, MINX, RANKX). Every iterator takes two arguments: the table to iterate over, and the expression to evaluate for each row.

Code

SUMX(<table>, <expression>)

AVERAGEX(<table>, <expression>)

COUNTX(<table>, <expression>)

Iterators are essential when the calculation that needs to be aggregated does not exist as a ready-made column in the model.

SUMX

SUMX iterates over every row in a table, evaluates an expression for each row, and sums the row-level results.

Code

Total Net Revenue =
    SUMX(
        FactSales,
        FactSales[Quantity] * FactSales[UnitPrice] * (1 - FactSales[DiscountRate])
    )

Practical example: The data model has no pre-calculated Net Revenue column. This measure iterates over every row in FactSales, computes the net revenue for that row (Quantity × UnitPrice × (1 − Discount)), and sums those row-level results. The answer is correct because each row’s discount is applied before the sum, rather than applying an average discount to the total, which would produce a different and incorrect result.

Why not just use SUM? The formula below requires a Revenue column to already exist:

Code

SUM(FactSales[Revenue])

SUMX can compute row-level values on the fly during aggregation, making it more flexible for scenarios where the column does not exist in the source data.

AVERAGEX

AVERAGEX iterates over every row in a table, evaluates an expression for each row, and returns the average of the row-level results.

Code

Average Basket Size =
    AVERAGEX(
        VALUES(FactSales[OrderID]),
        CALCULATE(SUM(FactSales[Revenue]))
    )

Practical example: This measure calculates the average revenue per order. It iterates over each unique OrderID, calculates the total revenue for that order using CALCULATE, and then averages those per-order totals. The result is the true average basket size, which correctly accounts for orders with multiple line items, rather than simply averaging individual line item amounts.

COUNTX

COUNTX iterates over every row in a table, evaluates an expression for each row, and counts the number of rows where the expression returns a non-blank value.

Code

Orders with Discount =
    COUNTX(
        FactSales,
        IF(FactSales[DiscountRate] > 0, FactSales[OrderID], BLANK())
    )

Practical example: This measure counts only the orders where a discount was applied. For each row, the IF expression returns the OrderID if a discount exists and BLANK() otherwise. COUNTX counts only the non-blank results, effectively counting discounted orders. Compared against the total order count, this reveals what proportion of orders received a discount.

11.4.3 RELATED and RELATEDTABLE

Accessing Data Across Relationships

In a well-structured star schema, information about a transaction lives in the fact table, while descriptive attributes such as customer name, product category, and region live in dimension tables. When writing a calculated column in a fact table, you often need to bring in a value from a related dimension table. RELATED and RELATEDTABLE are the DAX functions that cross relationship boundaries to access data in other tables.

RELATED retrieves a single value from a related table by following an existing relationship. It is used in a calculated column on the “many” side of a relationship to look up a value from the “one” side.

Code

RELATED(<column>)

Code

-- In FactSales (the many side), look up ProductCategory from DimProduct (the one side)
Product Category = RELATED(DimProduct[Category])

Practical example: FactSales has a ProductID column that links to DimProduct. A calculated column using RELATED looks up the Category for each transaction’s ProductID and stores it directly on the FactSales row. This makes the category available for row-level logic without needing to navigate the relationship in a measure.

Code

-- Classify each transaction by its product's price tier
Price Tier =
    IF(
        RELATED(DimProduct[UnitPrice]) >= 5000,
        "Premium",
        "Standard"
    )

RELATEDTABLE

RELATEDTABLE returns an entire table of rows from a related table that match the current row context. It is the reverse of RELATED, used in a calculated column on the “one” side of a relationship to access all the matching rows on the “many” side.

Code

RELATEDTABLE(<table>)

Code

-- In DimCustomer (the one side), count how many orders each customer has placed in FactSales (the many side)
Order Count = COUNTROWS(RELATEDTABLE(FactSales))

Practical example: For each customer in DimCustomer, the formula above returns all rows in FactSales that belong to that customer. Wrapping this with COUNTROWS gives the total number of orders per customer, stored directly on the DimCustomer table. This column can then be used in slicers or as an axis category in a histogram of order frequency.

RELATED Works in Calculated Columns Only

RELATED and RELATEDTABLE require a row context to know which row they are working from, so they work in calculated columns only, not directly in measures. In measures, use CALCULATE with USERELATIONSHIP for relationship traversal, or use LOOKUPVALUE for value lookups that need to work within a filter context.

11.5 DAX Best Practices

11.5.1 DAX Best Practices

Write Measures, Not Calculated Columns, for Aggregations

Every aggregation (sum, count, average) should be a measure, not a calculated column. Measures are computed dynamically and respond to filter context, making them flexible and reusable across all visuals. Calculated columns that replicate what a measure could compute waste storage and cannot adapt to filter changes at report time.

Use DIVIDE Instead of the Division Operator

In DAX, dividing by zero with the / operator causes an error. The DIVIDE function handles division safely by returning BLANK() (or an optional alternate result) when the denominator is zero or blank, preventing error values from appearing in your visuals.

Code

-- Unsafe
Margin % = [Gross Profit] / [Total Revenue]

-- Safe
Margin % = DIVIDE([Gross Profit], [Total Revenue])

-- Safe with a custom fallback value
Margin % = DIVIDE([Gross Profit], [Total Revenue], 0)

Name Measures Clearly and Group Them in a Dedicated Table

Give measures descriptive names that communicate what they calculate and their unit where relevant. “Total Revenue (₹)”, “YTD Orders”, and “Gross Margin %” are immediately understandable. “Measure1” and “Calc2” are not.

Create a dedicated empty table in your model using Enter data in Power BI Desktop with no rows, and store all your measures there. This keeps measures separate from the data tables in the Data pane, making them easy to find and manage as the model grows.

Context Is Everything in DAX

Every DAX formula produces a result that is inseparable from the context in which it is evaluated. A measure that returns 1,000,000 in one visual might return 50,000 in another, not because the formula changed, but because the filter context changed. Always test a new measure across multiple visuals and filter states to confirm it behaves correctly in all scenarios, not just the one you built it in.

Summary

Concept	Description
DAX Foundations
DAX Syntax	Function-based language for analytical expressions in models
Calculated Columns	New columns evaluated row-by-row at refresh time
Measures	Aggregations evaluated in the filter context of the visual
Implicit vs Explicit Measures	Drag-drop aggregations versus measures defined deliberately
Context and Practice
Filter Context	The set of filters applied when a DAX expression is evaluated
Row Context	Per-row evaluation used inside calculated columns and iterators
Common Functions	SUM, AVERAGE, COUNT, COUNTROWS, DISTINCTCOUNT, and CALCULATE
Best Practices	Naming conventions, comments, and avoiding implicit measures