4.3 DAX Foundations

DAX on DP-600 Is About Judgment, Not Trivia

The semantic-model domain assumes you can read and reason about DAX (Data Analysis Expressions), the formula language for measures, calculated columns, and calculated tables. DP-600 rarely asks you to write a long formula from scratch; it asks which construct is correct, why a result is wrong, or how to make a calculation efficient.

The exam's favorite DAX symptoms map to a small set of root causes:

Learn to diagnose from the symptom, because that is exactly how the questions are phrased.

Measure vs Calculated Column

This distinction is a near-guaranteed exam point.

Default to a measure. Reach for a calculated column only when you genuinely need a per-row value to filter, group, or relate on — for example a 'Customer Tier' derived from lifetime spend that must appear on a slicer. Over-using calculated columns is a classic Import-model mistake: each one is stored and compressed, inflating model size and refresh time, and is a frequent DP-600 wrong answer. In Direct Lake on OneLake, calculated columns are an unmaterialized preview feature and are unsupported on the SQL-endpoint flavor, so the exam leans toward pushing such logic upstream into the Delta tables.

Evaluation Context

DAX evaluates in two contexts, and confusing them is the single biggest source of wrong results.

Filter context is the set of filters currently applied: slicers, the row/column headers of a visual, the page and report filters, and anything CALCULATE adds. It determines which rows are visible when an aggregation runs.

Row context exists when DAX iterates a table one row at a time — inside a calculated column, or inside an iterator function such as SUMX. It exposes the current row's column values but, crucially, does not by itself filter related tables.

The two are independent. A calculated column has row context but no filter context; a measure dropped in a visual has filter context but no row context until an iterator creates one. The bridge between them is context transition, covered next. Expect questions that hand you a formula and a wrong number and ask which context concept explains it — the answer is almost always 'the filter context was removed' or 'row context never became filter context.'

CALCULATE and Context Transition

CALCULATE is the most important DAX function: it evaluates an expression in a modified filter context. Its filter arguments can add, replace, or remove filters (with helpers like ALL, ALLEXCEPT, REMOVEFILTERS, and KEEPFILTERS).

Context transition is the behavior where CALCULATE — and the implicit CALCULATE wrapped around every measure reference — converts the current row context into filter context. This is why calling a measure inside SUMX over the fact table produces a correctly filtered per-row result instead of the same grand total repeated on every row.

Weighted Margin =
SUMX ( Sales, Sales[Qty] * [Unit Margin] )   -- [Unit Margin] triggers context transition per row

Without context transition, [Unit Margin] would ignore the current row and return the overall figure. Microsoft loves the 'unexpected total in every row' symptom because it has two opposite causes the candidate must distinguish: filter context was removed by an ALL, or context transition is missing because a bare column expression was used where a measure call was needed. Expect at least one such item.

Variables and High-Frequency Functions

VAR ... RETURN stores a value once. The expression is evaluated a single time and the variable is reused, which is both faster (no recomputation) and far easier to debug. A subtle but tested benefit: a variable is evaluated in the context where it is defined, so it 'freezes' a value before CALCULATE later changes the context — useful for year-over-year deltas where you need the prior value captured first.

YoY % =
VAR Curr = [Total Sales]
VAR Prev = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN DIVIDE ( Curr - Prev, Prev )

Functions you should recognize on sight:

• Context: CALCULATE, FILTER, ALL, ALLEXCEPT, REMOVEFILTERS, KEEPFILTERS
• Iterators: SUMX, AVERAGEX, RANKX
• Safety/utility: DIVIDE (avoids divide-by-zero, returns blank or a fallback), COALESCE, SWITCH
• Time intelligence: TOTALYTD, SAMEPERIODLASTYEAR, DATEADD — all require a marked date table with a contiguous date column.