CALCULATE & Filter Context
Master the CALCULATE function — the most powerful and most tested DAX function on the PL-300 exam. Understand how it modifies filter context and works with ALL, REMOVEFILTERS, and KEEPFILTERS.
The most important DAX function
Think of CALCULATE as a pair of tinted glasses.
Normally, you see the whole room (all data). Put on blue-tinted glasses and everything looks blue (filtered to one category). CALCULATE puts these glasses on your measure — it changes what data the measure “sees” before doing its calculation.
CALCULATE(SUM(Sales[Revenue]), Products[Category] = “Fruit”) says: “Sum the revenue, BUT only look at fruit sales.” Even if the user hasn’t filtered to Fruit, this measure always shows fruit revenue.
CALCULATE can also REMOVE filters — like taking glasses off to see everything again, even when a slicer is active.
CALCULATE basics
Riley at Coastal Fresh (🛒) needs measures that always show specific slices, regardless of user filters:
Fruit Revenue =
CALCULATE(
SUM(Sales[Revenue]),
Products[Category] = "Fruit"
)This measure ALWAYS shows fruit revenue — even if the user selects “Dairy” on a slicer.
North Region Revenue =
CALCULATE(
SUM(Sales[Revenue]),
Stores[Region] = "North"
)How it works:
- CALCULATE takes the current filter context (whatever slicers/filters are active)
- It ADDS the filter
Products[Category] = "Fruit" - If there’s already a filter on Category, it REPLACES it
- Then it evaluates
SUM(Sales[Revenue])in this modified context
Removing filters with ALL
ALL removes filters from a table or column. Combined with CALCULATE, it lets you ignore specific filters.
Revenue % of Total =
VAR CurrentRevenue = SUM(Sales[Revenue])
VAR AllRevenue = CALCULATE(SUM(Sales[Revenue]), REMOVEFILTERS())
RETURN
DIVIDE(CurrentRevenue, AllRevenue)| Region | Revenue | Revenue % of Total |
|---|---|---|
| North | $50K | 42% |
| South | $30K | 25% |
| East | $25K | 21% |
| West | $15K | 12% |
| Total | $120K | 100% |
REMOVEFILTERS() (with no arguments) removes ALL filters from the entire model, so AllRevenue always returns the grand total of $120K regardless of any active filters. You can also target specific tables or columns: REMOVEFILTERS(Stores) removes only the store filter.
ALL vs REMOVEFILTERS vs ALLEXCEPT
| Function | What It Does | Use When |
|---|---|---|
| ALL(table) | Removes all filters from the entire table | You need the grand total, ignoring all filters on that table |
| ALL(column) | Removes filters from a specific column only | You want to ignore one filter but keep others |
| REMOVEFILTERS() | Same as ALL but clearer intent — removes filters | Microsoft's recommended replacement for ALL as a filter remover |
| ALLEXCEPT(table, column) | Removes all filters EXCEPT on specified columns | You want to keep one filter active and remove everything else |
| KEEPFILTERS() | Adds a filter that intersects with existing filters instead of replacing | You want to narrow down, not override the current filter |
Practical examples
Nadia at Prism Agency (📊) needs campaign metrics that compare against totals:
// Revenue share within each platform (ignores platform filter)
Platform Share =
DIVIDE(
SUM(Campaigns[Revenue]),
CALCULATE(SUM(Campaigns[Revenue]), ALL(Campaigns[Platform]))
)// Revenue keeping the platform filter but ignoring the campaign filter
Platform Total =
CALCULATE(
SUM(Campaigns[Revenue]),
ALLEXCEPT(Campaigns, Campaigns[Platform])
)Exam tip: CALCULATE filter replacement
This is the #1 CALCULATE trap on the exam. When CALCULATE has a filter on a column that’s already filtered by a slicer:
CALCULATE replaces the existing filter — it doesn’t intersect.
Example: A slicer selects “North” region. The measure CALCULATE(SUM(Revenue), Region = "South") returns South revenue, NOT zero. CALCULATE replaced the “North” filter with “South”.
To intersect (show only if BOTH conditions are true), use KEEPFILTERS:
CALCULATE(SUM(Revenue), KEEPFILTERS(Region = "South")) — returns zero because North AND South can’t both be true.
CALCULATE with multiple filters
You can stack multiple filter arguments — they all apply simultaneously:
North Fruit Revenue =
CALCULATE(
SUM(Sales[Revenue]),
Stores[Region] = "North",
Products[Category] = "Fruit"
)This is equivalent to “SUM revenue WHERE Region = North AND Category = Fruit”.
Dr. Ethan at Bayview Medical (🏥) uses CALCULATE for compliance metrics:
Emergency After Hours =
CALCULATE(
COUNTROWS(Appointments),
Appointments[Type] = "Emergency",
'Date'[IsBusinessHours] = FALSE
)Context transition
When CALCULATE is used inside an iterator function (SUMX, AVERAGEX, etc.) or a calculated column, it triggers context transition — converting the current row context into filter context.
This is an advanced concept tested on the exam:
// In a calculated column on the Products table:
Product Revenue = CALCULATE(SUM(Sales[Revenue]))Without CALCULATE, SUM(Sales[Revenue]) in a calculated column would return the grand total for every row (no filter context). With CALCULATE, context transition converts the row context (current product) into a filter — so each product gets its own revenue total.
Knowledge check
A slicer is set to Region = 'North'. Riley's measure is: CALCULATE(SUM(Sales[Revenue]), Stores[Region] = "South"). What does it return?
Nadia needs each campaign's revenue as a percentage of the platform total (e.g., Google Ads total). Which measure is correct?
🎬 Video coming soon
Next up: Time Intelligence and Calculation Groups — year-to-date, year-over-year, and other time-based calculations.