Mijn notitiesPraktische Power BI tips
ALLEXCEPT in DAX: één groepering vasthouden, de rest loslaten
Wat je in deze les leert
Gisteren eindigde met een belofte: het eerste echte praktijkgeval waarvoor je een filterwisser nodig hebt, percentage van totaal. Vandaag lossen we die belofte in met ALLEXCEPT, de functie waarmee je één groepering vasthoudt terwijl alle andere filters op je tabel losgaan.
ALLEXCEPT is één van die DAX-functies waar de naam precies zegt wat er gebeurt, en waar mensen alsnog vastlopen op wat ze erin moeten stoppen. Daarom doen we het in de volgorde die deze hele serie aanhoudt: eerst wat de functie als input wil en wat hij teruggeeft, dan pas de matrix waarin je hem ziet werken.
Wat ALLEXCEPT nodig heeft en doet
Het eerste argument is een basistabel uit je model, zoiets als Sales. De argumenten daarna zijn basiskolommen van precies diezelfde tabel, dus Sales[Categorie] mag wel en Product[Kleur] niet, ook al zijn die tabellen netjes gerelateerd. Een tabeluitdrukking zoals FILTER(Sales, …) mag evenmin op de plek van het eerste argument, en een berekende expressie zoals UPPER(Sales[Categorie]) niet op de plek van een kolom. ALLEXCEPT is streng op zijn input, en dat is maar goed ook, want zijn werk luistert nauw.
Binnen CALCULATE veegt hij alle filters op die tabel weg, behalve op de kolommen die je noemt, en hij geeft een aangepaste filtercontext terug in plaats van een tabel die je zelf verder kunt filteren. Filters van buiten die tabel laat hij bovendien staan: een slicer op een losse regiotabel raakt hij niet aan.
De measure en de matrix
Zet Sales[Categorie] op de rijen van een matrix en gebruik deze measure:
Aandeel binnen categorie =
DIVIDE(
SUM(Sales[Bedrag]),
CALCULATE(SUM(Sales[Bedrag]), ALLEXCEPT(Sales, Sales[Categorie]))
)
Elke cel van die matrix is één klein filter: de Bakery-rij rekent met Categorie=Bakery, de Snacks-rij met Categorie=Snacks, en verder niets. Zonder slicer telt de teller voor Bakery €40.000, en heeft ALLEXCEPT in de noemer niets te wissen, want er staat alleen een Categorie-filter en die moet juist blijven. De noemer landt dus op dezelfde €40.000 en elke rij toont 100%. Dat oogt als een fout, maar het is precies wat je hebt gevraagd: het aandeel van deze categorie binnen deze categorie.
Met de slicer op Noord
Zet er een slicer op Sales[Regio] bij en klik Noord aan. Die keuze reist als extra filter mee naar elke cel, dus de teller van de Bakery-rij wordt de Bakery-omzet in Noord, €10.000. In de noemer gebeurt het echte werk: ALLEXCEPT wist het Regio-filter terwijl Categorie=Bakery blijft staan, dus daar blijft €40.000 staan en de rij komt op 25% uit. Teller en noemer reageren bewust anders op dezelfde slicer, en dat is geen bug: je ziet welk deel van de totale Bakery-omzet uit Noord komt.
Zo krijgt elke rij zijn eigen breuk. Met Noord actief toont Bakery 25%, Snacks 16%, Beverages 25% en Frozen 40%: vier verschillende aandelen uit één measure, en overal dezelfde logica, want de teller volgt de volledige celcontext en de noemer houdt alleen de categorie vast.
Streng op zijn input
Die strengheid uit het begin voel je zodra je een kolom uit een gerelateerde tabel probeert: ALLEXCEPT(Sales, Product[Kleur]) levert een foutmelding op, ook al kan je matrix via de relatie prima op kleur groeperen. De oplossing is dan de kolom als attribuut in je feittabel opnemen, of een andere filter-modifier kiezen.
Marco Russo en Alberto Ferrari van SQLBI raden ALLEXCEPT aan voor situaties waarin je precies dit gedrag wilt: alle filters op de tabel weg, behalve op de expliciet genoemde kolommen. Voor het patroon “houd vast wat er nu zichtbaar is” gedraagt REMOVEFILTERS met VALUES zich volgens hen voorspelbaarder zodra je de measure hergebruikt in een visual waar de bewaarde kolom niet in de context zit. Voor de matrix van vandaag maakt dat niets uit, dus de vuistregel blijft eenvoudig: bewaar wat je nodig hebt, en test je measure even op een tweede visual voordat je hem door je rapport uitrolt.
ALLEXCEPT(Tabel, Kolom) zegt: houd de filter op deze kolom vast en veeg de andere filters op deze tabel weg. Het eerste argument is een basistabel, alles daarna een basiskolom van diezelfde tabel, en wat terugkomt is een aangepaste filtercontext, geen tabel.
Open een rapport met een aandeel-measure, zet hem in een matrix en reken één cel na: wat telt de teller, wat wist de noemer, en wat blijft daar staan? Zet dan een slicer aan en reken dezelfde cel opnieuw na. Bewegen teller en noemer allebei mee, dan houdt je filter-modifier niet vast wat je dacht.
Morgen: ALLSELECTED
Nu je weet hoe je één groepering vasthoudt en de rest loslaat, gaan we morgen door met ALLSELECTED, de functie waarmee je respecteert wat de gebruiker zelf in de slicer heeft gekozen. Wordt vervolgd.
Dit is dag 4 van 5 in de DAX filter-burst. Dag 3, REMOVEFILTERS en ALL: welke pak je wanneer, staat hierboven als voorganger, en de spiekbrief van de hele serie legt de vijf functies op één schaal.
Praktisch, helder en direct toepasbaar aan de slag met DAX? Op trainerbjorn.nl vind je trainingen en een gratis leerpad voor de PL-300 certificering.