DAX deep end
How the formula and storage engines work, context transition, how CALCULATE evaluates, query plans and why a measure is slow.
Last updated
View as Markdown For AI agents All guides as text
On this page
You'll be able to
- Explain how the formula engine and the storage engine share the work.
- Describe how CALCULATE evaluates filters and context transition.
- Read a query plan to see where the time goes.
- Recognize measure patterns that look simple but run slowly.
This guide is for when your measures work but you do not yet know why they work, or why one is fast and another is slow. It assumes DAX fundamentals and CALCULATE and filter context. Read it slowly. The ideas build on each other.
The two engines
A DAX query is answered by two cooperating engines.
| Engine | Job | Character |
|---|---|---|
| Formula engine (FE) | Parses DAX, builds the query plan, does everything that is not a simple scan: iterating row by row, branching with IF and SWITCH, complex calculations |
Single-threaded. Flexible and slow. |
| Storage engine (SE) | Scans compressed column data (the in-memory VertiPaq store) and returns aggregated results for simple requests | Multi-threaded and very fast. |
Most DAX tuning is the art of pushing work from the formula engine to the storage engine. A simple SUM(Sales[Amount]) filtered by a few columns is one fast storage-engine scan. A measure that loops over a million rows and calls IF on each is the formula engine doing it one row at a time.
VertiPaq in one page
VertiPaq stores each column separately and compresses it. What matters for you:
- Cardinality is the cost. A column with few distinct values (a status, a year) compresses to almost nothing. A column with millions of distinct values (an ID, a timestamp, a free-text comment) is large and slow to scan.
- Columns, not rows. A query that touches three columns reads three columns, however many exist.
- Dictionary encoding: each distinct value is stored once and rows hold a small number pointing at it. This is why
DISTINCTCOUNTon a low-cardinality column is quick. - Relationships are lookups between columns' dictionaries, so key columns with many distinct values cost memory on both sides.
- Calculated columns are stored data, computed at refresh, taking memory. Measures are computed at query time and take none. Prefer measures, and push row-by-row calculations back to the source or Power Query.
- Split a datetime into a date and a time column. A full timestamp has enormous cardinality; the two halves have very little.
Evaluation context, precisely
Two kinds of context exist, and they behave differently.
Filter context is a set of filters on columns. Every cell of a visual has one: the row's category, the column's year and any slicers combine into a filter context, and the measure is evaluated within it. CALCULATE changes it.
Row context means "the current row of one table" and exists inside calculated columns and iterators (SUMX, FILTER, ADDCOLUMNS). It does not filter anything by itself.
Context transition
CALCULATE turns every row context that exists into an equivalent filter context. A measure reference is silently wrapped in CALCULATE, so:
Customer Sales = SUMX ( Customer, [Total Sales] )
Inside the iterator the row context is the current customer. Calling the measure [Total Sales] triggers a transition: that row becomes a filter on all the customer's columns, so [Total Sales] returns that customer's sales. Two consequences:
- A column reference inside an iterator is just that row's value. A measure reference is the same row as a filter, which may hit more than one row if the table has duplicates.
- Transition across a big table costs a lot. Each row becomes a filter and an evaluation. This is the single most common cause of slow iterators.
How CALCULATE evaluates
When CALCULATE ( <expr>, <filter1>, <filter2> ) runs, in order:
- The filter arguments are evaluated in the original filter context.
- A row context in effect is turned into filters (context transition).
- The filters are applied to the filter context:
ALLandREMOVEFILTERSclear columns first, and other filters replace any existing filter on the same column. (UseKEEPFILTERSto intersect instead of replace.) - The expression is evaluated in the new context.
Because step 1 uses the old context, a filter argument can read the current cell (for example with SELECTEDVALUE) and then use it to change the filter.
Filter arguments such as Sales[Region] = "West" are shorthand for FILTER ( ALL ( Sales[Region] ), Sales[Region] = "West" ): a one-column table of allowed values. That is also why CALCULATE ( [x], Sales[Region] = "West" ) replaces a region slicer rather than adding to it.
ALL, ALLEXCEPT, ALLSELECTED, VALUES
| Function | In a filter argument it... |
|---|---|
ALL ( Table[Col] ) |
removes filters on that column (and, with ALL(Table), all columns of the table) |
ALLEXCEPT ( Table, Table[Col] ) |
removes every filter on the table except those columns |
ALLSELECTED ( ... ) |
restores the filters coming from outside the visual (slicers, page filters) while ignoring the row and column the cell sits in |
VALUES ( Table[Col] ) |
the distinct values currently visible, plus a blank if one is needed |
KEEPFILTERS ( ... ) |
intersect with the existing filter instead of replacing it |
ALLSELECTED is the usual choice for "percent of the visible total". ALL ignores slicers too, so a percent of ALL changes when you slice.
Filter propagation and relationships
A filter on a dimension reaches the fact table through the relationship, from the one side to the many side. A filter on the fact table does not reach the dimension unless the relationship is bidirectional (or you use CROSSFILTER or TREATAS).
Under the hood the engine behaves as if the fact table were expanded to contain the columns of every table on its one side. That is why RELATED works in a row context on the many side, and why filtering Product[Color] is like filtering a column of the expanded Sales table.
Know these tools:
USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] )activates an inactive relationship insideCALCULATEfor that evaluation. It is how you get "sales by ship date" next to "sales by order date".TREATAS ( VALUES ( Other[Key] ), Sales[Key] )applies the values of one column as a filter on an unrelated column. It is the way to use a virtual relationship without changing the model, and the usual fix for many-to-many without a bridge.CROSSFILTER ( Sales[Key], Dim[Key], BOTH )changes the direction for one calculation.- Lineage tracks which column a filter comes from.
TREATASandSELECTCOLUMNScan change it. Many "my filter does nothing" bugs are lost lineage.
Totals: why they differ
At a grand total the filter context has no filter on the row column, so the measure sees all rows. A measure that is not additive (a ratio, a distinct count, a rank) does not equal the sum of its rows.
Avg Price = DIVIDE ( [Total Sales], [Units Sold] )
The total uses total sales over total units, which is correct. For a measure that must act per row and add up, iterate the row set so each row is calculated, then summed:
Total Margin Rounded = SUMX ( VALUES ( Product[Product] ), ROUND ( [Margin], 0 ) )
ISINSCOPE ( Product[Product] ) returns true when the row being evaluated is a product, so you can behave differently on the subtotal and total rows. HASONEVALUE and SELECTEDVALUE answer "is there exactly one value in context".
Variables and evaluation
VAR computes an expression once, in the context where the VAR is written, and the RETURN expression reuses the result.
Sales Growth = VAR Current = [Total Sales] VAR Prior = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) RETURN DIVIDE ( Current - Prior, Prior )
A variable's value is fixed at the point it was defined. A variable defined outside CALCULATE does not change when CALCULATE changes the filter context inside it. This makes variables useful (compute once) and sometimes the cause of "why is it not responding to my filter" (the value was frozen before the filter).
Reading query plans: where time goes
Use Performance Analyzer in Power BI Desktop to find the slow visual and copy its DAX query. Run it in DAX Studio with Server Timings and Query Plan on.
| You see | It means | What to try |
|---|---|---|
| Many fast SE queries, long FE time | Row-by-row work in the formula engine | Remove iterators over big tables, avoid IF inside iterators, pre-compute in the source |
| One huge SE scan with large materialization | Pulled a lot of rows into the FE | Filter earlier; use columns of the dimension rather than the fact |
| Many repeated SE queries differing only slightly | Transition or nested iterators | Reduce nesting; compute once in a variable |
Callbacks (CallbackDataID) in the SE query |
The SE had to call the FE for each row (often IF, SWITCH, DIVIDE in an iterator, or a format) |
Move the logic out of the iterator; use DIVIDE outside |
| Big cache hits on the second run | The result was cached | Clear the cache between tests for honest timings |
Habits that make DAX fast
- Filter columns, not tables.
Sales[Region] = "West"is a column filter the SE handles easily.FILTER ( Sales, ... )iterates the whole fact table. - Use measures in the visual, not columns, and hide columns people should not aggregate.
SUMMARIZECOLUMNSis what visuals use. It is usually faster thanSUMMARIZEplusADDCOLUMNSfor building tables, and applies "auto-exist" so combinations that do not exist are not returned.- Avoid bidirectional relationships and many-to-many on large tables unless needed.
- Keep distinct counts to low-cardinality columns when you can, or pre-aggregate.
- Reduce cardinality: round decimals, split datetimes, drop unused columns.
- One date table, marked as the date table, and relationships on date (or integer) keys.
Patterns that look simple and are not
| Request | Trap | Safer approach |
|---|---|---|
| "Percent of total" | ALL ignores slicers; users expect the visible total |
ALLSELECTED for percent of visible, ALL for percent of everything |
| "Top 5 customers" with a slicer | TOPN inside CALCULATE computes in the wrong context |
Build the set in a VAR, then apply it with TREATAS or KEEPFILTERS |
| "Prior year" on a partial year | Prior year full vs this year partial | DATESYTD and SAMEPERIODLASTYEAR together, or compare to the same elapsed days |
| "Running total" | Window grows with every row, slow on big models | Use WINDOW or an index-based pattern; test timing |
| "Distinct count of customers who bought X and Y" | Row context does not intersect two filters | Compute both sets as tables, intersect with INTERSECT |
| "Average of a measure per month" | AVERAGEX ( VALUES ( 'Date'[Month] ), [Measure] ) is right; averaging the cell values by hand is not |
Iterate the level you want to average over |
A debugging routine
- Reduce: put the measure in a table visual with one column that shows the problem.
- Break it up: return each
VARas the final result, one at a time. - Inspect context: add
COUNTROWS,VALUESorCONCATENATEXmeasures that show what is filtered. - Test the model first: is the relationship active, the right direction, on the right key?
- Compare to SQL: write the same question in SQL against the source. If it differs, the problem is the model, not the measure.
Where next
- Pattern cookbook: finished measures to adapt.
- Performance and troubleshooting: a shorter, practical version of the tuning section here.
- Function reference: look up any function.
- Relationships and the data model: the model decisions that make everything else easy.
Want to see it run?
Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.