4.3 DAX Foundations
Key Takeaways
- A measure is evaluated at query time in the current filter context; a calculated column is computed at refresh and stored per row.
- CALCULATE is the only function that can modify filter context, and it is the engine behind almost every non-trivial measure.
- Context transition is CALCULATE (or a measure call) turning the current row context into an equivalent filter context — the most tested DAX concept on DP-600.
- VAR stores an expression once so it is evaluated a single time and reused, improving both readability and performance.
- Iterator functions (SUMX, AVERAGEX, FILTER) evaluate row by row; use DIVIDE for safe division and avoid calculated columns when a measure will do.
DAX on DP-600 Is About Judgment, Not Trivia
The semantic-model domain assumes you can read and reason about Data Analysis Expressions (DAX), the formula language for measures, calculated columns, and calculated tables. DP-600 rarely asks you to write long formulas from scratch; it asks which construct is correct, why a result is wrong, or how to make a calculation efficient.
Measure vs Calculated Column
This distinction is a near-guaranteed exam point.
| Measure | Calculated column | |
|---|---|---|
| When evaluated | Query time, in filter context | Data refresh, per row |
| Storage | Not stored; computed on demand | Stored in the model (uses memory) |
| Aggregation | Already an aggregate | A value per row, aggregated later |
| Best for | Sums, ratios, KPIs that respond to slicers | Row-level attributes used to slice/group |
Default to a measure. Use a calculated column only when you need a per-row value to filter, group, or relate on. Over-using calculated columns bloats Import models and is a common DP-600 wrong answer.
Evaluation Context
DAX evaluates in two contexts. Filter context is the set of filters applied by slicers, rows/columns of a visual, and CALCULATE. Row context exists when iterating a table (calculated columns and iterators like SUMX). Confusing the two is the single biggest source of wrong DAX results, so the exam probes it directly.
CALCULATE and Context Transition
CALCULATE is the most important DAX function: it evaluates an expression in a modified filter context. Filter arguments add, replace, or remove filters.
Context transition is the behavior where CALCULATE (and an implicit CALCULATE around every measure reference) converts the current row context into filter context. This is why calling a measure inside an iterator like SUMX over a fact table produces a correctly filtered per-row result instead of the same grand total repeated. Expect at least one DP-600 question whose 'unexpected total' symptom is explained by context transition.
Variables and Common Functions
VAR ... RETURN stores a value once. The expression is evaluated a single time and the variable is reused, which is faster than repeating the sub-expression and far easier to debug. Variables are evaluated in the context where they are defined, which also avoids subtle context bugs.
High-frequency functions to know:
CALCULATE,FILTER,ALL,ALLEXCEPT,REMOVEFILTERS,KEEPFILTERS- Iterators:
SUMX,AVERAGEX,RANKX - Safety/utility:
DIVIDE(safe division, avoids divide-by-zero),COALESCE,SWITCH - Time intelligence:
TOTALYTD,SAMEPERIODLASTYEAR,DATEADD(require a marked date table)
A measure that should show this-year sales returns the all-years grand total in every row of a table visual grouped by year. Which concept most likely explains the bug?
You need a field to slice sales by a 'Customer Tier' derived from each customer's lifetime spend. The tier must be usable on slicers and the axis of charts. What should you create?