Power BI: Tips voor het berekenen van totalen icm row level security

Als data scientist transformeer ik dagelijks data in waardevolle informatie en visuele inzichten voor onze opdrachtgevers. Hierbij maak ik onder andere gebruik van Power BI: het business intelligence platform van Microsoft. In deze blog deel ik handige tips voor het berekenen van totalen in Power BI als data op rolniveau is afgeschermd met Row Level Security.

Gegevens vergelijken in Power BI

Regelmatig wil ik in Power BI rapporten de cijfers van een persoon of afdeling vergelijken met de totale cijfers van een organisatie. Neem bijvoorbeeld de verkoopresultaten van één accountmanager versus de gemiddelde verkoopresultaten over alle accountmanagers. Of uitvaltijd vergeleken met gemiddelde uitvaltijd van machines. Dit is niet zo’n heel moeilijk vraagstuk.

Power BI

Row Level Security en vergelijken van Power BI data

Het wordt al wat lastiger als ik Row Level Security wil gebruiken om te zorgen dat één accountmanager niet de cijfers van alle accountmanagers kan zien. Of dat één afdeling alleen de cijfers van de medewerkers op die afdeling kan zien. Door gebruik te maken van Row Level Security is de data van de totalen niet meer beschikbaar.

Dit is op te lossen door de data een tweede keer te laden. In die tweede lading kan de persoons- of afdelingsdata verwijderd worden en op die manier kunnen de totalen berekend worden zonder dat de gebruiker gegevens kunnen inzien die niet voor hem/haar bedoeld zijn.

Tip voor filtering met behulp van Power BI slicers

Dit levert echter meteen de volgende uitdaging op: Filtering met behulp van slicers kan standaard niet worden toegepast op meerdere databronnen. Ik kan nu dus wel de cijfers van één persoon of afdeling vergelijken met het totaal, maar wanneer ik wil gaan filteren kan ik nog steeds alleen vergelijken met het ongefilterde totaal.

Om dit op te lossen kan gebruik worden gemaakt van hulptabellen. Voor iedere dimensie die gefilterd moet worden, wordt één hulptabel ingericht met de waarden uit de originele tabellen. Door deze vervolgens te koppelen aan beide datasets kan een slicer op een rapport over zowel de persoons- of afdelingsdata als over de totale data filteren.

Stel we hebben de volgende feiten-tabel:

De tweede lading zou er dan als volgt uitzien:

Viva Learning

De eerste tabel kan worden gebruikt voor Row Level Security. [Name]=username() zal zorgen dat een accountmanager alleen zijn eigen data te zien krijgt.

Nu zijn slicers nodig voor de dimensies. Een hulptabel voor dimensie “Color” zou er als volgt uit kunnen zien:

Iets soortgelijks kunnen we doen voor hiërarchische dimensies zoals Merk/Type. Door de kolommen bij het laden van de dataset te combineren:

Deze formules zorgen ervoor dat alle dimensies in de naamloze tabel in de slicer worden getoond. Hierdoor kunnen ook de dimensies, die geen data van de desbetreffende persoon bevatten, worden getoond. Alternatief is het kiezen voor een formule gebaseerd op ‘Data Dimensions’, zodat alleen de dimensies getoond worden, die data van de desbetreffende persoon bevatten.

Overigens kunnen we voor de Datum slicer iets soortgelijks doen. Door gebruik te maken van de Calendar functie vult de datum tabel zichzelf met alleen relevante data.

Voorbeeld datamodel

Het volledige datamodel komt er dan als volgt uit te zien:

Eindresultaat

Het eindresultaat is dat de ontwikkelaar een rapport kan bouwen, waarmee de gebruiker zijn verkopen af kan zetten tegen de gemiddelde verkopen per accountmanager of tegen de totale verkopen, zelfs als  de gebruiker wil filteren op verschillende dimensies. Ik kan, bijvoorbeeld, zien hoeveel groene auto’s ik verkocht heb van het totaal aan verkochte groene auto’s. Of hoeveel Reano Kleeyo’s ik verkocht heb ten opzicht van het gemiddelde.

Twee belangrijke opmerkingen

Als je in jouw Power BI omgeving gaat werken met deze tips wil ik je nog graag wijzen op het volgende:

  1. Kijk kritisch naar je dimensies. In dit voorbeeld hebben we, bijvoorbeeld, Merk en Type samengevoegd. Ook gegevens die onderdeel zijn van een dimensie hebben geen aparte hulptabel nodig.
  2. Datasets met veel dimensies kunnen onoverzichtelijk worden. Ben kritisch op welke dimensies er gefilters moet kunnen worden.

Wil je hiermee aan de slag? Mijn broncode is beschikbaar op mijn Azure Devops repo.

Direct meer weten?

Wil je meer weten over het bereken van totalen en/of het vergelijken van data in Power BI in combinatie met Row Level Security? Of heb je ondersteuning nodig bij het vertalen van data in waardevolle (management)informatie?  Laat het me weten. Ik denk graag met je mee.

Wat doen we nog meer?

Modern datacenter

Modern datacenter

Een modern datacenter in Microsoft Azure biedt je maximale flexibiliteit, schaalbaarheid en veiligheid.

Oplossingen

onze oplossingen

Maak kennis met de innovatiekracht en toegevoegde waarde van de Microsoft cloud voor jouw organisatie.

Moderne werkplek

Online vergaderen met Microsoft Teams

De moderne werkplek faciliteert prettig en veilig (samen)werken vanuit huis, op kantoor en onderweg.