Calculatiegroepen in Power BI Desktop

Elk project heeft zijn eigen uitdagingen en dat maakt ieder eindproduct ontwikkeld in Power BI uniek. Soms lopen we hierin tegen beperkingen aan die zich bij elk project weer voordoen. Een van deze beperkingen in Power BI is dat de ontwikkelaar voor elke basismeting in het rapport, denk aan een berekening die simpelweg de omzet optelt, ook elke variatie die er in het rapport getoond moet worden apart moet schrijven. Deze variaties zijn vaak hetzelfde, denk aan een variatie voor het vorige jaar of een variatie voor year-to-date. Een andere beperking is op het gebied van ontwikkeling van rapportpagina’s, waarbij de ontwikkelaar 3 exact dezelfde pagina’s ontwikkelt. Het enige verschil tussen deze pagina’s is dat de basismeting die wordt getoond anders is. Sinds de Power BI Desktop update van juli 2020 is er een oplossing voor de 2 genoemde beperkingen , namelijk calculatiegroepen. In deze blog word toegelicht wat calculatiegroepen zijn en hoe je kunt starten deze te gebruiken.

Wat zijn calculatiegroepen?

In het kort zijn calculatiegroepen een onderdeel in het Power BI model waarin variaties op basismetingen opgeslagen worden ongeacht wat de basismeting is. Deze variaties binnen een calculatiegroep noemen we calculatie items en kunnen door middel van DAX geschreven worden. Calculatiegroepen zijn onderdeel van Power BI model, net als tabellen, en de calculatie items zijn opgeslagen als waarden in een kolom, zodat ze ook op deze manier door de ontwikkelaar of eindgebruiker van het rapport gebruikt kunnen worden. Bijvoorbeeld door in een lijn-grafiek de calculatie items te plaatsen als categorie of de calculatie items toe te voegen aan een filter voor de hele pagina, om zo alle gebruikte metingen te wijzigen naar de geselecteerde variatie van de meting (er zijn mogelijkheden om metingen uit te sluiten van calculatiegroepen, maar daar gaan we in deze blog niet op in).

Hoe voeg ik een calculatiegroep toe aan mijn model?

Om de eerste calculatiegroep toe te voegen is er een aantal voorbereidende stappen nodig in Power BI Desktop namelijk:

1. Zorg dat ten minste de versie van juli 2020 geïnstalleerd is op je pc, check je huidige versie via Bestand -> Info.
2. Zorg dat het programma Tabular Editor is geïnstalleerd, dit is een externe tool waarin de calculatiegroepen ontwikkeld worden, Power BI zal deze automatisch herkennen nadat Power BI Desktop opnieuw start. Let er bij deze tool op dat dit het Power BI model verandert en dat je een backup maakt voordat je calculatiegroepen op bestaande rapporten toepast.
3. Zorg dat je de volgende preview-functie inschakelt om via Tabular Editor je model aan te passen: Gegevenssets opslaan in de indeling voor geavanceerde metagegevens. Dit kun je doen via Bestand -> Opties en Instellingen -> Opties -> Preview-functies.

Nadat de bovenstaande voorbereidingen getroffen zijn kan de eerste calculatiegroep in het model toegevoegd worden. De calculatiegroep die we gaan toevoegen is degene die in nagenoeg ieder rapport terugkomt en dat zijn variaties van tijd. Ik ga de calculatiegroep toevoegen aan het volgende model waarin vier simpele tabellen staan:

In de ‘Sales’-tabel staan 3 basismetingen en hun DAX-code die ik graag in verschillende tijd-variaties wil zien en ga gebruiken als basis voor mijn rapport:

• Omzet = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
• Aantal klanten = DISTINCTCOUNT( Sales[CustomerKey] )
• Aantal verkopen = COUNTROWS ( Sales )

In het lint boven in Power BI desktop is na de update van juli 2020 het kopje ‘Externe hulpprogramma’s’ te vinden, waaronder de zojuist geïnstalleerde Tabular Editor te vinden is.

Door Tabular Editor te openen kunnen we ons Power BI model op een platgeslagen manier zien in een mappen-structuur. In de ‘Tables’-map gaan we de calculatiegroep toevoegen door met de rechter muisknop op de ‘Tables’-map te klikken, vervolgens Create new -> Create new calculation group. Hiermee hebben we een lege calculatiegroep toegevoegd, deze geven we de naam ‘Tijd-variaties’.

De calculatiegroep komt voor in de lijst met tabellen en als we Tijd variaties openklappen zien we een map met calculatie items en een kolom met de standaard-naam ‘Name’. Deze kolom word gebruikt in het rapport om de calculatie items in visualisatie of filters toe te kunnen passen. We wijzigen de naam van deze kolom in ‘Tijd’ rechtsonder in het scherm en we hoeven hier verder niets meer mee te doen in Tabluar Editor.

De basis voor de calculatie items is dat deze dynamisch om kunnen gaan met verschillende basismetingen, deze dynamiek wordt in DAX gecreëerd door op de plek waar je eerder [Meting] zou schrijven in de DAX-code voor de variatie op de basismeting schrijven we nu de vervangende code: SELECTEDMEASURE() . Deze code is meteen de code die we gebruiken voor onze eerste calculatie item, namelijk de meting voor het huidige jaar. Om een calculatie item aan te maken in Tabular Editor klikken we met de rechtermuisknop op Calculation items en vervolgens op New calculation. Vervolgens geven we het calculatie item de naam ‘Huidig jaar’.

Vervolgens schrijven we in de expression editor (het witte veld rechtsboven) de code voor het calculation item voor het huidige jaar die in de afbeelding is weergegeven. Klik vervolgens op de het vinkje met ‘Accept changes’ om het item op te slaan. Je eerste calculatie item is een feit!

De bovenstaande stappen voor het aanmaken van calculatie item herhalen we voor volgende 3 calculatie items, de naam gevolgd door de DAX Code die je in het veld plakt die begint na de dubbele punt:

• Vorig Jaar: CALCULATE ( SELECTEDMEASURE(), SAMEPERIODLASTYEAR ( ‘Date'[Date] ) )
• Year-To-Date: CALCULATE ( SELECTEDMEASURE(), DATESYTD ( ‘Date'[Date] ) )
• Verschil Huidig Jaar en Vorig Jaar: VAR HuidigJaar = SELECTEDMEASURE ()
  VAR VorigJaar = CALCULATE (SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( ‘Date'[Date] ))
  VAR Resultaat = HuidigJaar- VorigJaar
  RETURN Resultaat

Nadat je deze calculatie items hebt aangemaakt kun je deze opslaan en laten verwerken in het model, door linksboven op File -> Save te klikken. De Tabular Editor zal de wijzigingen doorvoeren in het Power BI model en Tabular Editor kan gesloten worden.

Gebruik van de calculatiegroep in Power BI Desktop

Terug in Power BI Desktop, moet het model nog handmatig vernieuwd worden om vervolgens de calculatie groep te kunnen gebruiken. Dit kunnen we doen door op de ‘Nu vernieuwen’-knop in de gele balk te klikken.

Na de verwerking zien we dat calculatiegroep is toegevoegd als tabel in ons model en dat deze beschikbaar is om in ons rapport te gebruiken. We starten met het maken van ons rapport door een simpele Matrix toe te voegen waarin we de omzet, de basismeting, voor de maanden van het jaar 2008 tonen.

Vervolgens passen we de calculatiegroep toe op deze visualisatie door de kolom ‘Tijd’, vanuit de ‘Tijd variaties’-tabel toe te voegen op de kolommen van de matrix. Hiermee worden alle calculatie items die we zojuist in de Tabular Editor ontwikkeld hebben toegepast in deze visualisatie. Door simpelweg de basismeting te veranderen in de visualisatie, worden alle variaties van tijd voor die meting toegepast. (zie hieronder in het filter voorbeeld)

Doordat de calculatiegroep als kolom wordt aangemerkt kunnen wij hier ook op filteren. Bijvoorbeeld het wegfilteren van het Year-To-Date calculatie item omdat deze niet gewenst is in het rapport. We klikken op de kolom ‘Tijd’ in het filtervenster en filteren deze uit de visualisatie.

Conclusie

Tot zo ver deze introductie van calculatiegroepen binnen Power BI Desktop waarmee we met de kracht van DAX en de mogelijkheid om het model aan te passen via Tabular Editor veel tijd besparen om variaties op metingen aan te maken. De calculatiegroepen zijn gemakkelijk te gebruiken doordat ze als reguliere tabellen en kolommen terugkomen in het rapport. Een vervolgstap voor Power BI Desktop zou kunnen zijn dat de calculatiegroep direct in Power BI Desktop word aangemaakt. Helaas zijn we nog niet zo ver, maar met oog op de toekomst is de introductie van calculatiegroepen in Power BI Desktop een goed teken.