How to Use FILTER in DAX in Power BI: Simple Guide

In Power BI, use the FILTER function in DAX to return a table filtered by a condition you specify. It takes a table and a filter expression, then returns only rows that meet that condition, enabling dynamic and flexible data analysis.

Syntax

The FILTER function syntax is:

• FILTER(table, filter_expression)

table: The table you want to filter.

filter_expression: A condition that each row must satisfy to be included.

DAX

FILTER(<table>, <filter_expression>)

Example

This example shows how to filter the Sales table to include only rows where Sales[Amount] is greater than 1000.

We create a measure that sums the filtered sales amounts.

DAX

Total Sales Over 1000 =
SUMX(
    FILTER(Sales, Sales[Amount] > 1000),
    Sales[Amount]
)

Output

Returns the sum of all sales amounts greater than 1000 from the Sales table.

Common Pitfalls

Common mistakes when using FILTER include:

• Using FILTER inside a measure without context can cause performance issues.
• Forgetting that FILTER returns a table, so it must be used with an aggregator like SUMX or inside another function expecting a table.
• Writing filter expressions that do not return TRUE/FALSE for each row.

Example of wrong and right usage:

DAX

/* Wrong: FILTER used alone in a measure without aggregation */
Wrong Measure = FILTER(Sales, Sales[Amount] > 1000)

/* Right: Use FILTER inside SUMX to aggregate */
Right Measure = SUMX(FILTER(Sales, Sales[Amount] > 1000), Sales[Amount])

Quick Reference

Parameter	Description
table	The table to filter
filter_expression	Condition that returns TRUE for rows to keep
Returns	A filtered table with rows meeting the condition

Key Takeaways

FILTER returns a table filtered by a condition you specify.

Always use FILTER with an aggregator like SUMX to get a single value in measures.

Filter expressions must return TRUE or FALSE for each row.

FILTER is useful for dynamic, row-level filtering in DAX calculations.