In deze serie DAX voor Financials leggen we een aantal veelgebruikte DAX-formules uit. We behandelen daarbij formules die veel gebruikt worden voor financiële rapportages en dashboards. We kijken dus naar DAX vanuit de optiek van een accountant, controller of administrateur.
We werken in dit voorbeeld verder met de dataset uit het vorige blog item.
Datamodel met relaties
De DAX formules worden toegepast op een datamodel met tabellen die aan elkaar gerelateerd zijn.
Tussen de tabellen Grootboek en Mutaties ligt een één-op-veel relatie op het veld Reknr. Elk rekeningnummer komt één keer voor in de tabel Grootboek en kan meerdere keren voorkomen in de tabel Mutaties. Ook tussen de tabellen Klanten en Mutaties ligt een één-op-veel relatie, op het veld Klantnr.
In DAX worden deze relaties gebruikt. Met de functies RELATED en RELATEDTABLE kunnen we de werking van de regelcontext goed uitleggen.
RELATED
Met de functie RELATED kun je de waarde uit een kolom van een gerelateerde tabel ophalen.
RELATED(<column>)
In Power BI kun je met DAX zowel een meting (measure) als een berekende kolom (calculated column) maken. Tot nu toe hebben we alleen metingen gemaakt (dat heeft ook de voorkeur), maar om de werking van de regel context uit te leggen maken we nu een berekende kolom in de tabel Mutaties.
Een berekende kolom heeft per definitie een regel context, de berekening wordt regel voor regel uitgevoerd in de context van de betreffende regel.
Op deze manier kunnen we een extra kolom toevoegen aan de tabel Mutaties die per regel de naam van de klant ophaalt uit de tabel Klanten. De functie RELATED voer je uit aan de veel-kant van een relatie en kan daardoor één waarde ophalen uit de één-kant van de relatie.
Overigens is dit voorbeeld niet erg praktisch, maar bedoeld om de werking van de regel context toe te lichten. Op vergelijkbare manier kun je de RELATED functie gebruiken in metingen (measures) waarin je een regel context hebt. Bijvoorbeeld in de functies SUMX en FILTER.
RELATEDTABLE
Andersom kun je de functie RELATEDTABLE gebruiken.
RELATEDTABLE(<tableName>)
De parameter is een tabel, dus kun je dit gebruiken in andere functies waar als parameter een tabel moet worden opgegeven. Bijvoorbeeld:
In de tabel Grootboek voegen we een berekende kolom toe die een optelling doet van het Aantal * Prijs van alle overeenkomende mutatieregels. Door de regel context van het rekeningnummer op de regel, wordt dit alleen voor de mutatieregels berekend waar het rekeningnummer overeenkomt met die van de tabel Grootboek.
De functie RELATEDTABLE voer je uit aan de één-kant van de relaties en kan daardoor een gefilterde tabel ophalen uit de veel-kant van de relatie.
Gebruik in metingen (measures)
De RELATEDTABLE formule kun je op dezelfde manier ook gebruiken in een measure, want de SUMX functie zorgt dan voor de benodigde regel context. Het voorbeeld met de RELATED functie kun je niet op dezelfde manier in een measure gebruiken, omdat dan de regel context ontbreekt waarin de berekening uitgevoerd moet worden.
Met de functie CALCULATE kun je dit gedrag aanpassen, meer daarover in het volgende item in deze serie.
Meer weten?
Met Power BI maak je in korte tijd fraaie dashboards en rapportages. Vanuit Davista helpen we je daar graag mee op weg. Voor diverse softwarepakketten bieden we connectors aan, zodat je jouw data in Power BI kunt gebruiken. Voor een snelle start kun je aan de slag met een standaard dashboard of met één van de templates. Natuurlijk kunnen we je ook helpen met een dashboard op maat, of we geven je een training zodat je zelf jouw dashboards kunt maken.
Nodig ons gerust uit voor een vrijblijvende kennismaking, zodat we de meerwaarde voor jouw onderneming kunnen laten zien.