DAX FILTER Function

Table iterator for complex conditions and multi-column constraints.

Syntax
FILTER( Table, FilterExpression )

Overview

FILTER iterates row-by-row over a table and returns a new virtual table containing only rows where the boolean expression is TRUE.

Because FILTER is an iterator, it establishes a row context over its input table. It is frequently passed as a filter argument into CALCULATE when evaluating measures against column values or checking multiple interdependent columns.

High Value Sales = 
CALCULATE(
    [Total Revenue],
    FILTER(Sales, Sales[Revenue] > 2500)
)

When to use FILTER

  • Filtering on a condition that a simple column filter cannot express, such as comparing two columns or a measure threshold.
  • Returning a filtered table to an iterator (SUMX, COUNTROWS) or to another table function.
  • Filtering by the result of a measure per item, for example customers whose sales exceed a target.

Worked examples

Filter the column, not the table

High-Price Sales =
CALCULATE (
    [Total Sales],
    FILTER ( ALL ( Sales[Unit Price] ), Sales[Unit Price] > 100 )
)

FILTER over a single column iterates only its distinct values. FILTER(Sales, Sales[Unit Price] > 100) would iterate every row of the fact table — and as a table filter it also carries the expanded table's columns, which changes semantics.

Filtering by a measure

Customers Above 10K =
COUNTROWS (
    FILTER ( VALUES ( Customer[Customer ID] ), [Total Sales] > 10000 )
)

FILTER iterates the visible customers and evaluates the measure for each one (context transition), keeping those above the threshold. Iterating a dimension like this is fine; iterating the fact table the same way usually is not.

Common mistakes

  • Using FILTER(Table, ...) unnecessarily for simple column checks like Customers[Country] = 'USA', which hurts performance.
  • Forgetting to wrap with ALL() when intending to evaluate across the entire table.
  • Iterating over massive tables with millions of rows without pre-filtering.

Performance notes

FILTER is an iterator that is evaluated row by row in the formula engine, so its cost grows with the number of rows it scans. The most common DAX performance problem is FILTER over an entire fact table where a column filter would do. As a rule: filter columns with simple predicates directly in CALCULATE, and use FILTER over the smallest table or column set that expresses the condition.

Frequently asked questions

What is the difference between FILTER and CALCULATE filters?
A boolean filter in CALCULATE (Product[Color] = "Red") is shorthand for FILTER(ALL(Product[Color]), ...) over one column and is efficient. FILTER is a general iterator that can express any condition but evaluates row by row.
Why is my measure with FILTER slow?
Most likely it filters an entire large table, or it evaluates a measure for every row of a large table. Filter a column or a dimension instead, or pre-compute the condition as a column in the source.
Does FILTER remove existing filters?
No. FILTER iterates the table in the current filter context. To ignore existing filters, iterate ALL(...) or REMOVEFILTERS-adjusted tables explicitly.

Related DAX concepts