FILTER
Returns the rows of a table where a condition is true, checked one row at a time.
FILTER(<table>, <filter>)Returns: A table.
What it does
FILTER goes through a table one row at a time and keeps the rows where your condition is TRUE. Use it when the condition needs a measure, or compares two columns, which a simple Boolean filter cannot do. A typical question: "Which stores have total sales above a target?"
Syntax and arguments
- table: the table to scan. Give it the smallest table that works, such as
VALUES(Stores[StoreName]), rather than the whole Sales table. - filter: a Boolean expression tested on each row. It may use measures, which are evaluated for each row.
Example
A regional controller wants a count of stores that are above a sales target. The target sits in a variable so it is easy to change.
Stores Above Target =
VAR Target = 1000000
RETURN
COUNTROWS (
FILTER (
VALUES ( Stores[StoreName] ),
[Total Sales] > Target
)
)
For each store in the current view, FILTER tests Total Sales against the target. COUNTROWS counts the stores that pass. The result is a whole number that updates with the slicers.
Example: a second use
Find sales lines where the amount charged is below the recorded cost. A Boolean filter cannot compare two columns of the same table in this way, so FILTER is the right tool.
Lines Sold Below Cost =
COUNTROWS (
FILTER (
Sales,
Sales[SalesAmount] < Sales[TotalCost]
)
)
This checks every row of Sales, so keep the rest of the report filters tight. The count is a quick exception check for a margin review.
Things to watch
- FILTER over a large table is slow because it visits every row. Filter a single column with
ALLorVALUESwhen you can. - Inside
CALCULATE,FILTER(ALL(Table), ...)removes the existing filter on that table.FILTER(Table, ...)works from the current filter. Choose on purpose. - FILTER returns a table. Use it inside
CALCULATEor an aggregator, not directly as a measure result. - For a condition on one column with a fixed value, a Boolean filter in
CALCULATEis simpler and faster. - A blank value fails most comparisons, so a blank measure usually drops the row.
In business terms
Finance uses FILTER to build exception lists: accounts above a threshold, stores below budget, lines with negative margin, customers over a credit limit. It is the building block for "show me only the items that break the rule."