<!-- https://learnsql.cpetoday.com/analyze/dax-deep-end -->
# DAX deep end: how the engine evaluates your measure

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](/analyze/fundamentals) and [CALCULATE and filter context](/analyze/calculate). 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 `DISTINCTCOUNT` on 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](/analyze/power-query-m-basics).
- 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:

```dax
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:

- `USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] )` activates an inactive relationship **inside** `CALCULATE` for 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. `TREATAS` and `SELECTCOLUMNS` can 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.

```dax
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:

```dax
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.

```dax
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

- **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.
- **`SUMMARIZECOLUMNS`** is what visuals use. It is usually faster than `SUMMARIZE` plus `ADDCOLUMNS` for 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

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](/analyze/sql-essentials) against the source. If it differs, the problem is the model, not the measure.

## Where next

- [Pattern cookbook](/analyze/patterns): finished measures to adapt.
- [Performance and troubleshooting](/analyze/performance): a shorter, practical version of the tuning section here.
- [Function reference](/analyze/functions): look up any function.
- [Relationships and the data model](/analyze/relationships): the model decisions that make everything else easy.
