ALLEXCEPT in DAX: hold one grouping, let the rest go

Yesterday ended on a promise: the first real practical case that needs a filter eraser, percentage of total. Today we make good on that promise with ALLEXCEPT, the function that holds one grouping in place while every other filter on your table falls away.

ALLEXCEPT is one of those DAX functions where the name says exactly what happens, and people still get stuck on what to feed it. So we take it in the order this whole series follows: first what the function wants as input and what it returns, and only then the matrix where you watch it work.

What ALLEXCEPT needs and does

The first argument is a base table from your model, something like Sales. The arguments after that are base columns of exactly that same table, so Sales[Category] is fine and Product[Color] is not, even when those tables are neatly related. A table expression such as FILTER(Sales, …) is not allowed in the first position either, and a calculated expression such as UPPER(Sales[Category]) cannot stand in for a column. ALLEXCEPT is strict about its input, and that is just as well, because its job is precise.

Inside CALCULATE it wipes every filter on that table except the ones on the columns you name, and it returns an adjusted filter context rather than a table you could filter further yourself. Filters from outside the table stay untouched: a slicer on a separate region table is none of its business.

The measure and the matrix

Put Sales[Category] on the rows of a matrix and use this measure:

Share within category =
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALLEXCEPT(Sales, Sales[Category]))
)

Every cell of that matrix is one small filter: the Bakery row calculates with Category=Bakery, the Snacks row with Category=Snacks, and nothing else. Without a slicer the numerator for Bakery reads 40,000, and ALLEXCEPT has nothing to wipe in the denominator, because the only filter present is the Category filter it is told to keep. So the denominator lands on the same 40,000 and every row shows 100%. That looks like a bug, but it is exactly what you asked for: the share of this category within this category.

With the slicer on North

Add a slicer on Sales[Region] and click North. That choice travels into every cell as an extra filter, so the numerator of the Bakery row becomes the Bakery revenue in North, 10,000. The real work happens in the denominator: ALLEXCEPT wipes the Region filter while Category=Bakery stays in place, so 40,000 remains there and the row lands on 25%. Numerator and denominator deliberately react differently to the same slicer, and that is the point: you see which part of total Bakery revenue comes from North.

That gives every row its own fraction. With North active, Bakery shows 25%, Snacks 16%, Beverages 25% and Frozen 40%: four different shares from one measure, and the same logic everywhere, because the numerator follows the full cell context and the denominator holds on to the category alone.

Strict about its input

You feel that strictness from the opening section the moment you try a column from a related table: ALLEXCEPT(Sales, Product[Color]) throws an error, even though your matrix can group by color through the relationship just fine. The fix is to bring the column into your fact table as an attribute, or to pick a different filter modifier.

Marco Russo and Alberto Ferrari of SQLBI recommend ALLEXCEPT for situations where you want exactly this behavior: every filter on the table gone, except on the columns you named. For the pattern “keep whatever is visible right now” they find REMOVEFILTERS with VALUES more predictable once you reuse the measure in a visual where the kept column is not part of the context. For today’s matrix it makes no difference, so the rule of thumb stays simple: keep what you need, and try your measure on a second visual before you roll it out across your report.

Tomorrow: ALLSELECTED

Now that you know how to hold one grouping and let the rest go, tomorrow we continue with ALLSELECTED, the function that respects what your user has picked in the slicer. To be continued.

This is day 4 of 5 in the DAX filter burst. Day 3, REMOVEFILTERS and ALL: which one to grab when, 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 Power BI and DAX step by step? Head to trainerbjorn.nl for training courses and a free study path for the PL-300 exam.