Visual Calculations , new kid on the PBI block
Het is eindelijk gedaan met onnodig complexe DAX measures in Power BI voor simpele vraagstukken.
Wie Power BI dashboards en rapportages ontwikkelt, herkent vast de complexe uitdaging van het maken van DAX measures. Sommige ogenschijnlijk simpele berekeningen blijken een hels karwei. In de eerste plaats niet alleen om de regels code die nodig zijn, maar ook het vinden van een oplossing voor de trage visuals. Ik heb het hier niet over het berekenen van een optimum van twee functies. Nee, ik heb het over het verschil uitrekenen tussen een waarde (n) en de vorige waarde (n-1). Of het uitrekenen van een ‘running total’.
Al jaren wordt Microsoft gevraagd om hier een passende oplossing voor te bieden, en eindelijk is daar gehoor aan gegeven. De oplossing luidt naar de naam Visual Calculations. Dit nieuwe stuk gereedschap mag niet ontbreken in de toolkit van de Power BI ontwikkelaar!
Hoe werkt DAX?
Om te begrijpen wat visual calculation zijn, leg ik eerst uit hoe DAX measures door Power BI verwerkt worden. In onderstaande schema geef ik dit visueel weer.
De reis begint wanneer een gebruiker een Power BI dashboard of rapportage opent. Als het goed is, zullen er mooie visuals verschijnen. Op de achtergrond worden de DAX measures die onder deze visuals liggen, door Power BI aangeroepen.
Allereerst gaat de ‘formula engine’ aan de slag (1). Zie dit als het slimme, maar langzame brein van Power BI. De formula engine gaat de gewenste measure omzetten in een query plan. Dit query plan wordt vervolgens aan de ‘storage engine’ doorgegeven (2). In tegenstelling tot de formula engine is deze dom, maar ontzettend snel. De storage engine zorgt ervoor dat er een bak met data klaargezet wordt zodat de formula engine de taak kan afronden. Vervolgens laat de formula engine een DAX query op deze bak met data los (3). Het resultaat is de uitkomst van de query (4) welke doorgegeven wordt aan de Power BI visual.
Hier wordt hopelijk snel duidelijk waarom DAX measures in sommige opzichten complex en traag zijn. De benodigde data moet immers altijd eerst volledig ingeladen worden alvorens deze wordt gefilterd. Pas later in het proces bij stap 3 wordt die data omgezet naar het benodigde eindresultaat voor een visual.
Dit is een visual calculation
Nu je hebt gezien hoe DAX measures door Power BI worden verwerkt, wordt het concept van visual calculations duidelijk. Zoals de naam al doet vermoeden, zijn dit berekeningen welke in de visual gedaan worden. In onderstaande schema kun je zien dat deze stap (5) toegevoegd wordt bovenop het Query resultaat uit stap (4). Dit betekend dat er gaan ballast wordt meegenomen uit eerdere stappen in het proces en dat er voor de berekening ook geen data meer opgehaald hoeft te worden. Dit betekend dat er een aanzienlijke winst behaalt kan worden door bepaalde berekeningen als visual calculations uit te voeren.
Microsoft heeft met de februari 2024 Power BI update een eerste versie van de visual calculations uitgebracht. De functie is in preview en zal daardoor nog flink wat verbeteringen krijgen. Desondanks zitten er al hele interessante functies en parameters bij. Ik probeer deze als volgt in te delen:
-
- Berekenen van verschillen
- Berekenen van verhoudingen
- Berekenen van tijdframes
- As-Parameter
Berekenen van verschillen
Zoals de naam al doet vermoeden, kun je hiermee verschillen uitrekenen tussen bepaalde DAX measures. De meest simpele variant is X-Y. Denk hierbij bijvoorbeeld aan het uitrekenen van de delta tussen een target en de actuals.
X vs Y
-
- Delta = [Sales] – [Target]
X vs vorige/volgende X
Het wordt al iets leuker wanneer we het verschil gaan uitrekenen tussen de huidige regel en de vorige regel. Hiervoor diende tot voor kort een verschrikkelijk complexe DAX measure te worden geschreven.
-
- Sales vs Previous = [Sales] – PREVIOUS([Sales])
- Sales vs Next = [Sales] – NEXT([Sales])
X vs eerste/laatste X
Het is ook interessant om per regel een verschil uit te rekenen met de eerste of de laatste waarde. Het laat zich al raden hoe deze functies eruit zien.
-
- Sales vs First = [Sales] – FIRST([Sales])
- Sales vs Last = [Sales] – LAST([Sales])
Berekenen van verhoudingen
Naast verschillen, is het ook mogelijk om verhoudingen uit te rekenen. Hierbij kan er rekening gehouden worden met subgroepen zoals productcategorie en product subcategorie.
X in verhouding tot het totaal
In dit geval wordt voor elke regel de verhouding uitgerekend tussen de sales, en de totale sales van alle productgroepen bij elkaar.
-
- Sales tov Totaal = DIVIDE([Sales], COLLAPSEALL([Sales], ROWS)) * 100
X in verhouding tot het subtotaal
In dit geval houden we geen rekening met het totaal, maar enkel het subtotaal. Merk op dat de functies bijna identiek zijn, op een woord na: COLLAPSEALL / COLLAPSE.
-
- Sales tov subgroep = DIVIDE([Sales], COLLAPSE([Sales], ROWS)) * 100
In beide gevallen valt ook op dat een parameter met de waarde ROWS gedefinieerd is. Hier is iets bijzonders mee aan de hand wat ik later in dit artikel zal behandelen.
Berekenen van tijdframes
Hopelijk ben je positief verrast over de eenvoud waarmee waardes nu op rijniveau vergeleken kunnen worden. Het wordt nog leuker als we gaan kijken naar berekeningen over tijd.
Moving average van X
We hebben allemaal wel eens de, in mijn ogen doodnormale vraag gehad om een verschil uit te rekenen van een waarde ten opzichte van een moving average. De oplossing die hiervoor in Power BI gebouwd moest worden, had meer weg van ductape en tie-rips. Als ontwikkelaar was je hier soms een dag mee bezig om er vervolgens achter te komen dat de visual langzaam werd door het datavolume. Dit is gelukkig verleden tijd!
-
- Moving Average = MOVINGAVERAGE([Sales], 12)
- Sales vs Moving Average = [Sales] – MOVINGAVERAGE([Sales], 12)
Running Total van X
Een andere mooie functie is de running total. Deze telt voor elke rij de waarde op bij de som van de voorgaande rijen. Deze is onmisbaar indien je een project dashboard maakt waarin de verbruikte uren over tijd worden afgezet tegen het resterende budget. In dit voorbeeld beperk ik mij tot sales data.
-
- Running sum Sales = RUNNINGSUM([Sales])
- Delta = RUNNINGSUM([Sales]) – RUNNINGSUM([Target])
As-Parameter
Eerder schreef ik al dat je bij sommige functies een as-parameter kan toevoegen. Hier is iets bijzonders mee aan de hand wat ik hieronder uitleg. Ik neem als voorbeeld de functie om X te berekenen in verhouding tot het totaal. Deze functie ziet er als volgt uit:
-
- Verhouding tot total = DIVIDE([Field], COLLAPSEALL([Field], Axis))
De as-parameter bepaalt hoe de visual calaculation uitgevoerd wordt. Er zijn vier smaakjes:
-
- Rij
De berekening wordt rij voor rij, van boven naar beneden uitgevoerd.
-
- Kolom
De berekening wordt kolom voor kolom, van links naar rechts uitgevoerd.
-
- Rij-kolom
De berekening wordt rij voor rij, van boven naar beneden uitgevoerd. Wanneer dit voor de eerste kolom klaar is, schuift de berekening een kolom naar rechts op om opnieuw rij voor rij te berekenen.
-
- Kolom-rij
De berekening wordt kolom voor kolom, van links naar rechts uitgevoerd. Wanneer dit voor de eerste rij klaar is, schuift de berekening een rij naar beneden op om opnieuw kolom voor kolom te berekenen.
Visual Calculations zijn je beste vriend wanneer..
Ik hoor je vast denken, waarom zou ik nog DAX measures gebruiken als visual calculations zo simpel zijn? Visual Calculations zijn in ieder geval geen vervanger van DAX measures. In de eerste plaats alleen al om het feit dat je een DAX measure in een visual nodig hebt om visual calculations te maken. Verder is het ook niet altijd zinvol om visuals calculations toe te passen. In sommige gevallen, zoals bij het uitrekenen van het verschil tussen Sales en Target is er geen performance winst te behalen. Dit komt omdat beide measures een simpele som van een kolom zijn. Bijgevolg is het verschil uitrekenen in een aparte DAX measure niet zwaar voor Power BI.
Het is ook goed om te beseffen dat visual calculations alleen leven binnen een specifieke visual. Hierdoor is het niet mogelijk om deze berekening te delen met andere Power BI ontwikkel teams of om de berekening in een andere visual opnieuw te gebruiken.
Maar wanneer gebruik je visual calculations dan wel? Om extra context toe te voegen! Visual calculations kunnen immers net als DAX measures als tooltip ingezet worden. Hieronder kun je zien dat de staafgrafiek met daarin de sales en de target verrijkt is met een delta, een indicator, een running total en een moving average. Handig!
Verder zijn er ook weer nieuwe out-of-the box toepassingen denkbaar zoals in onderstaand voorbeeld waarbij een visual calculation het subtotaal berekent in een matrix visual.
Conclusie
Visual calculations zijn een welkome aanvulling in de toolkit van de Power BI ontwikkelaar. Het is nu mogelijk om op een andere manier context mee te geven aan bepaalde visuals zonder dat hier in het datamodel exotische oplossingen voor nodig zijn. Ik vind het erg gaaf dat er eindelijk een manier is om regels onderling met elkaar te vergelijken. Dit was met DAX measures in de praktijk vaak onhaalbaar als er veel data aanwezig was.
Ben ik al 100% tevreden? Nee, dat nog niet. Er zijn nog wel een aantal zaken die ik graag ontwikkeld zie worden. Bijvoorbeeld de mogelijkheid om een format mee te geven, of nog mooier, dynamische format. Hierdoor kun je onderscheid maken tussen absolute waardes en percentages. Of verschillende munteenheden. Ook zou het een welkome toevoeging zijn als je in visual calculations kunt filteren op waardes uit de gebruikte dimensies.
Wil je de officiële documentatie van Microsoft nalezen, klik dan hier.
Start onze samenwerking hier?
Vond je dit een interessant artikel en kun je wel wat hulp gebruiken bij jouw data en BI vraagstukken? Neem dan gerust contact op voor een (digitale) kop koffie.
