Snelle berekeningen in Power Pivot met DAX
Uitvoer Berekeningen Voorbereiden Invoer
Twee soorten DAX-berekeningen
Berekende kolom
Berekend veld
Voegt een kolom toe aan een tabel
Definieert een aggregatie-functie
Wordt berekend (gevalideerd) bij het maken of verversen van data
Wordt berekend (gevalideerd) bij gebruik
Resultaten worden opgeslagen in het model, en nemen dus ruimte in
Resultaten worden alleen opgeslagen in draaitabellen of grafieken
Altijd gebonden aan een tabel
Niet gebonden aan een tabel (maar wel geplaatst bij een tabel)
Voorbeeld:
Voorbeeld:
=[Prijs]*[Aantal]
Omzet:=SUM(Verkoop[Bedrag])
Context
Selectiecontext (ook wel: querycontext)
Rijcontext
Datum
Product
Prijs
Aantal
Bedrag
13-10-2013
Pen
1,25
50
62,50
25-11-2013
Potlood
0,95
150
142,50
5-12-2013
Potlood
0,90
200
180,00
8-12-2013
Schrijfblok
2,45
80
196,00
12-12-2013
Pen
1,30
50
65,00
In een rij kent Power Pivot de waarde van elke kolom, maar geen waarden in andere rijen
DAX Syntax Berekende kolom: Begint met = Verwijst naar kolommen in de tabel met [Kolomnaam]
Berekend veld: Begint met NaamVeld:= Verwijst naar kolommen in een tabel met Tabelnaam[Kolomnaam]
(best practice)
Operatoren: + - * / & Functies: FUNCTIE(argument1;argument2;...)
Functies kunnen genest worden Houd je formules kort!
DAX-functies zijn Engelstalig: niet SOM maar SUM
Basisfuncties Aggregatiefuncties
SUM, AVERAGE, MIN, MAX, COUNT Logische functies
IF, AND, OR, NOT Bijvoorbeeld: gemiddelde prijs
GemiddeldePrijs:= SUM(Verkoop[Bedrag]) / SUM(Verkoop[Aantal]) of in Excel 2013: GemiddeldePrijs:= DIVIDE(SUM(Verkoop[Bedrag]);SUM(Verkoop[Aantal];0)
Filters Filters passen de selectiecontext aan: filtercontext
DAX →
Filters toepassen met CALCULATE
Syntax: Resultaat:=CALCULATE([Expressie];
;;…)
Bijvoorbeeld Omzet303:=CALCULATE([Omzet];Product[Nr]=303)
Korte schrijfwijze (en vaak efficienter): Omzet304:=[Omzet](Product[Nr]=304)
CALCULATE laat niet elk filter toe, voor complexere filters gebruik je de tabelfunctie FILTER.
ALL
ALL is een filterfunctie, bijvoorbeeld TotaalOmzet:=CALCULATE([Omzet];ALL(Product))
Deze berekening geeft [Omzet] voor alle producten in de tabel Product
ALL(Tabel): heft alle (selectie)filters op Tabel op ALL(Tabel[Kolom]): heft alle filters op Tabel[Kolom] op, maar niet op andere kolommen in dezelfde tabel ALLEXCEPT(Tabel[Kolom]): heft alle filters op Tabel op, behalve die op Tabel[Kolom]
Intelligente tijdfuncties
Engels: time intelligence Werkt op datums, niet op tijdstippen... Datumtabel (bijvoorbeeld Datum) Een rij per dag (bijv. Datum[Datum]) Filteren op kolommen in de datumtabel Markeer tabel als datumtabel in je model Elke intelligente tijdfunctie verwijst naar Datum[Datum]
Mijn favoriete DAX-functie: TOTALYTD
Year‐to‐date berekeningen maak je met TOTALYTD Syntax: TOTALYTD(<expressie>;Datum[Datum])
Bijvoorbeeld OmzetYTD:=TOTALYTD([Omzet];Datum[Datum]
TOTALYTD gebruik je niet alleen om totalen (SUM) uit te rekenen! Bijvoorbeeld TOTALYTD([HoogstePrijs];Datum[Datum])
Berekent de hoogste prijs vanaf het begin van het jaar tot nu toe
In feite is TOTALYTD, en elke intelligente tijdfunctie een filterfunctie die filtert op de datumtabel
Voorbeelden van intelligente tijdfuncties
TOTALYTD, TOTALQTD, TOTALMTD SAMEPERIODLASTYEAR STARTOFYEAR, ENDOFYEAR FIRSTDATE, LASTDATE DATEADD, PARALLELPERIOD DATESINPERIOD, DATESBETWEEN
Tabelfuncties
Functies die een tabel gebruiken als argument SUMX, AVERAGEX, MINX, MAXX, COUNTX COUNTROWS Bijvoorbeeld: AvgKlant:=AVERAGEX(Klanten;[Omzet])
Hier is Klanten een tabel met klanten, en [Omzet] een berekend veld.
Tabelfuncties (2)
Functies die een tabel maken: berekende tabel Een berekend veld mag nooit een tabel opleveren als resultaat! Een berekende tabel gebruik je als argument in een andere tabelfunctie Voorbeelden: ALL, ALLEXCEPT TOPN VALUES, DISTINCT AvgKlant:=AVERAGEX(DISTINCT(Verkopen[KlantNr]);[Omzet])
FILTER
De functie FILTER is een tabelfunctie: met een tabel als argument die een berekende tabel als resultaat heeft
De DAX‐functie hieronder geeft het aantal klanten met een hogere omzet dan de gemiddelde omzet van de top 25: AantalTopKlanten:=COUNTROWS( FILTER(Klanten;[Omzet]> AVERAGEX(TOPN(25;Klanten;[Omzet]);[Omzet]) ))
