Memory hook: A measure answers the current filter context.
Must remember
SUM(Sales[Amount]) aggregates a column; SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) iterates rows and evaluates an expression. Row context identifies a current row; filter context defines the allowed set of rows. They are different, and relationships/filter propagation affect the result.
CALCULATE evaluates an expression with modified filter context and can perform context transition from row context. REMOVEFILTERS removes selected filters; KEEPFILTERS intersects rather than replacing the relevant existing filter. Avoid clearing more context than the denominator requires. DIVIDE handles a zero/blank denominator with a chosen alternate result. Variables improve readability and can avoid repeated expression evaluation within their scope.
Time-intelligence measures require a suitable date model and the correct relationship/date context. Year-to-date sums are useful for flows such as sales; balances are often semi-additive because summing month-end balances across time is meaningless. Decide whether the requirement is closing balance, average balance or a different business definition.
Quick measures generate DAX that still needs review. Calculation groups reuse calculation patterns across measures with precedence/format considerations. Visual calculations operate on the visual’s result structure and are not interchangeable with reusable model measures in every scenario.
Use Performance Analyzer to separate visual rendering and query time, then inspect DAX queries and model design. Remove unnecessary columns/rows, reduce high-cardinality text and use suitable types/grain. Excessive bidirectional relationships, expensive iterators over large tables and overly complex visuals can all contribute. A smaller model often helps, but aggregating away required detail changes analytical capability.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Revenue from quantity times price | SUMX over the appropriate grain. |
| Percent of category while retaining other filters | A carefully scoped CALCULATE denominator. |
| Month-end stock level | A semi-additive balance measure, not sum across all months. |
Traps
- A total is evaluated in its own context; it may not be the sum of visible row results.
- Calculated columns do not recalculate for every slicer interaction in an Import model.
Active recall
1. SUM versus SUMX?
Aggregate a column versus iterate rows and evaluate an expression.
2. What does CALCULATE change?
Filter context, and it can transition row context into filters.
3. Why use DIVIDE?
To handle zero/blank denominators consistently.
4. What makes a balance semi-additive?
It can aggregate across some dimensions but not by summing across time.
5. First performance step?
Measure the bottleneck before changing data/model/visual design.