Power BI DAX FILTER
The FILTER Function
FILTER goes through a table row by row, and keeps only the rows where a condition is true.
The result is a table, not a number.
Syntax
FILTER(table, filter)
| Argument | Meaning |
|---|---|
| table | The table to filter, like Sales. |
| filter | A condition that is true or false, checked for each row, like Sales[Quantity] >= 10. |
A card cannot show a table.
That is why FILTER is used inside other functions that need a table, such as CALCULATE, COUNTROWS and SUMX.
This page uses the Poke Mart model described in DAX Introduction, with the Amount and Cost columns from DAX RELATED, and the measures Total Sales from DAX Measures and Order Count from DAX Aggregation Functions.
See the Table That FILTER Returns
To see what FILTER returns, you can save its result as a calculated table:
- Select Table view on the left edge of Power BI Desktop.
- On the Table tools tab, select New table.
- Type the formula below in the formula bar, and press Enter.
Example
Big Order Rows = FILTER(Sales, Sales[Quantity] >= 10)
A new table appears in the Data pane.
The first rows look like this (the Date column is not shown):
| 123OrderID | ABCStoreID | ABCProductID | 123Quantity | 123Amount | 123Cost |
|---|---|---|---|---|---|
| 1002 | S2 | P01 | 19 | 3,800 | 2,090 |
| 1003 | S6 | P02 | 13 | 7,800 | 4,290 |
| 1005 | S4 | P04 | 19 | 5,700 | 2,850 |
| 1009 | S6 | P01 | 11 | 2,200 | 1,210 |
| 1011 | S1 | P01 | 28 | 5,600 | 3,080 |
| 1016 | S4 | P09 | 10 | 2,000 | 950 |
| ... | ... | ... | ... | ... | ... |
The table has the same columns as Sales, but only 971 of the 2,740 rows: the orders with a quantity of 10 or more.
Order 1001 (quantity 1) and order 1004 (quantity 9) are not in it.
Note: A calculated table is calculated when the data is loaded or refreshed.
Slicers do not change it.
This table was only for looking, so delete it now: right-click Big Order Rows in the Data pane, select Delete from model, and select Yes in the Delete table box.
Count Rows with COUNTROWS and FILTER
COUNTROWS counts the rows of a table.
Together with FILTER, it counts the rows that meet a condition:
Example
Big Order Count = COUNTROWS(FILTER(Sales, Sales[Quantity] >= 10))
In a matrix by Category, next to Order Count:
| Category | Order Count | Big Order Count |
|---|---|---|
| Balls | 1,035 | 556 |
| Medicine | 1,110 | 315 |
| Status Healers | 595 | 100 |
| Total | 2,740 | 971 |
Unlike the calculated table, this measure follows the filter context.
In the Balls row, FILTER only sees the 1,035 Ball orders, and 556 of them are big orders.
More than half of all Ball orders are for 10 or more balls.
CALCULATE with FILTER
FILTER can also be used as a filter in CALCULATE.
This measure shows the sales of the big orders only:
Example
Big Order Sales = CALCULATE([Total Sales], FILTER(Sales, Sales[Quantity] >= 10))
| Category | Total Sales | Big Order Sales |
|---|---|---|
| Balls | 4,447,200 | 2,727,000 |
| Medicine | 4,413,600 | 1,494,000 |
| Status Healers | 556,000 | 131,100 |
| Total | 9,416,800 | 4,352,100 |
The big orders are 971 of the 2,740 orders, but they bring in 4,352,100 of the 9,416,800 in sales.
A Simpler Way for This Condition
The condition Sales[Quantity] >= 10 uses one column of one table.
It follows the rules for simple filters, so FILTER is not needed here.
You can write Big Order Sales like this:
Example
Big Order Sales = CALCULATE([Total Sales], Sales[Quantity] >= 10)
It returns the same results, and it is faster.
You will see why below.
When You Need FILTER
Use FILTER when a condition cannot be a simple filter.
The most common case is a condition on a measure.
Example: the sales of the best-selling products, the ones with Total Sales over 1,000,000:
Example
Top Product Sales = CALCULATE([Total Sales], FILTER(Products, [Total Sales] > 1000000))
FILTER goes through the 10 rows of the Products table.
For each product, it calculates Total Sales for that product, and keeps the product if the result is over 1,000,000. Four products pass: Poke Ball (1,821,600), Great Ball (1,677,600), Super Potion (1,545,600) and Potion (1,527,000).
Then CALCULATE adds up the sales of those four products.
| Category | Total Sales | Top Product Sales |
|---|---|---|
| Balls | 4,447,200 | 3,499,200 |
| Medicine | 4,413,600 | 3,072,600 |
| Status Healers | 556,000 | |
| Total | 9,416,800 | 6,571,800 |
No Status Healer sells for more than 1,000,000, so that row is blank.
Note: The condition is checked in the current filter context.
In a matrix by Year, a product must sell for more than 1,000,000 in that year alone.
No product does in 2024, so the 2024 cell is blank.
In 2025, only Poke Ball does (1,018,400).
FILTER is also needed when one condition uses columns from two different tables, for example Quantity from the Sales table and Region from the Stores table.
Performance: Filter Columns, Not Tables
FILTER(Sales, ...) checks the condition for every row of the Sales table.
That is 2,740 rows here, and it can be millions in a large model.
A simple filter like Sales[Quantity] >= 10 only has to check the different values of one column.
The Quantity column has only 30 different values (1 to 30).
Power BI stores data by column, so this kind of filter is very fast.
Microsoft's advice:
- Use simple column filters in CALCULATE whenever you can.
- Use FILTER only when you need it: for conditions on measures, and for complex conditions.
- When you need FILTER, filter a small table if you can, like Products (10 rows) rather than Sales.
What You Learned
- FILTER returns a table with the rows where a condition is true.
- COUNTROWS(FILTER(...)) counts the rows that meet a condition.
- FILTER can be a filter in CALCULATE, and it is needed for conditions on measures.
- For a simple condition on one column, a simple filter in CALCULATE is shorter and faster.