De spiekbrief van de DAX filter-burst: vijf functies, twee taken

Vijf dagen lang stond er elke ochtend een nieuwe DAX-functie op je scherm: CALCULATE, FILTER, ALL en REMOVEFILTERS, ALLEXCEPT, ALLSELECTED. Je hebt ze alle vijf zien werken, met dezelfde tabel en dezelfde slicer, en toch is er een moment dat je bij een lege measure niet meer weet welke je nodig hebt. Dat komt doordat de vijf posts nooit naast elkaar stonden. Dit artikel doet dat wel, en legt de hele serie op één schaal.

Twee taken binnen CALCULATE

Alle vijf de functies van deze week leven binnen dezelfde CALCULATE, en daarbinnen hebben ze maar twee taken. Ze zetten een filter aan, of ze halen er een weg. CALCULATE is de plek waar dat gebeurt, en ook de plek waar de weghaalfuncties hun rol krijgen. ALL en REMOVEFILTERS bestaan buiten CALCULATE namelijk niet in dezelfde vorm; REMOVEFILTERS werkt uitsluitend als modifier binnen CALCULATE of CALCULATETABLE, en ALLEXCEPT en ALLSELECTED hebben helemaal geen bestaan buiten die context. Zodra je van een functie weet in welke van de twee taken hij zit, valt de helft van de twijfel al weg.

Aanzetten: CALCULATE en FILTER

Geef je CALCULATE een filterargument mee, dan vervangt jouw filter het filter dat van buiten kwam, en dat gebeurt precies op de kolom die je noemt. De andere kolommen blijven ongemoeid. Zolang je voorwaarde een simpele vergelijking op één kolom is, is dat alles wat je nodig hebt, en Microsoft raadt in de eigen best-practicepagina zelfs af om FILTER te gebruiken zolang zo’n simpele voorwaarde volstaat.

FILTER hoort erbij zodra die voorwaarde meer moet doen: twee kolommen met elkaar vergelijken, rekenen, of per rij een measure aanroepen. Hij loopt dan de tabel rij voor rij langs en geeft de rijen terug die door je check kwamen. Het verschil tussen de twee zit dus in hoe ingewikkeld de voorwaarde is die daarvoor nodig is, want wat ze filteren is hetzelfde.

De weghaalkant is een schaal

Hier zit het echte werk van deze serie, want de vier weghaalfuncties zijn posities op één schaal, van alles wissen tot alleen datgene wissen dat de gebruiker zelf niet heeft aangeklikt.

Neem één rapport: Sales met Categorie op de rijen, een slicer op Sales[Regio] met Noord aangeklikt, en kijk naar de rij Bakery. Elke variant hieronder gebruikt dezelfde teller van €10.000, dezelfde measureschil DIVIDE(SUM(Sales[Bedrag]), CALCULATE(SUM(Sales[Bedrag]), <modifier>)), en alleen de modifier in de noemer verandert.

Met ALL(Sales) of REMOVEFILTERS(Sales) wordt alles gewist: de noemer wordt €100.000, en de rij Bakery toont 10%. Binnen CALCULATE zijn ALL en REMOVEFILTERS functioneel identiek; de keuze tussen die twee is een leesbaarheidskeuze en verder niets.

Met ALLEXCEPT(Sales, Sales[Categorie]) blijft de groepering op Categorie staan. De noemer zakt naar €40.000, en dezelfde rij Bakery komt op 25% uit, want er blijft nu meer over om tegen te delen dan bij de vorige variant.

Met ALLSELECTED(Sales[Categorie]) blijft alleen overeind wat de gebruiker zelf heeft aangeklikt, in dit geval de regioselectie op Noord. De noemer zakt verder naar €25.000, en de rij Bakery toont 40%.

Drie varianten, dezelfde teller, drie totaal verschillende uitkomsten, en het enige dat verschilt is hoeveel er in de noemer blijft staan.

De vraag voor je noemer

Op het moment dat je voor een lege measure zit, stel je jezelf dus één vraag: hoeveel mag er in mijn noemer blijven staan? Moet je filteren, dan pak je CALCULATE, en pas als je voorwaarde rekent of twee kolommen vergelijkt zet je er FILTER in. Moet je wissen, dan loop je de schaal af. ALL(Sales) en REMOVEFILTERS(Sales) wissen alles. ALLEXCEPT houdt je eigen groepering vast. ALLSELECTED houdt de selectie van de gebruiker vast. Vijf functies, twee taken, één vraag.

De hele serie

Wil je de spiekbrief naast je scherm houden? Flip de vijf kaarten hieronder, in de volgorde van de schaal.

Deze spiekbrief sluit de DAX filter-burst af: dag 1 over CALCULATE, dag 2 over FILTER, dag 3 over ALL en REMOVEFILTERS, dag 4 over ALLEXCEPT en dag 5 over ALLSELECTED. Elke aflevering staat los te lezen, maar samen leggen ze precies de schaal neer die hierboven staat.

Wil je dit met je eigen model doornemen, of loop je vast op een measure die niet doet wat je verwacht? Ik denk graag mee, kijk op trainerbjorn.nl.