Power BI’s FILTER Function: A Complete Guide with Examples

If you’ve ever tried to build a measure that needed more filtering power than a basic slicer, you’ve probably hit a wall.

That’s where DAX’s FILTER function comes in.

While visual filters and slicers work great for basic scenarios:

FILTER gives you row-level control to create sophisticated calculations that respond dynamically to your business logic.

What Is the FILTER Function?

In DAX, FILTER returns a table containing only the rows that meet the criteria you define.

Unlike visual filters and slicers (which work at the UI level), FILTER works inside your calculation, so you control exactly which rows are included in the result.

Here’s the official DAX syntax:

FILTER(<table>, <filter_expression>)

The parameters are straightforward:

• <table> – The table you want to apply the filter to
• <filter_expression> – A Boolean expression that determines which rows are returned

The key thing to remember: FILTER returns a table, not a single value.

This table contains only the rows that meet your criteria, making it perfect for use inside other DAX functions like CALCULATE, SUMX, or COUNTROWS.

Think of FILTER as creating a temporary, custom view of your table and then running calculations against just that view.

If this is all sounding a bit confusing, you might want to check out our Introduction to DAX so you can get your footing!

When to Use the FILTER Function

You’ll reach for FILTER when standard filters fall short:

• Dynamic filtering – When you need to filter based on calculated values or measures
• Complex conditions – When your filter logic depends on multiple calculations or requires AND/OR logic
• Cross-table filtering – When you need to filter one table based on calculations from another

Consider a common business scenario: a sales dashboard, where stakeholders wanted to see “products that performed above the category average.”

Standard filters couldn’t handle this because the threshold changed dynamically based on the data.

FILTER made it possible in a single measure by allowing calculations within the filter logic.

Basic Examples

Let’s start with simple examples using a sales dataset.

Our sample data includes a Sales and a Product table.

Example 1: Showing Sales Greater Than 5000

This measure filters the Sales table to include only transactions above 5000, then sums those amounts.

The table below shows comparison of data returned by a sales measure with and without the FILTER function applied.

Example 2: Showing The Last 30 Days Only

This creates a rolling 30-day sales total by filtering out any sales older than 30 days from today.

The filter condition dynamically updates each day.

These examples show FILTER working inside CALCULATE, which is incredibly powerful for creating dynamic measures.

The screenshots below show three approaches to display sales greater than 5000, all producing identical results.

The first two use a standard Sum of Sales measure filtered by visual-level filters and slicers.

The third uses our High Value Sales measure with FILTER built into the DAX.

The key difference: FILTER provides control within measures, while visual filters and slicers work at the report layer.

Visual-level filter
Slicer button
FILTER function

FILTER in CALCULATE

While CALCULATE can handle simple filters (static values):

It can’t evaluate complex expressions in the filter argument. That’s where FILTER comes in:

This measure filters for sales that are 150% above the average sale amount.

Notice how the filter condition includes a calculation (AVERAGE) which is something simple filter arguments can’t handle.

Advanced Examples

1) Multiple Conditions with AND and OR

This combines two conditions: sales must be both high-value (>5000) AND recent (last 30 days) using ‘&&’.

Both conditions must be true for a row to be included.

This uses OR logic with ‘||’ to include sales that are either high-value (>5000) OR from the Electronics category.

Only one condition needs to be true for a row to be included.

2) Dynamic Filtering Based on Another Table

This counts products whose total sales exceed the overall average.

The filter works across table relationships, evaluating each product’s performance against the calculated average.

However, note that this only works for tables with established relationship.

3) Using Variables for Performance:

Variables store the calculated values (threshold and date in the example) once, then reuse them for each row evaluation, improving performance over recalculating these values repeatedly.

FILTER context functions such as ALL, ALLEXCEPT, and KEEPFILTERS let you control which filters are applied or removed during a calculation.

They’re essential for overriding slicers, ignoring certain filters, or fine-tuning how your measures respond to user selections.

Mastering how they work with FILTER opens advanced scenarios like comparing performance against unfiltered totals.

For a comprehensive introduction, check out the official documentation at Filter functions (DAX) – DAX | Microsoft Learn.

Performance Considerations

FILTER can slow down your reports if not used carefully:

• Row-by-row evaluation – FILTER checks every row, which gets expensive on large datasets
• Limit the input table – Use the smallest relevant table as your starting point
• Store calculations in variables – Avoid recalculating the same value for every row

Poor performance example (recalculates average for every row):

Better performance example (calculates average once):

Trainer Insight: In datasets with millions of rows, optimizing FILTER becomes critical.

Start by limiting the input table to the smallest relevant dataset instead of passing large fact tables directly.

Use variables to store repeated calculations so they’re evaluated once, not per row.

Whenever possible, replace FILTER with simpler direct filter arguments in CALCULATE for better performance.

Common Mistakes

Mistake 1: Using FILTER when simple filters work

Unnecessarily complex:

Simpler and faster:

Mistake 2: Forgetting that FILTER returns a table

FILTER never returns a number – it always produces a table.

If you forget this and try to use it as a numeric value, your measure won’t work.

It always needs to sit inside a function that can consume a table.

Mistake 3: Filtering Without Relationships 

If your data model relationships aren’t properly set up, FILTER may not work as you expect.

For example, trying to filter one table based on an unrelated column in another table will usually give unexpected results.

Practical Use Cases

1. Exception reporting

FILTER is ideal for spotting unusual or high-value transactions that may need attention, such as large sales orders or irregular expense claims.

By highlighting these outliers, managers can quickly investigate and take corrective action.

2. Dynamic thresholds

Instead of relying on fixed cut-offs, FILTER allows you to create measures that adapt to changing targets, like “above average sales” or “greater than last month.”

This makes your reports far more flexible and relevant to real-world business conditions.

3. Cross-table filtering

When your logic depends on related tables, FILTER can include or exclude rows based on data in another table — for example, showing only sales from products that are still in stock.

This kind of control isn’t possible with slicers or simple filters alone.

Conclusion

The FILTER function transforms static calculations into dynamic, context-aware measures that adapt to your business logic.

Start with simple examples on small datasets to understand how FILTER behaves, then build more complex scenarios.

The combination of FILTER with CALCULATE, ALL, and VALUES opens up nearly limitless possibilities for sophisticated analytics.

Remember: FILTER gives you row-level control, but with that power comes responsibility for performance.

Use variables, limit your input tables, and always test with realistic data volumes.

Master FILTER, and you’ll unlock a new level of analytical sophistication in your Power BI reports.