← All handbooks

DAX Handbook

Data Analysis Expressions end to end. DAX is easy to start and hard to master — the whole game is evaluation context and what CALCULATE does to it. Get those and everything else falls into place.

01 Measures vs calculated columns

Total Sales = SUMX(Sales, Sales[Qty] * Sales[Price])
Margin %   = DIVIDE([Profit], [Total Sales])   -- DIVIDE handles /0

02 Evaluation context

Two contexts. A measure is always evaluated inside some context.

• Filter context — the set of filters applied to the model for this cell: slicers, rows/columns of a visual, page filters, and CALCULATE filters. It restricts which rows are visible before the calc runs.
• Row context — “the current row.” Exists in calculated columns and inside iterators (SUMX, FILTER). It does not automatically filter the model.

03 CALCULATE — the heart of DAX

CALCULATE evaluates an expression in a modified filter context. It’s the only function that can change filter context — and it’s everywhere.

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

Sales LY     = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))
Sales Bikes  = CALCULATE([Total Sales], Product[Cat] = "Bikes")
Pct of Total = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Product)))

• Each filter argument replaces any existing filter on that column (it’s an override, not an intersect) — unless you wrap with KEEPFILTERS.
• A boolean filter like Product[Cat]="Bikes" is shorthand for FILTER(ALL(Product[Cat]), ...).
• Filter modifiers: ALL (remove filters), ALLEXCEPT, ALLSELECTED, REMOVEFILTERS, KEEPFILTERS, USERELATIONSHIP.

04 Context transition

When CALCULATE runs inside a row context, it performs context transition: the current row’s values become filter context. This is why a measure referenced inside an iterator “just works” per row.

-- a measure is implicitly wrapped in CALCULATE -> transition happens
Avg Cust Sales = AVERAGEX( VALUES(Customer[Id]), [Total Sales] )

For each customer id, context transition turns that row into a filter, so [Total Sales] recomputes for just that customer, then AVERAGEX averages the results.

05 Filter functions

• FILTER(table, condition) — returns a filtered table (iterator); use inside CALCULATE for complex conditions.
• ALL / ALLEXCEPT / ALLSELECTED / REMOVEFILTERS — clear or preserve filters (totals, % of total, ignoring slicers).
• VALUES / DISTINCT — unique values in current context. HASONEVALUE guards single-selection logic.
• SELECTEDVALUE(col, alt) — the one value in context (or fallback). RELATED / RELATEDTABLE — traverse relationships.
• TREATAS, KEEPFILTERS, CROSSFILTER for advanced filter control.

High-Value Sales = CALCULATE([Total Sales],
    FILTER(Sales, Sales[Qty] * Sales[Price] > 1000))

06 Iterators (the X functions)

Iterators evaluate an expression row by row over a table (creating row context), then aggregate.

SUMX(Sales, Sales[Qty]*Sales[Price])      -- row-level math then sum
AVERAGEX(VALUES(Date[Month]), [Total Sales]) -- avg of a measure per month
RANKX(ALL(Product), [Total Sales])           -- ranking
MAXX, MINX, COUNTX, CONCATENATEX

Use an iterator when the calculation needs a value computed per row before aggregating (e.g. weighted averages, row-level products) — you can’t do SUM(qty*price) directly because that column doesn’t exist.

07 Relationships in DAX

• Filters flow from the one side to the many side along active relationships. RELATED pulls a column from the one-side into a many-side row context.
• Only one active relationship per pair of tables; activate an inactive one for a calc with USERELATIONSHIP inside CALCULATE.
• CROSSFILTER changes filter direction (e.g. for bridge tables / many-to-many).
• Bidirectional filtering is powerful but risky (ambiguity, performance) — prefer single direction + CROSSFILTER where needed.

Sales by Ship Date = CALCULATE([Total Sales],
    USERELATIONSHIP(Sales[ShipDate], Dates[Date]))

08 Variables

YoY % =
VAR cur = [Total Sales]
VAR prev = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))
RETURN DIVIDE(cur - prev, prev)

• VAR evaluates once, in the context where it’s defined — improves readability and performance (no recomputation), and avoids subtle re-evaluation bugs.
• A variable is a constant within RETURN; later filter changes don’t alter an already-evaluated VAR.
• Use them to debug: RETURN a single VAR to inspect it.

09 Time intelligence

Requires a marked, contiguous Date table (one row per day) related to your fact’s date.

YTD     = TOTALYTD([Total Sales], Dates[Date])
MTD     = TOTALMTD([Total Sales], Dates[Date])
LY      = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))
Prev Mo = CALCULATE([Total Sales], DATEADD(Dates[Date], -1, MONTH))
Rolling 12M = CALCULATE([Total Sales],
    DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -12, MONTH))

• Core funcs: DATESYTD/QTD/MTD, SAMEPERIODLASTYEAR, DATEADD, PARALLELPERIOD, DATESINPERIOD, DATESBETWEEN.
• Fiscal years / 4-4-5 calendars: write custom date logic; built-ins assume standard calendar.

10 Patterns & recipes

% of total / parent

% of Grand Total = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED()))

Cumulative / running total

Running Total = CALCULATE([Total Sales],
    FILTER(ALLSELECTED(Dates[Date]), Dates[Date] <= MAX(Dates[Date])))

Dynamic ranking & top-N

Rank = RANKX(ALL(Product[Name]), [Total Sales], , DESC, DENSE)

Also: switch-based measure selector (SWITCH(TRUE(), ...)), virtual relationships with TREATAS, segmentation/banding, and budget-vs-actual.

11 Performance

• DAX runs on two engines: the fast Storage Engine (VertiPaq, cached, multithreaded) and the slower Formula Engine. Push work to SE; minimize FE row-by-row.
• Prefer simple aggregations and measures over heavy iterators on big tables. Use VAR to compute once.
• Avoid calculated columns on huge fact tables (RAM); model a star schema; reduce cardinality.
• Profile with Performance Analyzer in Desktop and DAX Studio (server timings, query plans).

12 Interview drills

• Row context vs filter context — define and give an example of each.
• What does CALCULATE do; how do filter arguments combine; KEEPFILTERS?
• Explain context transition; why a measure inside a calc column behaves oddly.
• ALL vs ALLSELECTED vs REMOVEFILTERS; SUM vs SUMX.
• Write YoY %, % of total, running total, top-N rank.
• Why prefer DIVIDE over /; measure vs calculated column.