Module 5: Filter context and CALCULATE · Lesson 5.4 · about 16 min
FILTER and table filters
FILTER walks a table row by row and keeps the rows where a condition is true. Use it inside CALCULATE for conditions a simple filter can't express, and see which rows it keeps and drops.
Module 5 · about 16 minutes · read first: 5.2 and 4.3. You’ll be able to use FILTER for conditions that need row-by-row tests or measures.
1. Why this matters
Many business rules can’t be written as “column = value”: big orders, products above a sales threshold, customers who bought twice. FILTER handles them all.
2. Keep the rows that pass
Write Big Line Sales: sales from order lines of $100 or more, using FILTER(Sales, Sales[Amount] >= 100) inside CALCULATE. Trace a row to watch FILTER keep and drop lines.
Press Run to see the result.
Total Sales = SUM(Sales[Amount])
Big Line Sales = CALCULATE([Total Sales], FILTER(Sales, Sales[Amount] >= 100)) Trace a category: after the filter context, FILTER goes through the visible Sales rows and keeps the ones with Amount ≥ 100. CALCULATE then uses the kept rows as the filter for the sum.
FILTER(table, condition) is an iterator: it creates a row context for each row of the table, tests the condition, and returns the rows that pass. It returns a table, so it’s used inside CALCULATE, COUNTROWS, SUMX and other functions that take a table.
3. Filtering with a measure
Write Strong Products: how many products have Total Sales above 40,000 in the current context. Iterate the product list with FILTER and a measure.
Press Run to see the result.
The condition goes after the comma: [Total Sales] > 40000. For each product, the measure is worked out for that product (context transition, Lesson 5.5).
Total Sales = SUM(Sales[Amount])
Strong Products = COUNTROWS(FILTER(VALUES(Product[Product]), [Total Sales] > 40000)) For each product in VALUES(Product[Product]), the condition evaluates [Total Sales] for that product: the measure reference turns the current product row into a filter (context transition, the next lesson). Split by Year to see how many products cross the line each year.
4. Good habits
- Don’t use FILTER for simple column conditions.
CALCULATE([Total Sales], Product[Category] = "Home")is clearer and faster thanCALCULATE([Total Sales], FILTER(Product, Product[Category] = "Home")). - Filter the smallest table that works.
FILTER(VALUES(Product[Product]), …)(12 rows) beatsFILTER(Sales, …)(7,328 rows) when the test is about products. - Filtering a whole fact table inside CALCULATE (like
FILTER(Sales, …)) is fine for row-level tests on that table, but on very large tables a column filter is faster:CALCULATE([Total Sales], Sales[Amount] >= 100)works too, because the test uses one column.
Principle
Principle: FILTER = iterate + test + keep. In set notation, for a table and condition :
The table is evaluated first, in the current filter context, so FILTER can only keep rows that are already visible, never bring back hidden ones. Use ALL inside it (FILTER(ALL(…), …)) when you need rows the context has hidden.
Try it: in a 2024 context, can FILTER(Sales, Sales[Amount] > 0) return 2025 rows? (No: 2025 rows aren’t visible to begin with.)
5. Worked example
How much of each category’s sales come from multi-unit lines? Multi Unit Sales = CALCULATE([Total Sales], Sales[Quantity] >= 2) (a one-column test, so the shorthand works). Then Multi Unit % = DIVIDE([Multi Unit Sales], [Total Sales]). In Northbay’s data a little under half of the lines have two or more units, and the categories are close to each other.
6. Practice
- Write
Lines Over 200: the number of Sales rows with Amount over 200. - Write
Products Under 20k: products with sales below 20,000 in the current context. - What’s wrong with
CALCULATE([Total Sales], FILTER(Product, [Total Sales] > 40000))on very large models, and what’s a better first argument?
Answers
COUNTROWS(FILTER(Sales, Sales[Amount] > 200))orCALCULATE(COUNTROWS(Sales), Sales[Amount] > 200).COUNTROWS(FILTER(VALUES(Product[Product]), [Total Sales] < 20000)).- It iterates every column of the Product table;
VALUES(Product[Product])iterates just the product names and gives the same result here.
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.