Launch Deal · 50% off 1st month ·

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 )
  • VAR evaluates 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 CALCULATE doesn't re-evaluate it.
  • DIVIDE handles 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 of SUMX for line totals
  • Using ALL when the percentage should respect slicers (use ALLSELECTED)
  • Using FILTER ( ALL ( Sales ), ... ) over a whole fact table when a column predicate is enough (slow)
  • Expecting a variable defined outside CALCULATE to 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.

See plans

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