CALCULATE – Das Schweizer Armeemesser in DAX

Aufrufe: 52888

Measures in DAX zu schreiben ist relativ einfach zu erlernen. Aber sicherlich habt ihr auch schon festgestellt, dass DAX nicht immer die gewünschten Resultate zurückgibt und dass mit simplen Measures wie zum Beispiel SUM() oder COUNTROWS() komplexere Berechnungen nicht möglich sind.

Hier kommt CALCULATE() ins Spiel. CALCULATE() funktioniert ein wenig wie die Excel-Funktionen SUMMEWENN() respektive SUMMEWENNS(), ist aber um einiges mächtiger.

Willst du Power BI lernen?
Auf unseren neuen Seite Power BI Coach haben wir einen Onlinekurs zusammengestellt.

In diesem Beitrag werde ich Euch CALCULATE() näherbringen und ihr werdet am Schluss auch die Anspielung auf das Schweizer Armee Sackmesser verstehen – CALCULATE() ist eine universell einsetzbare Funktion.

Aber beginnen wir zuerst mit der Auffrischung von einigen fundamentalen Prinzipien in DAX für Power Pivot und auch für Power BI.

1. Was ist DAX?

DAX steht für

Data Analysis eXpressions und dient in

• Power Pivot für Excel,
• Power BI sowie
• im tabularen Model des SQL Server Analysis Services (SSAS)

als funktionale Berechnungssprache.
Mittels DAX ist es möglich, sowohl einfache Summenberechnungen als auch hochkomplexe Berechnungen wie zum Beispiel Forecasts durchzuführen.
Da DAX einige Funktionen von Excel, wenn auch nicht 1:1, übernommen hat, fällt es vielen Excel-Anwendern leicht, die ersten Schritte in DAX zu machen.

Wo und wann wird DAX angewendet?

In der Entwicklungsphase von Berichten werden die Daten zuerst mittels Power Query importiert und bearbeitet. Danach folgt die Erstellung von DAX Formeln und deren Verwendung in Berichten. Schlussendlich werden die Berichte veröffentlicht, sei es über den Power BI Service, mittels Verteilen der Exceldateien, Ausdruck als PDF und so weiter.
Das nachfolgende Bild soll dies verdeutlichen

2. Wie rechnet DAX?

Ich habe eingangs erwähnt, dass DAX einfach zu erlernen ist. Aber wie ein Student von Marco Russo, seines Zeichens DAX Maestro und MVP, einmal gesagt hat:

“DAX is simple, but not easy”.

Diese Aussage sagt bereits alles!
Es ist einfach, eine simple Summierung mittels SUM() vorzunehmen. Aber es erfordert bereits für diese Funktion ein Verständnis für die Berechnungsweise von DAX. Dieses werden wir uns nun zusammen anschauen.
Die nachstehende Pivot Tabelle wurde mittels Power Pivot erstellt.

Die Resultate sind absolut richtig, aber auf den ersten Blick überraschend.
Vielleicht habt ihr euch noch nie Gedanken darübergemacht, wie DAX für die Berechnung der einzelnen Resultate vorgeht. Aber es ist essentiell, ein Verständnis dafür zu entwickeln.

Es gibt zwei Grundsätze für die Vorgehensweise:

1. Jede Zelle wird individuell berechnet
Betrachten wir uns die Ergebnisse der Spalte “TEST” für männliche Kunden. DAX hat berechnet, dass in Frankreich und der Schweiz der Umsatz je 36.27 beträgt. Aber das Gesamtergebnis ist auch 36.27. Wie das?

Das Gesamtergebnis ist NICHT die Summe der beiden Einzelwerte, sondern das Ergebnis der individuellen Berechnung dieser Zelle unter Berücksichtigung der angewandten Kriterien!

2. Filterkontext

Dies führt uns zur Frage, wie genau diese individuelle Berechnung durchgeführt wurde?
Zuerst werden die Koordinaten der Pivot Tabelle als Filterkriterien angewendet, erst danach wird die Berechnung der Zelle durchgeführt!

Anders gesagt: Zuerst Filterkontext, dann Berechnung

Schauen wir uns den Vorgang Schritt für Schritt an.

3. Schritt für Schritt Berechnung

DAX wendet 4 Schritte an, um zum gewünschten Ergebnis pro Zelle zu gelangen.

1. Filter auf der Pivot Tabelle (initialer Filter Kontext) evaluieren
2. Calculate Filter anwenden (falls vorhanden)
3. Beziehungen anwenden (in Filterrichtung)
4. Arithmetik – Berechnen des Resultats gemäss Formel

Die vier fett markierten Buchstaben ergeben übrigens die Abkürzung

FC BA

den FC Business Analytics……

Schritt 1 – initialer Filterkontext evaluieren

Schauen wir uns den initialen Filter Kontext an. Ich habe hierzu das Beispiel vereinfacht.

Das DAX Measure für das Beispiel lautet:

Total Verkäufe:= SUM('Verkäufe'[SalesAmount])

Wie kommt DAX auf den rot markierten Wert “36.27”?
Menschen interpretieren den Wert als

Dies ist absolut richtig.
In den beiden Datenschnitten oben sind die Werte “27.03.1932” und “31.10.1932” respektive “Zubehör” aktiviert. Die Spaltenüberschrift ist “Männlich”, die Zeilenüberschriften sind “Schweiz” und “2013”.

DAX respektive Power Pivot berechnet den aber Wert nicht so, sondern eruiert zuerst aus den Koordinaten der Pivot Tabelle die entsprechenden Filterkriterien und wendet sie auf die Datentabellen an.
Für DAX ist der Wert “36.27” das Ergebnis aus den folgenden Bedingungen:

Die Koordinaten der Pivot Tabelle wurden also in Filterkriterien umgewandelt. Dies nennen wir den

“initialen Filterkontext”.

Dieser wird nun auf die unterliegenden Dimensionstabellen umgelegt.
Anhand der Tabelle “Kunden” kann dieser Vorgang sichtbar gemacht werden.

DAX scannt die gesamte Tabelle “Kunden” und prüft Zeile für Zeile, ob die Filterkriterien erfüllt werden. Jede Zeile, welche sie nicht erfüllt, wird virtuell gelöscht, bis schlussendlich nur noch die Zeilen übrigbleiben, welche sämtliche Kriterien erfüllen. In unserem Beispiel ist dies lediglich die Zeile 45.

Schritt 2 – CALCULATE() Filter anwenden

Diesen Schritt überspringen wir fürs Erste, da in unserem Beispiel die CALCULATE() Funktion nicht vorkommt.

Schritt 3 – Beziehungen anwenden
(Filter Propagation)

In einem gut modellierten Datenmodell werden die beschreibenden Stammdaten (Dimensionstabellen genannt) von den Transaktionsdaten (Faktentabellen) separiert und dann mittels Beziehungen miteinander verknüpft.
Im 3. Schritt werden die gefilterten Dimensionstabellen, wie zum Beispiel die Tabelle “Kunden” auf die Faktentabellen (“Verkäufe”) angewandt.

Was heisst das?
Die gesamte Faktentabelle mit den 60’398 Datensätzen wird nun ebenfalls gefiltert. Und zwar so, dass nur noch die Datensätze mit denjenigen Kunden (Nr. 26’628) übrigbleiben, welche sämtliche Kriterien des Filterkontexts erfüllen. Das Resultat ist also ebenfalls eine virtuelle Tabelle “Verkäufe”, die nur noch folgende Einträge enthält:

Von den 60’398 Datensätzen sind noch ganze 3 Datensätze übriggeblieben.
Diesen Vorgang nennen wir im englischen “Filter Propagation”, was in etwa mit “Filter Ausbreitung” übersetzt werden kann.

Schritt 4 – Arithmetik
Berechnen des Resultats gemäss Formel

Als letzter Schritt erfolgt die Berechnung gemäss der Formel
SUM(‘Verkäufe'[SalesAmount]).
Das Measure summiert die übrig gebliebenen Einträge der Spalte Verkäufe[SalesAmount].
Das Resultat ist 36.27, welches nun in der Pivot Tabelle in die entsprechende Zelle eingetragen wird.
Wer noch mehr Details über die Berechnungsweise erfahren möchte, dem empfehle ich das Buch
“Power Pivot and Power BI: The Excel User’s Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016”
von Rob Collie. Er hat diesem Thema das gesamte Kapitel 7 mit ausführlichen Beschreibungen gewidmet.
Ich erhalte übrigens keine Vergütungen aus dieser Empfehlung.

4. CALCULATE() – Einführung

Im Einführungsbeispiel haben wir gesehen, dass die Koordinaten auf der Pivot Tabelle in Filterkriterien umgewandelt werden. Dieser Filter Kontext bestimmt schlussendlich den Rückgabewert, welcher in der Pivot Tabelle angezeigt wird.
Jedoch kann mit einfachen Measures der Filter Kontext nur über die Pivot Koordinaten beeinflusst werden, zum Beispiel durch Auswahl eines anderen Eintrags im Datenschnitt oder Veränderung des Pivot Filters oder der Zeilen- und Spaltenüberschriften.
Und hier kommt CALCULATE() ins Spiel. CALCULATE() ist die einzige DAX Funktion, welche den initialen Filter Kontext verändern oder neu setzen kann. Daher wird sie auch als

“bester Freund eines DAX Benutzers”

bezeichnet.
Ihr könnt euch CALCULATE() als SUMMEWENNS()-Funktion vorstellen, die aber viel mehr als das Äquivalent in Excel kann.

FC BA mit CALCULATE()

Gehen wir nochmals zurück zu unserem Akronym, FC BA.
Bisher anhin haben wir das das “C” aussen vorgelassen. Nun ist es an der Zeit, dieses anhand eines Beispiels vorzustellen.

In nachstehender Pivot Tabelle werden alle Verkäufe pro Land dargestellt.

5. Anwendung von CALCULATE()

Der Umsatz aus Fahrradverkäufen soll nun dem Total aller Verkäufe pro Land gegenübergestellt werden. Natürlich können wir über die Auswahlmöglichkeiten im Datenschnitt uns nur die Fahrradverkäufe anzeigen lassen. Aber wir wollen explizit beide Werte (Total Verkäufe und Fahrradverkäufe) nebeneinander sehen und zusätzlich die Verhältniszahl anzeigen lassen.

Mit einer simplen DAX Funktion kann dies jedoch nicht erreicht werden. Wir werden dazu CALCULATE() benötigen.
Hierzu erstellen wir ein neues Measure, welche das Measure
[Total Verkäufe]
als Grundlage verwendet und eine zusätzliche Filterbedingung hinzufügt.

Die Syntax für CALCULATE lautet wie folgt:

CALCULATE (<Definition des Measures>, <Filter1>, <Filter2>, <Filter N+>)

Der erste Parameter ist die Rechenanweisung, die weiteren Parameter definieren die jeweiligen Filterbedingungen.
Die Berechnung erfolgt, indem auch hier zuerst die Filterbedingungen auf die Dimensionstabellen umgelegt werden. Danach wird diese gefilterte Tabelle auf die Faktentabelle angewandt und erst danach erfolgt die Berechnung des Wertes.

In unserem Beispiel gebrauchen wir die folgende Formel für das Measure:

nur Fahrrad Verkäufe:= 
CALCULATE(
          [Total Verkäufe];
 	  Produkte[Kategorie]="Fahrräder"
         )

Was bewirkt die Formel?
Nachdem der initiale Filterkontext über die Koordinaten der Pivot Tabelle festgelegt wurde, wird zusätzlich die Dimensionstabelle “Produkte” aufgrund des Filterparameters in der CALCULATE() Anweisung gefiltert.

Übrig bleiben so nur noch die Produkte der Kategorie “Fahrräder”. Diese virtuelle Tabelle wird dann wieder auf die Faktentabelle umgelegt.
Als nächster Schritt werden die Einträge der Spalte Verkäufe[SalesAmount] summiert. Das Resultat wird an die erste Zelle in der Pivot Tabelle zurückgegeben. Danach wird die nächste Zelle berechnet.
Als Resultat erhalten wir:

Die Verhältniszahl aus den Measures “nur Fahrrad Verkäufe” in % aller Verkäufe “Total Verkäufe” ist wiederum einfach abzubilden:

% Anteil Fahrrad Verkäufe:= 
DIVIDE([nur Fahrrad Verkäufe];[Total Verkäufe];0)

 

    

6. Veränderter Filter Kontext

Der Unterschied zwischen den beiden Measures “Total Verkäufe” und “nur Fahrrad Verkäufe” ist hauptsächlich der Filter im zweiten Measure. Ansonsten sind die beiden Measure gleichwertig, obwohl das erste Measure kein CALCULATE() verwendet.

Warum gleichwertig?
SUM(‘Verkäufe'[SalesAmount]) kann auch als
CALCULATE( SUM( [‘Verkäufe'[SalesAmount]))
geschrieben werden und gibt das gleiche Resultat zurück.

Schauen wir uns an, wie die CALCULATE()-Funktion den Filter Kontext verändert hat.

Links steht das Ergebnis des Measures “Total Verkäufe” in United States, rechts das des Measures “nur Fahrrad Verkäufe” in United States.

Der erste Schritt ist bei beiden Measures gleich. Die Koordinaten der Pivot Tabelle werden in Filterbedingungen, den initialen Filter Kontext, umgewandelt.
Erst der zweite Schritt bewirkt, dass das Measure [nur Fahrrad Verkäufe] einen anderen Wert zurückgibt.

Die Filterbedingung im zweiten Measure verändert den initialen Filter Kontext.
Da sich sowohl die Bedingung Produkte[Kategorie] = “Alle” als auch die Bedingung Produkte[Kategorie] = “Fahrräder] auf die gleiche Spalte beziehen, wird der initiale Filter Kontext überschrieben.

Wenn sich eine Bedingung in einer CALCULATE() Anweisung auf die gleiche Tabelle und Spalte wie der initiale Filter Kontext bezieht, so gewinnt IMMER CALCULATE()!

Die nächsten Schritte bleiben für beide Measures gleich und ihr könnt euch das Ergebnis wie folgt vorstellen:

Der durch CALCULATE() veränderte initiale Filter Kontext wird nun FILTER KONTEXT genannt.

7. Mehrere Filter Bedingungen

Aus der Syntax der CALCULATE() Funktion ist ersichtlich, dass auch mehrere Filter Bedingungen angewendet werden können. Wie viele dies maximal sind, ist nirgends dokumentiert, aber ich habe bis anhin noch nie eine Situation erlebt, in welcher keine zusätzlichen Filter mehr hinzugefügt werden konnten.

Im nächsten Beispiel sollen die Verkäufe all derjenige roten Fahrräder berechnet werden, die mehr 2’000 pro Stück kosten. Die Pivot Tabelle sieht wie folgt aus:

Das Measure lautet:

rote Fahrräder, Betrag > 2000:= 
 CALCULATE( 
 	[Total Verkäufe]; 
 	Produkte[Kategorie] = "Fahrräder"; 
 	Produkte[Farbe] = "Rot"; 
 	Produkte[ListPrice] > 2000
        )

Das Measure filtert also dreimal die Produktetabelle. Die Bedingungen wirken additiv und müssen alle erfüllt sein.

Die DAX-Zeile “Produkte[Kategorie] = “Fahrräder” verändert den initialen Filter Kontext, da im Datenschnitt alle Kategorien angewählt sind. Sowohl der initiale Filter Kontext als auch die DAX Anweisung machen Aussagen zur Spalte Produkte[Kategorie] und wie gesehen behält in diesem Fall die DAX Anweisung die Oberhand.

Die restlichen beiden DAX Zeilen “Produkte[Farbe] = “Rot” und “Produkte[ListPrice] > 2000” fügen neue, zusätzliche Bedingungen hinzu.
CALCULATE() kann also nicht nur den initialen Filter Kontext verändern, sondern auch neue Bedingungen hinzufügen.

8. Oder Bedingungen

Anstelle von additiven Bedingungen können wir auch “ODER” Bedingungen verwenden.
Im nächsten Beispiel soll der gesamte Verkaufsumsatz aller Produkte in Rot oder Silber berechnete werden.

Folgende Formel gibt eine leere Tabelle zurück.

=CALCULATE(
	[Total Verkäufe];
	Produkte[Farbe]="Rot«; 
	Produkte[Farbe] ="Silber")

Eine komplett leere Spalte in einer Pivot Tabelle ist fast immer ein Zeichen dafür, dass etwas schief gelaufen ist.

In DAX ist LEER nicht dasselbe wie 0 (Null).

Der Ansatz, auch hier die Filter Kriterien aneinander zu reihen, wird nicht funktionieren, da ein Produkt nicht zugleich rot und silbrig sein kann. Daher gibt Power Pivot eine leere Produktetabelle zurück, da kein Produkt beide Bedingungen erfüllt. Diese leere Tabelle wird wieder auf die Faktentabelle “Verkäufe” umgelegt, welche natürlich auch leer sein wird.

Das vorstehende Bild zeigt die Situation, nachdem die Tabelle auf rote Produkte gefiltert wurde. Es ist unmöglich, hier auch noch die silbrigen Produkte zu filtern.

Um das korrekte Resultat zu erhalten, benötigen wir eine ODER Funktion. In Power Pivot müssen wir dazu das “Pipe Zeichen” || anwenden.

Die Lösung für das Beispiel lautet:

= CALCULATE(
    [Total Verkäufe];
    Produkte[Farbe]="Rot" ||
    Produkte[Farbe] ="Silber"
         )

Durch Verwendung der ODER Anweisung erhalten wir auch das korrekte Resultat.

…. dranbleiben

Damit sind wir am Ende dieses ersten Teils angelangt. Ich habe euch gezeigt, wie DAX rechnet und wo uns CALCULATE helfen kann. Im nächsten Teil werde ich weitere Funktionen einführen, die im Zusammenspiel mit CALCULATE noch mehr Möglichkeiten zu lassen.

Mehr über Power BI könnt ihr hier nachlesen.

Happy Querying!