FILTER in DAX: when to use it and when to let CALCULATE do the work

24 July 2026

🎯 Beginner⏱ 15 minutes✍️ 2 exercises

What you'll learn here

Know what FILTER does as a function and what it returns
Recognize the Boolean form that CALCULATE handles faster on its own
Recognize the three cases where FILTER is the only route

FILTER is an escape hatch in DAX. Knowing that already changes how you use it. The rest is knowing which situations actually call for it, and which ones CALCULATE handles perfectly well on its own.

This article covers day two of a five-part DAX series on filter context. The question is simple: when do you need FILTER, and when does CALCULATE do the job better by itself? To answer that honestly, we start with what FILTER actually does as a function.

What FILTER actually does

FILTER takes two arguments: a table and a condition. It walks row by row through the table, checks whether the condition holds for each row, and returns a new table containing only the rows that passed the check. The result of FILTER is always a table. CALCULATE then uses that table to determine which rows it is allowed to see when evaluating a measure.

That is precise and logical. But it also means FILTER always iterates through the entire supplied table, regardless of how simple the condition is. That detail matters more than it first appears.

Same answer, more work

Take a straightforward question: revenue in the Netherlands. You can write that as:

CALCULATE([Revenue], Sales[Country] = "NL")

Or as:

CALCULATE([Revenue], FILTER(Sales, Sales[Country] = "NL"))

Both formulas return exactly the same number. The difference is in how the engine processes them. The first gives CALCULATE a Boolean filter argument on a single column. CALCULATE can jump directly to the Dutch rows without scanning the rest of the table. The second forces FILTER to walk the entire Sales table row by row, just to identify which rows carry “NL” in the Country column.

Microsoft’s official guidance is clear: avoid FILTER as a filter argument in CALCULATE when a Boolean expression on a single column is enough. The FILTER version does not produce a wrong answer. But it asks the engine for more work than necessary, without making the result any better.

Make your pick

Two ways to write it, one answer: which route does the engine take?

Where it breaks down on large tables

The row-by-row behavior only becomes a serious problem once you put a measure inside FILTER. Consider a Sales table with ten million rows and this formula:

CALCULATE([Revenue], FILTER(Sales, [RevenuePerCustomer] > 1000))

For each of those ten million rows, DAX separately computes what [RevenuePerCustomer] yields there, just to decide which rows stay. Click a slicer, and the whole process repeats for every visual on the page. The formula reads cleanly and the answer is correct, but the engine is running millions of small calculations that a different approach would have avoided.

This is the classic reason reports “suddenly” slow down: not a large dataset by itself, but a measure called per row inside FILTER on a large fact table. Recognise that pattern and you will see the next slow measure coming from a distance.

When FILTER belongs

There are situations where you cannot avoid FILTER, because the condition compares something that a Boolean expression on a single column cannot express.

Comparing two columns on the same row. A condition like Sales[Discount] > Sales[Cost] compares two columns on the same row. You cannot express that as a Boolean on a single column; two columns are involved. FILTER is the only route.

A calculation inside the condition. Filtering on Sales[Quantity] * Sales[UnitPrice] > 1000 requires computing something per row. CALCULATE’s Boolean filter arguments operate on columns, not row-level calculations. FILTER is unavoidable here.

Calling a measure per row. When you need to check, row by row, whether a measure meets a threshold, that is exactly what FILTER does. CALCULATE has no equivalent for this.

In these three cases, FILTER is not the convenient choice. It is the only choice. Use it deliberately, with that reason in mind.

Sorting drill

Do you really need FILTER here, or is a Boolean filter argument enough?

The rule

FILTER belongs in your measure when the condition compares something you cannot express with a Boolean on a single column. As long as a simple column condition is enough, let CALCULATE do the work and write the Boolean form.

Short enough to carry in your head, practical enough to apply in your next report.

📌

Takeaway

FILTER belongs in your measure when the condition compares something a Boolean on a single column cannot express: two columns on the same row, a calculation per row, or a measure per row. In every other case, write the Boolean form and let CALCULATE do the work.

✋

Try it yourself

Open an existing report and find the measure that feels slow. For every FILTER you come across: if it wraps a simple single-column condition, rewrite it to the Boolean form. If it calls a measure inside and the table is a large fact table, you have found the classic slowdown.

The next part of this series covers ALL and REMOVEFILTERS: the first two functions that remove filters rather than add them. Day 1, CALCULATE replaces the filter on that column, sits right before this one, and the cheat sheet for the whole series lays all five functions out on one scale. Want to work through the full DAX filter series? Find the trainings at trainerbjorn.nl.