<!-- https://learnsql.cpetoday.com/functions/dax/filter -->
# FILTER

Language: DAX
Category: Filter and context
Syntax: FILTER(<table>, <filter>)
Summary: Returns the rows of a table where a condition is true, checked one row at a time.
Returns: A table.
Level: Intermediate
Related: CALCULATE, CALCULATETABLE, ALL, VALUES, TOPN, ADDCOLUMNS

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

```dax
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.

```dax
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 `ALL` or `VALUES` when 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 `CALCULATE` or an aggregator, not directly as a measure result.
- For a condition on one column with a fixed value, a Boolean filter in `CALCULATE` is 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."
