Memory hook: Reuse a calculation without losing its context.
Must remember
DAX variables name intermediate results within an evaluation scope; they improve clarity and can avoid repeated work. They are evaluated where defined, so moving a variable outside an iterator can change meaning. Iterators such as SUMX create row context, while CALCULATE changes filter context and can perform context transition.
Table filters define the rows available to a calculation. Prefer the narrowest correct filter instead of clearing an entire model for a denominator. Information functions such as ISINSCOPE distinguish hierarchy scope and can support meaningful subtotal behavior. SELECTEDVALUE needs an alternate result when there is no single selected value.
Window functions such as OFFSET, INDEX and WINDOW navigate ordered/partitioned model rows. Specify the relation, ordering and partitioning deliberately; a previous row is not necessarily the previous calendar day. Ties and missing periods must match the business definition.
Calculation groups apply reusable transformations across measures, such as time comparisons. Precedence matters when multiple groups apply. Dynamic format strings preserve numeric values while changing presentation; converting a measure to text with FORMAT changes how downstream visuals can use it. Field parameters let report users switch selected fields/measures without duplicating every visual.
Many-to-many relationships need a deliberate bridge and filter direction. A composite model introduces source/storage boundaries that can change performance and supported semantics. Test totals, subtotals, role behavior and representative slicer combinations, not just the first visible row. A total is a fresh evaluation in total context, not necessarily a sum of displayed cells.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Reuse YTD logic across many measures | Calculation group with tested precedence. |
| Show currency format without converting number to text | Dynamic format string. |
| Compare an ordered prior row within each product | A correctly partitioned/ordered window expression. |
Traps
- A variable is not re-evaluated every time its name appears.
- A previous ordered row and previous date are different requirements.
Active recall
1. Why can variable placement matter?
It determines the context in which the value is evaluated.
2. What does ISINSCOPE help identify?
Whether a column is a grouping level in the current hierarchy context.
3. Dynamic format versus FORMAT?
Preserve numeric type versus return formatted text.
4. Why define window ordering?
Without the intended order and partitions, relative-row comparisons answer the wrong question.
5. Why test totals separately?
Measures are evaluated again in total context, which can produce a result different from adding visible rows.