Module 5: Filter context and CALCULATE · Lesson 5.2 · about 18 min
CALCULATE: change the filters
CALCULATE evaluates an expression in a modified filter context. Add a filter, replace one, or keep both with KEEPFILTERS, and watch the context change step by step.
Module 5 · about 18 minutes · read first: 5.1. You’ll be able to use CALCULATE to add, replace and combine filters, and predict its result.
1. Why this matters
CALCULATE is the most important function in DAX. Almost every business measure beyond a plain sum uses it: sales for one category, last year’s sales, share of total, sales to date. If you understand CALCULATE, you understand DAX.
2. Add a filter
Write Home Sales: Total Sales for the Home category only, whatever the visual shows. Split by Store.
Press Run to see the result.
CALCULATE(expression, filter): CALCULATE([Total Sales], Product[Category] = "Home").
Total Sales = SUM(Sales[Amount])
Home Sales = CALCULATE([Total Sales], Product[Category] = "Home") Trace a store: the filter context starts with that store, then CALCULATE adds Category = Home, and only then does SUM run.
3. Replace, or keep?
Split by Product[Category] and run. Why does Home Sales show the same number on every row? Then try the KEEPFILTERS version.
Press Run to see the result.
On the Grocery row, the visual says Category = Grocery. CALCULATE’s filter is also on Category, so it replaces Grocery with Home: every row shows Home’s sales. That’s usually what you want for a fixed comparison (“Home sales next to each category”). KEEPFILTERS keeps both filters, so only the Home row passes.
4. Several filters, and IN
Write Weekend Sales for Saturday and Sunday only, using IN {"Sat", "Sun"} on 'Date'[Weekday], and Weekend % = Weekend Sales ÷ Total Sales.
Press Run to see the result.
Total Sales = SUM(Sales[Amount])
Weekend Sales = CALCULATE([Total Sales], 'Date'[Weekday] IN {"Sat", "Sun"})
Weekend % = DIVIDE([Weekend Sales], [Total Sales]) Write West Outdoor 2025: sales of Outdoor products in West stores in 2025. CALCULATE takes several filters, separated by commas.
Press Run to see the result.
Each filter is its own argument: CALCULATE([Total Sales], filter1, filter2, filter3). Filters on different columns are combined with AND.
Total Sales = SUM(Sales[Amount])
West Outdoor 2025 = CALCULATE([Total Sales], Store[Region] = "West", Product[Category] = "Outdoor", 'Date'[Year] = 2025) Filters on different columns combine with AND. For OR on one column, use IN { … } or || inside one condition: Product[Category] = "Home" || Product[Category] = "Outdoor".
5. What a filter argument really is
Product[Category] = "Home" inside CALCULATE is shorthand for a table: FILTER(ALL(Product[Category]), Product[Category] = "Home"), the list of category values where the condition is true. CALCULATE then puts that list in place of any existing filter on Product[Category]. That’s why it replaces, and it’s why a condition can only use columns from one table. (Filters can also be tables, such as FILTER(Sales, …) in Lesson 5.4 or DATESYTD(…) in Module 6.)
Principle
Principle: CALCULATE(expr, f1, f2, …) does four things in order:
- If there is a current row (row context), turn it into filters (context transition, Lesson 5.5).
- Apply removers like
ALLandREMOVEFILTERS(Lesson 5.3). - Apply each filter argument: replace existing filters on the same columns (or intersect, with KEEPFILTERS).
- Evaluate
exprin the new filter context.
Try it: split by Year. What does CALCULATE([Total Sales], 'Date'[Year] = 2024) show on the 2025 row? (2024 sales: the filter on Year is replaced.)
6. Worked example
Northbay’s manager wants each store’s Outdoor sales next to its total. Outdoor Sales = CALCULATE([Total Sales], Product[Category] = "Outdoor"), split by Store. The store filter stays (CALCULATE only touches Category), the category filter is set. Add Outdoor % = DIVIDE([Outdoor Sales], [Total Sales]) and you can see which stores lean on summer goods.
7. Practice
- Write
2025 Salesthat ignores the Year on the visual. - Write
Big Ticket Salesfor products with Product[UnitPrice] ≥ 50. - What does
CALCULATE([Total Sales])with no filters do in a measure? (Hint: nothing visible yet; Lesson 5.5.)
Answers
CALCULATE([Total Sales], 'Date'[Year] = 2025).CALCULATE([Total Sales], Product[UnitPrice] >= 50): earbuds and the camping chair.- In a measure with no row context it changes nothing; inside an iterator it turns the current row into a filter.
Check yourself
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.