3.3 — DAX Calculations: Variables, Iterators, Filtering, Windowing and Information Functions
DP-600 Domain 3 · Skill 3.3
What You Need to Know
DP-600 tests whether you can read and choose DAX. The skills outline names four families: iterators, table filtering, windowing and information functions, plus variables.
Variables
Margin % =
VAR Revenue = SUM ( Sales[Amount] )
VAR Cost = SUM ( Sales[Cost] )
RETURN DIVIDE ( Revenue - Cost, Revenue )
VARevaluates once, in the context where it's defined. That makes measures easier to read and faster, because repeated expressions aren't recalculated.- A variable captures the filter context at the point it's defined. Using it later inside
CALCULATEdoesn't re-evaluate it. DIVIDEhandles division by zero safely, returning BLANK or an alternate value.
Iterators (X functions)
| Function | Use |
|---|---|
SUMX ( Sales, Sales[Qty] * Sales[Price] ) |
Row-by-row expression, then sum. Needed when the calculation must happen per row before aggregating |
AVERAGEX, MINX, MAXX, COUNTX |
The same pattern for other aggregations |
RANKX ( ALL ( Product[Name] ), [Revenue] ) |
Rank items by a measure |
CONCATENATEX |
Build a text list |
Table filtering
| Function | What it does |
|---|---|
CALCULATE ( [Revenue], Product[Color] = "Red" ) |
Changes the filter context (it replaces filters on Color) |
FILTER ( table, condition ) |
Returns rows that meet a condition. Use it for conditions on measures or several columns; prefer simple column predicates when they're enough |
ALL ( table or column ) / REMOVEFILTERS |
Removes filters, for example for percent of total: DIVIDE ( [Revenue], CALCULATE ( [Revenue], ALL ( Product ) ) ) |
ALLEXCEPT ( table, column ) |
Removes all filters on the table except the listed columns |
ALLSELECTED |
Removes filters inside the visual but keeps the slicer and page selection ("percent of visible total") |
KEEPFILTERS |
Adds a condition without overriding existing filters on the same column |
Window functions
| Function | Returns | Example |
|---|---|---|
OFFSET ( -1, ... ) |
A row relative to the current one | Previous month's revenue: CALCULATE ( [Revenue], OFFSET ( -1, ALLSELECTED ( 'Date'[YearMonth] ), ORDERBY ( 'Date'[YearMonth] ) ) ) |
WINDOW ( -2, REL, 0, REL, ... ) |
A range of rows | Rolling three-month total |
INDEX ( 1, ... ) |
The row at an absolute position | The first (or, with -1, last) period in the selection |
All three take a relation (often ALLSELECTED of the axis column), ORDERBY, and optionally PARTITIONBY to restart, for example per category.
Information functions
| Function | Typical use |
|---|---|
ISINSCOPE ( Product[Category] ) |
Different logic at each hierarchy level or on total rows |
HASONEVALUE, SELECTEDVALUE ( col, alt ) |
Read a single selected value (for example from a slicer or field parameter), with a fallback |
ISFILTERED, ISCROSSFILTERED |
Detect whether something filters a column |
ISBLANK, COALESCE |
Handle empty results |
USERPRINCIPALNAME () |
The signed-in user, used in dynamic RLS |
Key Concept: Use variables for clarity and speed, iterators for per-row math, CALCULATE with ALL, ALLSELECTED or KEEPFILTERS for filter control, OFFSET, WINDOW and INDEX for period comparisons along an axis, and ISINSCOPE or SELECTEDVALUE for context-aware measures.
Exam Traps
- Using
SUM ( Sales[Qty] ) * SUM ( Sales[Price] )instead ofSUMXfor line totals - Using
ALLwhen the percentage should respect slicers (useALLSELECTED) - Using
FILTER ( ALL ( Sales ), ... )over a whole fact table when a column predicate is enough (slow) - Expecting a variable defined outside
CALCULATEto be re-evaluated under the new filters
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 17 DP-600 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Use variables to improve your DAX formulas, Microsoft Learn
- CALCULATE function, Microsoft Learn
- OFFSET function, Microsoft Learn
- WINDOW function, Microsoft Learn
- ISINSCOPE function, Microsoft Learn