2.5 — Single Aggregation Measures and the CALCULATE Function
PL-300 Domain 2 · Skill 2.5
What You Need to Know
Single aggregation measures
| Function | Returns |
|---|---|
SUM(col), AVERAGE(col), MIN(col), MAX(col) |
The aggregate of a numeric column |
COUNT(col) |
Non-blank values in a column |
COUNTA(col) |
Non-blank values of any type |
COUNTBLANK(col) |
Blank values |
COUNTROWS(table) |
Rows in a table (the usual way to count orders or lines) |
DISTINCTCOUNT(col) |
Distinct values, counting BLANK as a value |
SUMX(table, expr), AVERAGEX… |
An iterator: evaluates expr row by row, then aggregates |
Use an iterator when the value must be computed per row first: Revenue = SUMX(Sales, Sales[Quantity] * Sales[Unit Price]). This avoids storing a calculated column.
Filter context
Every cell in a visual has a filter context: the slicers, page and report filters, the row and column headers of the visual, and RLS. A measure is evaluated inside that context. Filters reach a table through relationships.
CALCULATE
CALCULATE(<expression>, <filter1>, <filter2>, …) evaluates an expression in a modified filter context. It's the most important DAX function.
Red Sales = CALCULATE([Total Sales], Product[Color] = "Red")
- A simple filter like
Product[Color] = "Red"replaces any existing filter on that column. Slicing by Color = Blue still shows red sales. - Wrap it in
KEEPFILTERSto intersect with the existing filter instead:CALCULATE([Total Sales], KEEPFILTERS(Product[Color] = "Red"))returns blank for Blue. - Several filter arguments are combined with AND. Use
||inside one argument, orIN { }, for OR on one column. FILTER(table, condition)is needed when the condition uses a measure or compares columns:CALCULATE([Total Sales], FILTER(Customer, [Customer Sales] > 10000)).
Removing filters
| Function | Removes |
|---|---|
ALL(table) / REMOVEFILTERS(table) |
Every filter on the table |
ALL(col) / REMOVEFILTERS(col) |
Filters on that column only |
ALLEXCEPT(table, col1, …) |
Every filter on the table except those on the listed columns |
ALLSELECTED(…) |
Filters from inside the visual, but keeps slicer and filter-pane selections ("% of visible total") |
Percent of total:
% of All Products = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Product)))
Context transition
When CALCULATE is used where there's a row context (a calculated column, or inside an iterator), it turns the current row into a filter. That's why a measure referenced in a calculated column returns the value for that row, while a bare SUM in a calculated column returns the grand total.
Key Concept: CALCULATE is how you change what a measure sees: add filters, override them, keep them, or remove them with ALL, REMOVEFILTERS and ALLEXCEPT.
Exam Traps
- Expecting
CALCULATE([Sales], Product[Color] = "Red")to respect a slicer on Color (it overrides it unless you use KEEPFILTERS) - Using
ALLwhen the percentage must respect the user's slicer selection (use ALLSELECTED) - Writing
CALCULATE([Sales], [Profit] > 0)(a measure in a filter needs FILTER) - Counting orders with
COUNT(Sales[OrderID])when the table has one row per order line (use DISTINCTCOUNT on the order number, or COUNTROWS on an orders table) - Forgetting that DISTINCTCOUNT counts BLANK as one value
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 22 PL-300 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
- CALCULATE function (DAX), Microsoft Learn
- Filter context and row context, Microsoft Learn
- ALLSELECTED function (DAX), Microsoft Learn
- KEEPFILTERS function (DAX), Microsoft Learn