DAX in één les: van je eerste measure tot de valkuilen

In het artikel over SQL ging het over de taal waarin je database antwoordt. DAX is de taal waarin Power BI rekent. Het verschil is groter dan het lijkt.

Een SQL-vraag geeft één tabel terug. Een DAX-formule geeft een getal, en rekent dat opnieuw uit voor elke cel in je rapport, voor elke filter die iemand aanklikt. Wie dat verschil snapt, snapt de helft van DAX. De andere helft zijn de valkuilen, en die komen hieronder ook aan bod.

De voorbeelden komen uit een model dat ik gebouwd heb: facturen in een tabel fct_factuurlijn, met daarrond een kalender, kanalen, vestigingen en producten. Die opbouw staat in het artikel over het datamodel.

1. Kolom of measure

In Power BI kan je op twee plaatsen rekenen. Een berekende kolom rekent één keer per rij, bij het verversen, en slaat het resultaat op. Een measure rekent pas wanneer iemand kijkt, en altijd voor de selectie die op dat moment geldt.

De vuistregel: alles wat je optelt, deelt of vergelijkt, is een measure. Een kolom gebruik je alleen voor iets waarop je wil filteren of groeperen, zoals een leeftijdscategorie of een prijsklasse.

De klassieke fout is een margepercentage als kolom. Dan staat er per factuurlijn een percentage, en wie dat in een visual zet, krijgt de som of het gemiddelde van percentages. Beide zijn fout. Daarover meer in stap 4.

2. Je eerste measure, en wat filtercontext is

Omzet = SUM ( fct_factuurlijn[omzet] )

Dat is alles. Zet die measure in een tabel met kanalen in de rijen, en je krijgt de omzet per kanaal. Zet ze in een kaart met een filter op één jaar, en je krijgt de omzet van dat jaar. Dezelfde formule, telkens een ander antwoord.

Dat komt door de filtercontext: de verzameling filters die geldt voor die ene cel. De rij in je tabel, de slicer bovenaan, de filter op de pagina. DAX kijkt voor elke cel welke filters er gelden, en telt alleen die rijen op.

Dat is het belangrijkste idee in DAX. Een measure weet niet in welke visual ze staat. Ze ziet alleen filters.

3. CALCULATE: de filter zelf aanpassen

CALCULATE is de functie waar alles om draait. Ze rekent een measure uit met aangepaste filters.

Omzet webshop =
CALCULATE ( [Omzet], dim_kanaal[naam] = "Webshop" )

En om alle filters op kanaal weg te halen:

Omzet alle kanalen =
CALCULATE ( [Omzet], REMOVEFILTERS ( dim_kanaal ) )

Aandeel kanaal =
DIVIDE ( [Omzet], [Omzet alle kanalen] )

Zet Aandeel kanaal in een tabel per kanaal, en elke rij toont haar deel van het totaal. De teller volgt de rij, de noemer negeert ze.

Valkuil: zet Omzet webshop in diezelfde tabel per kanaal, en op de rij Retail staat ook de webshopomzet. CALCULATE vervangt de filter op het kanaal, het voegt er geen toe. Wil je dat de rij meetelt, schrijf dan:

Omzet webshop =
CALCULATE ( [Omzet], KEEPFILTERS ( dim_kanaal[naam] = "Webshop" ) )

Nu is de rij Retail leeg en de rij Webshop gevuld, wat je waarschijnlijk bedoelde.

4. Iterators: rij per rij rekenen

SUM telt een kolom op. SUMX gaat rij per rij door een tabel, rekent per rij een uitdrukking uit, en telt dan op.

Kostprijs verkocht =
SUMX (
    fct_factuurlijn,
    fct_factuurlijn[aantal] * RELATED ( dim_product[kostprijs] )
)

RELATED haalt voor elke factuurlijn de kostprijs uit de producttabel. Dat kan alleen in een iterator, want er moet een rij zijn om vanuit te vertrekken.

Dan het margepercentage, juist:

Marge = SUM ( fct_factuurlijn[marge] )
Marge % = DIVIDE ( [Marge], [Omzet] )

Valkuil: het gemiddelde van percentages. Een factuur van 10 euro aan 50 procent marge en een van 10.000 euro aan 10 procent geven samen geen 30 procent. Ze geven iets meer dan 10. Deel altijd de som door de som, nooit het gemiddelde van de verhoudingen.

5. Tijd: vorig jaar, dit jaar tot nu

Omzet vorig jaar =
CALCULATE ( [Omzet], SAMEPERIODLASTYEAR ( dim_datum[datum] ) )

Omzet YTD =
TOTALYTD ( [Omzet], dim_datum[datum] )

Die functies werken alleen met een goede kalendertabel. Die moet elke dag bevatten, zonder gaten, en gemarkeerd zijn als datumtabel. En de relatie met je facturen moet op de datum liggen, niet op een tekstveld.

Valkuil één: een kalender die verder loopt dan je data. Measures die op de laatste datum ankeren, komen dan leeg terug. Dat staat uitgewerkt in het artikel over lege measures.

Valkuil twee: dit jaar tegenover vorig jaar op dagniveau, terwijl je facturen op vaste dagen klonteren. Dan vergelijk je toevallige momenten. Zie waarom een vergelijking per dag je voorliegt.

6. VAR: leesbaar en sneller

Met VAR bewaar je een tussenresultaat onder een naam. Dat maakt een formule leesbaar, en DAX rekent elke variabele maar één keer uit.

Groei % =
VAR huidig = [Omzet]
VAR vorig = [Omzet vorig jaar]
RETURN
    DIVIDE ( huidig - vorig, vorig )

DIVIDE in plaats van een deelstreep is geen detail. Als vorig nul of leeg is, geeft een deelstreep een fout of oneindig. DIVIDE geeft een lege waarde, en een lege cel is in een rapport altijd beter dan een foutmelding.

7. De valkuilen die je rapport stil doen liegen

Een paar fouten geven geen foutmelding. Ze geven gewoon een verkeerd getal.

Relaties in twee richtingen. Ze lossen een probleem op en maken er twee bij: filters lopen langs wegen die je niet verwacht, en het model wordt trager. Laat filters van de dimensie naar de feitentabel lopen, één richting. Twee richtingen alleen waar het echt niet anders kan, en dan liefst op een één-op-één-relatie.

Nul in plaats van leeg. Wie [Omzet] + 0 schrijft om lege cellen te vullen, krijgt een tabel met elk product in elke maand, ook waar nooit iets verkocht werd. Laat leeg gewoon leeg.

FILTER over een volledige tabel. CALCULATE ( [Omzet], FILTER ( fct_factuurlijn, ... ) ) laat DAX rij per rij door je grootste tabel lopen. Filter waar het kan op een kolom, zoals in stap 3. Het resultaat is hetzelfde, de snelheid niet.

Impliciete measures. Een kolom rechtstreeks in een visual slepen maakt stil een som of een gemiddelde. Dat werkt, tot iemand een andere visual maakt en een ander aggregaat krijgt. Schrijf elke berekening als expliciete measure, dan bestaat ze maar één keer.

Hoe ik measures organiseer

Alle measures staan bij mij in een aparte tabel, zonder data, met mappen: Resultaat, Volume, Controle en Tijd.

Ik bouw van onder naar boven. Eerst de basis, zoals Omzet, Marge en Aantal. Daarna alles wat daarop verder rekent: percentages, vorig jaar, groei. Zo staat de definitie van omzet op één plek, en verandert ze op één plek. Dat is het vastleggen van definities, maar dan in code.

De map Controle bevat measures die nul horen te zijn: lijnen zonder vestiging, facturen zonder datum. En ik leg de omzet in het model geregeld naast dezelfde som in SQL op de database. Klopt dat, dan klopt de laag eronder.

De stelling

DAX is niet moeilijk omdat de functies moeilijk zijn. Het is moeilijk omdat een fout geen foutmelding geeft maar een getal. Wie filtercontext snapt en elke measure één keer schrijft, heeft de meeste van die fouten al vermeden.