DAX: de kracht van data-analyse-expressies

De meest voorkomende expressies in Power BI zijn SUM, MAX, MIN en AVERAGE. Deze worden in de praktijk ook wel basis expressies genoemd. Ze nemen één column uit een datamodel en berekenen hiervan de som, het maximaal, het minimaal of het gemiddelde. Het is belangrijk om te onthouden dat de basis expressie in beginsel geen gebruik maakt van filter context, rijcontext creatie (iteratie) of filter context creatie (CALCULATE).

Filter context is contextuele data waarop kan worden gefilterd. Dit kan een kolom met waarden zijn in een datamodel ( filter context ), of een categorie in een visual ( visual filter context ).  Rijcontext creatie (iteratie) betekent dat Power BI niet langer het totaal of de max van één kolom berekent, maar per rij zijn expressie gaat berekenen. Het zet vervolgens een overkoepelende basis expressie, zoals SUM, over deze uitkomst heen. Door per rij te berekenen (iteratie) in plaats van een berekening los te laten op één kolom, wordt filter context meegenomen. Oók daar waar in eerste instantie geen filter context aanwezig was.

Met filter context creatie wordt bedoeld dat Power BI de mogelijkheid opent om bestaande filter context (als bijvoorbeeld een categorie in een visual) te modificeren en/of filteren.

De X staat voor iteratie expressie. Iteratie betekent dat Power BI niet langer het totaal of de max van één kolom berekent, maar per rij zijn expressie gaat berekenen. Het zet vervolgens een overkoepelende basis expressie, zoals SUM, over deze uitkomst heen. Door per rij te berekenen (iteratie) in plaats van een berekening los te laten op één kolom, wordt filter context meegenomen. Ook daar waar in eerste instantie geen filter context aanwezig was. In het volgende voorbeeld word dit verder duidelijk gemaakt.

Stel onze basis expressie is een MAX. Hierbij wordt in eerste instantie naar één kolom met waarden gekeken en hiervan de MAX berekend. Filter context is aanwezig als visual filter context in de visual zelf door middel van de categorie klant. In ons voorbeeld willen wij de MAX per klant weten, maar het totaal niveau moet een som zijn van het aantal klanten. Door het gebruik van de categorie klant in onze visual, hebben wij visual filter context gecreëerd. Elke MAX staat gelijk aan één klant. Op de totaalregel ontbreekt de filter context. In de totaalregel is de klant immers niet gespecificeerd en hierdoor ziet de totaalregel de MAX van de hele kolom (in ons geval klant D). Om dit goed te ondervangen, kan een iteratie expressie worden gebruikt. In de iteratie expressie SUMX gaat Power BI per rij bekijken wat de waarde is van de aangegeven expressie (In ons geval de MAX). Afsluitend gebruikt het deze gecreëerde context voor de basis expressie SUM.

Met iteratie expressies kan men dus de totaalregel beïnvloeden. Door per rij te kijken in plaats van het totaal wordt filter context meegenomen. Oók daar waar in eerste instantie geen filter context aanwezig was. Hierbij moet wel worden opgemerkt dat dit performance technisch zwaar kan vallen. Bij een iteratie expressie als SUMX zal Power BI per regel gaan kijken wat de waarden zijn en die vervolgens optellen. In grote feitentabellen kan dit erg veel performance kosten. Om dit grotendeels te ondervangen moet een iteratie expressie altijd worden gebruikt in combinatie met de expressie VALUES. De expressie VALUES geeft een enkele column met unieke waarden terug. Hierdoor wordt de iteratie drastisch verkleind en dit zorgt voor een betere performance van de SUMX expressie.

De expressie CALCULATE is misschien wel de meest belangrijke expressie uit de DAX bibliotheek. Met de expressie CALCULATE kan bestaande filter context worden aangepast en nieuwe toegevoegd.

In onderstaande voorbeeld is in eerste instantie gebruik gemaakt van de basis expressie SUM. De expressie SUM pakt één kolom en berekent hierbij het totaal. Filter context is hierbij niet aangegeven, tenzij dit visueel wordt gespecificeerd zoals in onderstaande afbeelding.

Om onderstaande tabel te kunnen modificeren, je wil bijvoorbeeld het totaal voor alle klanten zien, alleen klant A zien, of gebruik maken van een andere relatie dan de bestaande, dan moet filter context worden toegevoegd of aangepast. Dit kan door CALCULATE voor de SUM te plaatsen. Er ontstaat dan, naast de optie ‘expressie’ zoals SUM, ook de optie ‘filter’.

Om alleen klant A te zien, kun je gebruik maken van de expressie FILTER of KEEPFILTERS. Beide hebben als doel data te filteren, maar ze werken op een andere manier.

De expressie FILTER is een tabel filter expressie. Ze retourneert een tabel object op de achtergrond die alleen de rijen bevat die aan de criteria voldoen. Dit houdt in dat bij het gebruik van de expressie FILTER, Power BI op de achtergrond een virtuele tabel aanmaakt. Dit opent de mogelijkheden voor meer complexe modificaties, als bijvoorbeeld verwijzingen naar meerdere measures of kolommen. Let wel op dat de virtuele tabel ook, net als bij de iteratie expressie SUMX, voor een verslechterde performance kan zorgen.

De expressie KEEPFILTERS is een filter aanpassing expressie. Haar doel is bestaande filters in een berekening te behouden. Het zorgt ervoor dat eventuele filters die op de tabel of kolommen worden toegepast, niet worden overschreven door daaropvolgende berekeningen.Het retourneert hierbij geen virtuele tabel op de achtergrond. Dit betekent dat KEEPFILTERS performance technisch beter zal werken, maar minder geschikt is voor complexe modificaties.

Voor filters met één kolom is het gebruik van KEEPFILTERS vaak een betere keuze dan het schrijven van een expliciete filter-functie. KEEPFILTERS zorgt daarnaast voor een verbeterde performance. Voor complexe modificaties is FILTER een betere oplossing.

De ALL expressie heeft het doel elke toegepaste filter te negeren. De expressie ALL kan worden gebruikt als een tabel filter expressie. Hierbij creëert ze op de achtergrond een tabel met data zonder toegepaste filters. Daarnaast kan ALL worden gebruikt als filter aanpassing expressie in de context van de expressie CALCULATE.

De twee rollen van de expressie ALL waren verwarrend voor de Power BI-gebruiker. Daarom heeft Microsoft de expressie REMOVEFILTERS geïntroduceerd.

De REMOVEFILTERS expressie is in essentie hetzelfde als de ALL expressie als filter aanpassing expressie.  REMOVEFILTERS heeft als doel elke toegepaste filter te negeren. Er wordt geen virtuele tabel op de achtergrond gecreëerd, de functie verwijdert simpelweg de filters.

Om de ALL expressie tot een goed einde te brengen, is het belangrijk elke tabel en/of kolom in context te betrekken. Dit betekent dat als een ALL expressie word gebruikt op een kolom waarop sortering is toegepast door middel van een andere kolom, rekening moet worden  gehouden met deze kolom bij het schrijven van de ALL functie.

Gebruik ALL om filters uit een specifieke kolom of tabel te verwijderen. Gebruik REMOVEFILTERS binnen CALCULATE om selectief filters te wissen terwijl andere filters intact blijven.

Een tabel kan maar één (actieve) relatie hebben met een andere tabel. Soms ontstaat een situatie waarbij ook een andere relatie nodig is. Die zal dan op inactief komen te staan. De expressie USERELATIONSHIP heeft het doel een inactieve relatie actief te maken binnen de context van zijn berekening. Dit betekent dat met een measure kan worden bepaald dat de inactieve relatie wordt gebruikt in plaats van de actieve relatie. De inactieve relatie zal dan, in de context van de measure, actief worden gemaakt terwijl de actieve relatie, in de context van de measure, op inactief zal worden gezet.

De expressie TREATAS creëert een virtuele relatie waarbij het filter context doorgeeft van de eerste tabel naar de tweede tabel. Dit werkt ook als een fysieke relatie niet mogelijk is. De expressie TREATAS kan men dus ook wel vergelijken met een onzichtbare many-to-many (both) relatie.

Uit mijn ervaring blijkt dat de expressie USERELATIONSHIP vaakt een verslechterde performance heeft. Mogelijk heeft dit te maken met het calculeren van de relatie tijdens het uitvoeren van de measure. Ik raad dan ook altijd aan om eerst te onderzoeken of er een actieve relatie kan worden aangemaakt en anders TREATAS te gebruiken.

Om tot een goede verstandhouding met DAX te komen, is het van belang om het principe van rijcontext en filter context te begrijpen. Houd er bij het schrijven van DAX altijd rekening mee dat die de performance kan beïnvloeden, omdat bijvoorbeeld een virtuele tabel wordt aangemaakt op de achtergrond.

Ter afsluiting nog een leuk feitje: Wist je dat je met de expressie UNICHAR een hele bibliotheek aan icoontjes hebt om in je expressie te plaatsen? Op het internet staan een heleboel verschillende UNICHAR codes die allemaal gelijk staan aan een icoon. Kies er een uit, zou ik zeggen!