Downloads

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:

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:

  1. 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.
  2. 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:

  1. The filter arguments are evaluated in the original filter context.
  2. A row context in effect is turned into filters (context transition).
  3. The filters are applied to the filter context: ALL and REMOVEFILTERS clear columns first, and other filters replace any existing filter on the same column. (Use KEEPFILTERS to intersect instead of replace.)
  4. 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:

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 )
Watch out

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

Tip

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

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

  1. Reduce: put the measure in a table visual with one column that shows the problem.
  2. Break it up: return each VAR as the final result, one at a time.
  3. Inspect context: add COUNTROWS, VALUES or CONCATENATEX measures that show what is filtered.
  4. Test the model first: is the relationship active, the right direction, on the right key?
  5. 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

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.