All lessons

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

Try it: write the DAX

Write Home Sales: Total Sales for the Home category only, whatever the visual shows. Split by Store.

Press Run to see the result.

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?

Try it: write the DAX

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

Try it: write the DAX

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.

Try it: write the DAX

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.

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:

  1. If there is a current row (row context), turn it into filters (context transition, Lesson 5.5).
  2. Apply removers like ALL and REMOVEFILTERS (Lesson 5.3).
  3. Apply each filter argument: replace existing filters on the same columns (or intersect, with KEEPFILTERS).
  4. Evaluate expr in 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

  1. Write 2025 Sales that ignores the Year on the visual.
  2. Write Big Ticket Sales for products with Product[UnitPrice] ≥ 50.
  3. What does CALCULATE([Total Sales]) with no filters do in a measure? (Hint: nothing visible yet; Lesson 5.5.)
Answers
  1. CALCULATE([Total Sales], 'Date'[Year] = 2025).
  2. CALCULATE([Total Sales], Product[UnitPrice] >= 50): earbuds and the camping chair.
  3. In a measure with no row context it changes nothing; inside an iterator it turns the current row into a filter.

Check yourself

1In a table split by Category, what does CALCULATE([Total Sales], Product[Category] = "Home") return on the Grocery row?
2How are several filter arguments in one CALCULATE combined?
3What does KEEPFILTERS change?

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.