DAX Measures

## CALCULATE is the only function that changes filter context Everything else in DAX reads the filter context; CALCULATE rewrites it. Its signature is `CALCULATE ( <expression>, <filter1>, <filter2>, … )`. It builds a new filter context from the current one plus the filter arguments, then evaluates the expression there. The interview question is almost always about the word "plus" — because it is not a plain intersection. ## Boolean arguments overwrite the column they touch Write this measure: ``` Accessories Sales = CALCULATE ( [Total Sales], Product[Category] = "Accessories" ) ``` and put it in a matrix whose rows are product categories. On the `Bikes` row you might expect blank — the row says Bikes, the measure says Accessories, and the intersection is empty. You get Accessories sales instead, repeated on every row. The reason is that a boolean filter argument is syntax sugar. DAX rewrites it internally as `FILTER ( ALL ( Product[Category] ), Product[Category] = "Accessories" )`. The `ALL` clears the existing filter on `Product[Category]` first; the predicate then reapplies just the categories you named. The engine calls this filter *overwrite*, and it applies per column: only the columns referenced in the filter argument are cleared. That last point is what makes the behaviour useful rather than dangerous. A year slicer, a region slicer and a row header on customer are all on other columns, so they survive untouched and still narrow the result. The measure means "Accessories, under whatever else is selected", which is exactly what you want for a benchmark or comparison measure sitting next to the sliced number. ## KEEPFILTERS restores the intersection When you genuinely want "Accessories **and** whatever is already selected on that column", wrap the argument: ``` Accessories Sales Kept = CALCULATE ( [Total Sales], KEEPFILTERS ( Product[Category] = "Accessories" ) ) ``` Now the new filter is intersected with the existing one instead of replacing it, so the `Bikes` row is blank and the `Accessories` row shows its value. KEEPFILTERS is the single most useful modifier to know here, because "my measure ignores the slicer" is one of the most common bug reports in Power BI and this is usually the fix. ## Filter arguments are evaluated in the outer context All filter arguments are computed *before* the new context exists, in the context CALCULATE was called from. So `CALCULATE ( [Total Sales], Sales[Amount] > [Average Amount] )` compares against the average of the outer context, not against an average recomputed under the new filter. Table-valued filter arguments — a `FILTER(...)` expression, `VALUES`, `TREATAS` — follow the same rule, and they intersect by default in the same per-column way once the columns they carry are determined. ## Removing filters deliberately The other half of CALCULATE's vocabulary is the filter-removal family. `ALL ( Table )` or `ALL ( Table[Column] )` removes filters entirely; `REMOVEFILTERS` is the modern, clearer-reading synonym for that use. `ALLEXCEPT ( Table, Table[Col] )` removes every filter on the table except the listed columns — the standard way to build a "percent of parent" measure. `ALLSELECTED` removes filters coming from inside the visual but respects the user's outer selections, which is why a percent-of-total measure built with ALLSELECTED rebases to what the slicer chose rather than to the entire model. ``` % of Category = DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLEXCEPT ( Product, Product[Category] ) ) ) ``` ## Context transition comes free with CALCULATE If a row context is active when CALCULATE runs, CALCULATE turns that row into filters on the iterated table before applying the filter arguments. Every measure reference is implicitly wrapped in CALCULATE, so calling a measure inside SUMX or a calculated column silently triggers this. It is the mechanism behind per-customer measures inside iterators, and it is also why such patterns get expensive over large fact tables. ## Multiple filter arguments Several filter arguments are combined with AND. Each is applied by the same per-column overwrite rule, so `CALCULATE ( [Total Sales], Product[Category] = "Bikes", 'Date'[Year] = 2024 )` clears and resets both columns. A boolean argument may reference only one column; a condition spanning two columns has to be written as a table filter with `FILTER`. ## What to say under questioning Name the overwrite semantics and the `FILTER(ALL(column), …)` rewrite; state that other columns still intersect; name KEEPFILTERS as the opt-in to intersection; mention that filter arguments evaluate in the outer context; and mention context transition as the second thing CALCULATE does. That covers the function that, in practice, is most of the language.