DAX FILTER function
Returns the rows of a table that meet a condition.
FILTER goes through a table row by row and keeps the rows where the condition is true. It returns a table, so it is used inside another function: COUNTROWS, SUMX or CALCULATE.
Syntax
FILTER(<table>, <filter>)| Argument | Required | Description |
|---|---|---|
| table | Required | The table to filter, or an expression that returns a table. |
| filter | Required | A condition checked for each row, like financials[Sales] > 100000. |
Returns: A table with the rows that meet the condition.
Examples
Calculated on the free practice data (table financials), with [Total Sales] = SUM(financials[Sales]) and [Total Profit] = SUM(financials[Profit]).
1. Sales from large orders
Large Order Sales = CALCULATE([Total Sales], FILTER(financials, financials[Units Sold] >= 2000))| Segment | Large Order Sales |
|---|---|
| Channel Partners | 908,823.12 |
| Enterprise | 11,303,240.63 |
| Government | 24,620,702.55 |
| Midmarket | 1,547,803.50 |
| Small Business | 23,390,971.50 |
| Total | 61,771,541.30 |
Only the sales rows with 2,000 units or more count toward each segment.
Good to know
- For a simple condition on one column, CALCULATE([Total Sales], financials[Country] = "Canada") is faster than FILTER over the whole table.
- Use FILTER when the condition needs several columns or a measure.
Related functions
Official reference: FILTER function on Microsoft Learn