DAX (Data Analysis Expressions)
Power BI · DAX (Data Analysis Expressions)
Theory
DAX is the formula language of the Power BI model. You use it to write measures (calculations evaluated in the context of a visual), calculated columns (values computed row by row and stored in a table), calculated tables, and row-level security rules. It looks like Excel formulas, with functions such as SUM, AVERAGE and IF, but it works on whole tables and columns rather than cell references: a column is written Table[Column] and a measure [Measure].
The key idea is that a measure has no fixed answer. Total Sales = SUM(Sales[Amount]) returns ₹6,200 on a card, but placed in a table next to Region it returns ₹3,200 for North, ₹2,400 for West and ₹600 for South, because each row of the visual filters the Sales table first. Mastering DAX mostly means understanding this evaluation context and the CALCULATE function that changes it.
Why we use it
DAX turns a model into business answers: totals, ratios, year-to-date, rankings and comparisons that respond instantly to slicers. Without it you are limited to simple implicit sums.
When to use it
Use DAX for anything that must react to report filters or aggregate across rows. Use Power Query for cleaning and reshaping data before it loads. Prefer explicit measures over dragging numeric columns into visuals.
How it works
- Write a measure
- Visual applies its filters (row, slicers, page)
- Sales table is filtered to matching rows
- The expression aggregates the remaining rows
- More rows or totals in the visual? Yes: Repeat with that row's filters. No: Show the result.
- Each cell shows its own answer
Syntax
Measure: <Measure Name> = <expression>
Column: Table[Column] Measure reference: [Measure]
Home → New measure | Table tools → New column | Modeling → New table
Comments: // single line /* block */Example
Total Sales = SUM(Sales[Amount])
Total Qty = SUM(Sales[Qty])
Orders = DISTINCTCOUNT(Sales[OrderID])
// Card: [Total Sales] → ₹6,200
// Table visual by Sales[Region]:
// North ₹3,200 | West ₹2,400 | South ₹600Key points
- Measures aggregate data dynamically based on filter context
- Reference columns as Table[Column] and measures as [Measure]
- Time intelligence functions handle YTD, MTD and period comparisons
- Iterator functions like SUMX operate row-by-row before aggregating
- Format measures (currency, %) in Measure tools, not with FORMAT()