Downloads
DAXFilter and contextIntermediate

FILTER

Returns the rows of a table where a condition is true, checked one row at a time.

Syntax
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

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

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."

Related functions

All DAX functions · View as Markdown · DAX fundamentals