COUNTIF() onder de knie krijgen in Power BI: een DAX-gebaseerde aanpak

De functie COUNTIF() is populair bij Excel-gebruikers omdat je er gegevens mee kunt tellen op basis van een voorwaarde. Veel gebruikers merken echter bij de overstap naar Power BI dat Power BI geen eigen COUNTIF()-functie heeft.

Gelukkig kun je de COUNTIF()-functionaliteit reproduceren met diverse DAX (Data Analysis Expressions)-formules. In dit artikel lees je hoe je COUNTIF()-achtige berekeningen in Power BI maakt met DAX-functies zoals CALCULATE(), COUNTROWS(), COUNTA() en FILTER().

Waarom COUNTIF() niet bestaat in Power BI

In tegenstelling tot Excel, dat is gebaseerd op cel-gebaseerde formules, draait Power BI om tabelgebaseerde berekeningen en filtercontext. Het werkt met tabellen en hun relaties en gebruikt DAX voor berekeningen. 

In plaats van individuele cellen te refereren, werkt DAX met kolommen en past het berekeningen dynamisch toe op basis van de filtercontext—die wordt bepaald door slicers, rij-/kolomfilters en tabelrelaties. 

Excel COUNTIF() gebruikt een celgebaseerde aanpak, waarbij formules rij voor rij binnen een opgegeven bereik worden toegepast. De aanpak in Power BI is anders omdat berekeningen worden verfijnd via de relatie tussen tabellen. In plaats van individuele cellen te scannen, past DAX filters toe op basis van de rapportcontext. 

Gevolgen voor gebruikers

Als Power BI-gebruiker die uit Excel komt, moet je je aanpassen aan de tabelgebaseerde rekensystematiek van Power BI. De DAX-aanpakken, die op kolommen in de data werken, zijn efficiënter dan rij-voor-rij-iteraties. 

In tegenstelling tot Excel moeten Power BI-gebruikers de meest geschikte manier kiezen om COUNTIF() te reproduceren die het beste past bij hun datamodel. Je moet dus afleren om formules op individuele cellen toe te passen en in termen van tabellen en filtercontext denken.

Kernfuncties in DAX om COUNTIF()-logica te reproduceren

Je aanpak om COUNTIF() in Power BI te implementeren hangt af van factoren zoals de relatie tussen tabellen, de aanwezigheid of afwezigheid van missende waarden in je data, de omvang van je data, enzovoort. Sommige DAX-functies zijn beter geoptimaliseerd voor snellere en efficiëntere verwerking.  

Overzicht van sleutel­functies

DAX-functies worden gebruikt om berekende kolommen, metingen (measures) en tabellen te maken. Dit zijn enkele DAX-functies die je helpen de COUNTIF()-functionaliteit in Power BI na te bootsen.

• CALCULATE(): Deze functie wijzigt de filtercontext voor een expressie en maakt het mogelijk om nieuwe filters toe te passen of bestaande dynamisch te wijzigen. Je kunt deze functie bijvoorbeeld gebruiken om de totale omzet voor een bepaalde regio te berekenen. 

• COUNTROWS(): Dit telt het aantal rijen in een gegeven tabel, en je kunt het combineren met filterfuncties om specifieke rijen te tellen. Een voorbeeld is het tellen van verkooporders groter dan $1.000.

• COUNTA(): Deze functie telt niet-lege waarden in een kolom en is handig bij ontbrekende data. Je kunt bijvoorbeeld niet-lege klant-ID’s tellen, zodat alleen geldige records worden meegenomen. 

• FILTER(): Wordt meestal gebruikt met CALCULATE() en COUNTROWS() en retourneert een tabel op basis van een voorwaarde. Je kunt bijvoorbeeld een tabel retourneren met verkooporders boven $500.

• ALLEXCEPT(): Dit verwijdert alle toegepaste filters behalve de in de functie opgegeven. Zo kun je bijvoorbeeld een tabel met totale omzet per regio teruggeven, ook al heb je slicers of tabelfilters toegepast. Dit is belangrijk bij aggregaties over hiërarchieën.

• RELATEDTABLE(): Retourneert een tabel op basis van bestaande relaties. Een voorbeeld is het tellen van het aantal verkooporders per klant via de relatie met klantgegevens.

Hoe ze samenwerken

Omdat COUNTROWS() het aantal rijen in een tabel telt, kun je met FILTER() een tabel uit je data teruggeven die aan een voorwaarde voldoet. Gebruik vervolgens CALCULATE() om de filtercontext te wijzigen, zodat COUNTROWS() alleen rijen meetelt die aan de voorwaarde voldoen. Hier is een voorbeeld van DAX-syntax die laat zien hoe dit werkt.

Sales over 5 quantities = 
CALCULATE(
    COUNTROWS(Sales), 
    FILTER('Sales, 'Sales[Quantity] > 5)
)Mogelijk gemaakt door

De functie CALCULATE() werkt min of meer als het combineren van twee verschillende expressies. In plaats van met COUNTROWS() alle data te tellen, beperkt CALCULATE() de telling tot de filtercontext die met FILTER() is afgeleid. 

Basale voorbeelden van COUNTIF()-equivalenten in Power BI

Voordat ik voorbeelden laat zien van hoe je COUNTIF() in Power BI implementeert, moet je eerst de supermarket sales data laden. 

Voorbeeld 1: Aantal orders door vrouwelijke klanten

De onderstaande DAX-syntax berekent het aantal orders van vrouwelijke klanten.

Orders by female customers = 
CALCULATE(
    COUNTROWS(Sales), 
    FILTER(Sales, Sales[Gender] = "Female")
)Mogelijk gemaakt door

Aantal orders door vrouwelijke klanten. Afbeelding door de auteur.

• FILTER(Sales, Sales[Gender] = "Female") filtert verkooporders op vrouwelijke klanten.

• COUNTROWS(Sales) telt, in plaats van alle data, de gefilterde tabel dankzij de invloed van CALCULATE().

Voorbeeld 2: Orders tellen per stad

Hieronder staat DAX-syntax om het aantal orders per stad te tellen.

Total Orders by City = CALCULATE(COUNTA(Sales[City]), ALLEXCEPT(Sales, Sales[City]))Mogelijk gemaakt door

Aantal orders per stad. Afbeelding door de auteur.

• COUNTA(Sales[City]) telt het aantal niet-lege waarden in de kolom City.

• ALLEXCEPT(Sales, Sales[City]) verwijdert alle filters behalve de kolom City uit de tabel Sales.

• CALCULATE() zorgt ervoor dat COUNTA() per stad apart wordt berekend en werkt zo vergelijkbaar met de Excel-functie COUNTIF().

Voorbeeld 3: Alternatieve methode met COUNTROWS

Een alternatief is de onderstaande DAX-syntax, die COUNTROWS() gebruikt in plaats van COUNTA().

Total Orders by City = CALCULATE(COUNTROWS(Sales), ALLEXCEPT(Sales, Sales[City]))Mogelijk gemaakt door

COUNTROWS() telt het aantal rijen in de tabel Sales, ongeacht de aanwezigheid van lege waarden. 

Als de kolom City leeg is, geven beide aanpakken dezelfde resultaten. Als er echter missende waarden in de kolom City staan, sluit COUNTA() die uit, terwijl COUNTROWS() ze meeneemt.

Gebruik de functie COUNTROWS() wanneer elke rij een record voorstelt, zoals een verkooporder. Maar als je data ongeldige invoeren bevat, kun je COUNTA() gebruiken, dat alleen records met een geldige waarde meetelt. 

Measures gebruiken voor dynamische berekeningen

Er zijn situaties waarin je measures moet maken die dynamisch veranderen op basis van een tabelfilter of slicer. In deze sectie bespreken we hoe je COUNTIF() in zulke scenario’s implementeert.

Scenario: Hoogwaardige orders tellen

Hier is een voorbeeld waarin we geïnteresseerd zijn in orders met een totale hoeveelheid groter dan vijf.

High Value Orders = COUNTROWS(FILTER(Sales, Sales[Quantity] > 5))Mogelijk gemaakt door

Aantal hoogwaardige orders. Afbeelding door de auteur.

• FILTER(Sales, Sales[Quantity] > 5) retourneert verkooporders met een orderhoeveelheid groter dan vijf.

• COUNTROWS()  telt de rijen in de gefilterde tabel met orders groter dan vijf.

In tegenstelling tot berekende kolommen, die bij het aanmaken je opslag en modelgrootte vergroten, worden measures alleen berekend wanneer nodig, wat ze efficiënter maakt. De DAX-syntax om een berekende kolom te maken ziet er zo uit:

 High Value = IF(Sales[Quantity] > 5, 1, 0)Mogelijk gemaakt door

Berekende kolom High Value. Afbeelding door de auteur.

Dit slaat in elke rij een statische 1 of 0 op. Om de hoogwaardige orders te tellen, moet je een measure maken met SUM(Sales[High Value]). 

Stel je voor dat je voor elk filter berekende kolommen aanmaakt. Dit verhoogt het opslagverbruik en vermindert de flexibiliteit. 

Hier is een tabel die laat zien waarom je meestal measures boven berekende kolommen zou moeten verkiezen.

Geavanceerde technieken en meerdere voorwaarden

Excel COUNTIFS() ondersteunt meerdere voorwaarden, wat je ook in Power BI kunt nabootsen.

Meerdere criteria / COUNTIFS()-logica

Hier is DAX-syntax om orders te tellen met een orderhoeveelheid groter dan vijf, betaald met creditcard. 

Quantity Above 5 and Credit Card = 
COUNTROWS(
    FILTER(
        Sales, 
        Sales[Quantity] > 5 && Sales[Payment] = "Credit card"
    )
)Mogelijk gemaakt door

Aantal orders met hoeveelheid boven vijf en betaald met creditcard. Afbeelding door de auteur.

De formule hierboven filtert de tabel Sales waar de orderhoeveelheid groter is dan 5 en de betaalmethode een creditcard is. De && staat voor de AND-operator en zorgt ervoor dat alleen orders die aan beide voorwaarden voldoen, worden geteld.

Als je alleen geïnteresseerd bent in orders die aan een van de voorwaarden voldoen, gebruik je de OR-operator, weergegeven door || in DAX.

Quantity Above 5 or Credit Card = 
COUNTROWS(
    FILTER(
        Sales, 
        Sales[Quantity] > 5 || Sales[Payment] = "Credit card"
    )
)Mogelijk gemaakt door

Aantal orders met óf hoeveelheid boven vijf óf betaald met creditcard. Afbeelding door de auteur.

Logische operatoren en variabelen gebruiken

DAX-syntax die COUNTIF() nabootst kan soms complex worden. Met variabelen blijft het overzichtelijk en worden voorwaarden in de gewenste volgorde geëvalueerd. Het voorkomt ook dubbele berekeningen door waarden één keer op te slaan. 

Hier is nog een voorbeeld dat laat zien hoe je het aantal orders van een vrouwelijke klant telt, die óf met creditcard zijn betaald óf boven vijf stuks zijn, met behulp van variabelen.

CreditCard Or Above 5 By Female = 
VAR FilteredSales = 
    FILTER(
        Sales,
        (Sales[Quantity] > 5 || Sales[Payment] = "Credit card") 
        && Sales[Gender] = "Female"
    )
RETURN 
    COUNTROWS(FilteredSales)Mogelijk gemaakt door

Aantal orders door vrouwelijke klanten die óf met creditcard zijn betaald óf boven vijf stuks zijn. Afbeelding door de auteur.

De filter wordt opgeslagen in een variabele die in de functie COUNTROWS() wordt gebruikt. Hier is een andere manier om bovenstaande expressie te schrijven

CreditCard Or Above 5 By Female = 
VAR MinQuantity = 5
VAR FilteredSales = 
    FILTER(
        Sales,
        (Sales[Quantity] > MinQuantity || Sales[Payment] = "Credit card") 
        && Sales[Gender] = "Female"
    )
VAR Result = COUNTROWS(FilteredSales)
RETURN 
    ResultMogelijk gemaakt door

Dit keer zijn zowel de minimumhoeveelheid, de filter als het resultaat als variabelen opgeslagen. Net als het bekende haakjes-voorrangsprincipe in de wiskunde, geeft DAX ook voorrang aan voorwaarden binnen haakjes. 

In het voorbeeld hierboven wordt de OR-operator || eerst geëvalueerd vóór de AND-operator && omdat deze binnen haakjes staat. Dit betekent dat verkooporders met een hoeveelheid boven 5 of betaald met creditcard eerst worden gefilterd, vóór het filteren op vrouwelijke klanten. 

COUNTIF() over gerelateerde tabellen

Als je met meer dan één tabel werkt, kun je COUNTROWS() of COUNTA() gebruiken om het aantal records te tellen op basis van een voorwaarde die in de andere tabel wordt vervuld met behulp van de functie RELATEDTABLE(). 

De functie RELATEDTABLE() houdt rekening met de modelrelatie tussen twee tabellen, die één-op-één of één-op-veel kan zijn.

Stel dat we nog een verkoopstabel hebben, Sales2, die nog steeds dezelfde supermarktdata gebruikt. En Sales en Sales2 zijn gerelateerd via Invoice ID. 

Relatie tussen de tabellen Sales en Sales2. Afbeelding door de auteur.

Je kunt de onderstaande DAX-syntax gebruiken om te zien hoeveel klanten lid zijn, ervan uitgaande dat Sales geen kolom Customer type heeft maar Sales2 wel.

Orders by members = 
COUNTROWS(FILTER(RELATEDTABLE(Sales2), Sales2[Customer Type] = "Member"))Mogelijk gemaakt door

Aantal orders door klanten die lid zijn. Afbeelding door de auteur.

• RELATEDTABLE(Sales2)  filtert Sales2 automatisch om alleen de rijen met dezelfde Invoice ID in de Sales-data op te nemen.

• De functie FILTER() beperkt dit verder tot alleen “Member”-orders, terwijl COUNTROWS() de gefilterde tabel telt. 

Praktische use-cases en integratietips

Hier zijn enkele praktische use-cases en integratietips voor het implementeren van Power BI-COUNTIF().

Scenario’s uit de praktijk

Als je een tabel met toetsresultaten van studenten of orders hebt, kun je COUNTROWS() en FILTER() gebruiken om het aantal dubbele records te tellen. Met behulp van hun scorekolom kun je ook tellen hoeveel studenten geslaagd of gezakt zijn. Een andere belangrijke COUNTIF()-equivalent is SUMX(), waarmee je sommen kunt maken van studentenscores op basis van een voorwaarde. 

Data-integratietools

In plaats van je spreadsheet uit Excel of Google Sheets telkens te uploaden voor analyse, kun je tools zoals Coupler.io gebruiken om data-extractie te automatiseren. 

Zodra je spreadsheet is bijgewerkt, wordt dit automatisch zichtbaar op je Power BI-dashboard, zonder dat je het handmatig hoeft te uploaden. 

Zo weerspiegelt Power BI altijd de nieuwste data zonder handmatig uploaden, waardoor jij je kunt richten op inzichten en zakelijke beslissingen. Je kunt met deze externe tools ook meerdere bronnen aan een dashboard koppelen. 

Tips uit de praktijk voor leesbaarheid en performance

Gebruik berekende kolommen wanneer berekeningen op rijniveau moeten gebeuren vóór aggregaties. Gebruik een measure wanneer de berekening afhangt van door de gebruiker gekozen filters of slicers, of wanneer je een aggregaatwaarde nodig hebt in plaats van rijniveau-berekeningen. 

Inzicht in filtercontext is cruciaal om te weten wanneer je een measure of een berekende kolom gebruikt. Filtercontext is van toepassing wanneer slicers, filters of groepering worden toegepast, in tegenstelling tot rijcontext, die geldt binnen een tabelrij. Berekende kolommen gebruiken rijcontext en kunnen onnodige kolommen creëren, wat het geheugengebruik vergroot. 

Het is ook belangrijk om de filtercontext in overweging te nemen bij het maken van measures. Gebruik bijvoorbeeld in plaats van SUM(Sales[Amount]) CALCULATE(SUM(Sales[Amount])) om filtering te beheersen. Als er al een filter op je data is toegepast, krijg je met de eerste niet de juiste waarde, maar met de tweede worden alle filters die op de data zijn toegepast genegeerd. 

Vermijd ook te veel iterators, zoals FILTER(), in één DAX-syntax, omdat ze grote datasets vertragen doordat ze rij voor rij itereren.

Conclusie

Hoewel de native COUNTIF() uit Excel ontbreekt in Power BI, maken DAX-functies zoals CALCULATE(), COUNTROWS(), COUNTA() en FILTER() het mogelijk om deze functionaliteit na te bootsen.

De aanpak die je kiest hangt af van de aard van je data. Als je missende waarden hebt, overweeg dan COUNTA(); als je data compleet is, kun je COUNTROWS() gebruiken. Om externe filters op de data uit te sluiten, gebruik je ALLEXCEPT() binnen een CALCULATE()-functie.

Onthoud dat het soort DAX dat je schrijft bepaalt hoe snel visuals op je dashboard laden. Schrijf geoptimaliseerde DAX en weet wanneer je measures of berekende kolommen gebruikt. Hier zijn enkele DataCamp-bronnen om andere geavanceerde DAX-technieken te verkennen. 

• Power BI Calculate Tutorial
• Power BI RELATED DAX Function: Introduction and Use Cases
• Mastering SWITCH in DAX for Power BI: A Comprehensive Guide
• How to Use the SUMX Power BI Functions
• DAX SUMMARIZE(): A Guide to Grouping and Summarizing Data
• A Comprehensive Guide to DAX LOOKUPVALUE