The DAX filter cheat sheet: five functions, two jobs

For five days straight, a new DAX function showed up every morning: CALCULATE, FILTER, ALL and REMOVEFILTERS, ALLEXCEPT, ALLSELECTED. You have seen all five at work, on the same table with the same slicer, and there is still that moment where an empty measure leaves you unsure which one you need. That is not a memory problem. The five posts were never laid out side by side. This article does that, and puts the whole series on one scale.

Two jobs inside CALCULATE

All five functions from this week live inside the same CALCULATE, and inside it they only have two jobs: turn a filter on, or take a filter away. CALCULATE is where that happens, and it is also where the filter-removing functions get their role in the first place. ALL and REMOVEFILTERS do not exist in that same form outside CALCULATE; REMOVEFILTERS only works as a modifier inside CALCULATE or CALCULATETABLE, and ALLEXCEPT and ALLSELECTED have no existence outside that context at all. The moment you know which of the two jobs a function belongs to, half the doubt disappears.

Turning a filter on: CALCULATE and FILTER

Give CALCULATE a filter argument, and your filter replaces the filter that came from outside, exactly on the column you name. Every other column stays untouched. As long as your condition is a simple comparison on one column, that is all you need, and Microsoft’s own best-practice page even advises against using FILTER while a condition that simple will do.

FILTER earns its place once the condition has to do more: compare two columns, run a calculation, or call a measure row by row. It then walks the table row by row and hands back the rows that pass your check. The difference between the two is not what they filter, it is how complicated the condition needs to be to get there.

The removal side is a scale

This is where the real work of the series sits, because the four filter-removing functions are not four separate tricks. They are positions on one scale, from wiping everything to wiping only what the user has not clicked themselves.

Take one report: Sales with Category on the rows, a slicer on Sales[Region] with North selected, and look at the Bakery row. Every variant below uses the same 10,000 euro numerator, the same measure shell DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), <modifier>)), and only the modifier in the denominator changes.

With ALL(Sales) or REMOVEFILTERS(Sales), everything gets wiped: the denominator becomes 100,000 euro, and the Bakery row shows 10%. Inside CALCULATE, ALL and REMOVEFILTERS are functionally identical; the choice between the two is a readability choice and nothing more.

With ALLEXCEPT(Sales, Sales[Category]), the grouping on Category stays in place. The denominator drops to 40,000 euro, and the same Bakery row comes out at 25%, because there is more left to divide against than in the previous variant.

With ALLSELECTED(Sales[Category]), only what the user clicked themselves survives, in this case the region selection on North. The denominator drops further, to 25,000 euro, and the Bakery row shows 40%.

Three variants, the same numerator, three completely different outcomes, and the only thing that changes is how much stays in the denominator.

The question for your denominator

The moment you are staring at an empty measure, ask yourself one question: how much is allowed to stay in my denominator? If you need to filter, reach for CALCULATE, and only add FILTER once your condition calculates something or compares two columns. If you need to remove filters, walk the scale. ALL(Sales) and REMOVEFILTERS(Sales) wipe everything. ALLEXCEPT keeps your own grouping. ALLSELECTED keeps the user’s selection. Five functions, two jobs, one question.

The full series

Want to keep the cheat sheet next to your screen? Flip the five cards below, in the order of the scale.

This cheat sheet closes the DAX filter burst: day one on CALCULATE, day two on FILTER, day three on ALL and REMOVEFILTERS, day four on ALLEXCEPT, day five on ALLSELECTED. Each part reads on its own, but together they lay out exactly the scale above.

Want to run this against your own model, or stuck on a measure that will not behave? I am happy to think it through with you, find me at trainerbjorn.nl.