What is Context Transition in DAX and why does using calculated columns degrade report performance?

Context transition is the automatic conversion of row context into filter context when a DAX function that requires filters (e.g., CALCULATE, TOTALYTD) is called, allowing the expression to behave as if the current row were filtered. Using calculated columns forces that transition to run during data refresh, storing one result per row in the model and dramatically increasing refresh time and memory usage.

Breakdown
1. Row context appears in iterators (SUMX, FILTER) or calculated columns.
2. Trigger: When a filter‑requiring function (CALCULATE, TOTALYTD, SAMEPERIODLASTYEAR) is invoked, the engine wraps the current row context into a filter on the table.
3. Filter propagation traverses relationships; with many‑to‑many or large tables this can generate millions of intermediate rows.
4. Measure execution – occurs at query time, only for rows needed by the visual.
5. Calculated column execution – occurs once per row during refresh; the result is stored, inflating the model.

Comparison
| Aspect | Measure (with context transition) | Calculated Column |
|--------|-----------------------------------|-------------------|
| Execution time | Query‑time, limited to visible rows | Refresh‑time, every row |
| Memory impact | Minimal, no stored values | Increases model size (1 value per row) |
| Refresh cost | Low | High; on 5 M rows adds >30 s and ~200 MB |
| Flexibility | Responds to slicers, dynamic filters | Static after compute |

Code examples

-- Measure using context transition
Sales YTD :=
CALCULATE(
    SUM(Sales[Amount]),
    TOTALYTD('Date'[Date])
)

-- Equivalent calculated column (not recommended)
Sales YTD Column :=
CALCULATE(
    SUM(Sales[Amount]),
    TOTALYTD('Date'[Date])
)

Practical gotcha: If a calculated column references a many‑to‑many relationship, the refresh can explode exponentially; always prefer a measure or a pre‑aggregated table instead.