DAX Filter Functions

Filtering and context manipulation in DAX

The FILTER() function in DAX is one of the most powerful and essential functions for performing advanced filtering and context manipulation in your data model. It’s especially useful inside CALCULATE().

Syntax

FILTER(table, filter_expression)
  • table: The table you want to filter.
  • filter_expression: A boolean expression that returns TRUE or FALSE for each row.

What It Does?

  • Returns a table that contains only rows that meet the condition.
  • Often used with CALCULATE() to override or refine filter context.

Example

High Sales = 
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Sales, Sales[Amount] > 1000)
)

This measure calculates the sum of sales where the Amount is greater than 1000.

How It Works

  • FILTER() does not return a scalar (single value) it returns a table.
  • It evaluates row by row, applying the condition.
  • It’s most effective inside CALCULATE(), which can take a table-based filter and apply it to change context.

Common Use Cases

Use CaseExample
Filter rows by conditionFILTER(Sales, Sales[Amount] > 500)
Use in CALCULATE for dynamic measuresCALCULATE(SUM(...), FILTER(...))
Create context-aware logicFor custom KPIs, ratios, or YoY analysis

Important Notes

  • Avoid using FILTER() unnecessarily it’s slower than native filtering like Sales[Amount] > 1000 directly in CALCULATE().

  • It’s required when:

    • You need complex conditions.
    • You’re filtering on a different table than the one being aggregated.

Real-World Example

Top Customers Sales = 
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(Customers, Customers[Rank] <= 10)
)

Returns the total sales only for the top 10 customers.

No questions available.