Hits: 532

Im ersten Teil habe ich CALCULATE() eingeführt. In Teil 2 werde ich nun die Grenzen von CALCULATE() aufzeigen und Euch zeigen, wie diese mit FILTER(), ALL(), ALLEXCEPT() sowie ALLSELECTED() überwunden werden können.

Wenn Du mehr über Power BI lesen willst, so abonniere unseren Newsletter.


CALCULATE() mit FILTER ()

Auch CALCULATE() hat Grenzen. Zum Beispiel ist es nicht möglich, in einer normalen CALCULATE() Anweisung den Wert eines Measures als Filterbedingung zu verwenden.

Als Beispiel verwenden wir folgende Aufgabenstellung:
Es soll der Umsatz berechnet werden, der aus Verkäufen mit einem Gesamttotal > 3’500 resultiert.

Ein naiver Ansatz könnte sein, dass wir die Bedingung direkt ins Measure einbauen:

Gesamtverkäufe > 3500 falsch :=
CALCULATE(
[Total Verkäufe];
[Total Verkäufe]> 3500)

DAX quittiert diesen Versuch unmittelbar mit nachstehender, sehr aussagekräftigen Fehlermeldung.

DAX Fehlermeldung

DAX Fehlermeldung


Ich habe die Fehlermeldung lange nicht durchschaut. Das war so ein Punkt, an welchem ich die Aussage, “DAX is simple, but not easy” so richtig verstanden habe…..
Die Ursache für die Fehlermeldung ist die folgende:

  • CALCULATE () erwartet Filterbedingungen in der Form von
    – Tabelle[Spalte] = Bedingung     (z.B. Produkte[Farbe]=”Rot”)
    oder
    – Tabelle[Spalte] <= Bedingung     (z.B. Produkte[Preis]<= 999)
    oder
    – ähnliches.
  • Links vom Vergleichsoperator (=, <, >, <=, >=, <>) muss also eine Spalte aus einer Tabelle aufgeführt sein. Wir haben jedoch ein Measure verwendet.
  • Auch rechts vom Vergleichsoperator darf kein Measure verwendet werden.

Wir könnten nun versuchen, der Tabelle eine berechnete Spalte mit Umsätzen pro Einkauf, welche alle Einzelprodukte des betreffenden Einkaufs zusammenzählt, hinzuzufügen.

Bitte tut das nicht!

Dieses Vorgehen hat folgende Nachteile:

  • Es resultiert ein aufgeblähtes Modell mit unnötigen Spalten
  • Was wiederum zu höherer RAM Beanspruchung führt, da die sämtliche Spalten
    in-memory geladen werden.
  • Dadurch kann die Performance der Lösung leiden!

Nutzt berechnete Spalten nur, wenn ihr wisst, warum ihr es tun müsst!

Ich habe hierzu vor längerer Zeit mal einen Artikel geschrieben, welche das Thema etwas detaillierter ausleuchtet.

FILTER()

Aber zum Glück gibt es auch hier eine Lösung: Die DAX Funktion FILTER().

Die Syntax lautet FILTER(<table>, <filter>)

FILTER() gibt als Resultat eine neue Tabelle mit gefilterten Zeilen zurück, welche danach durch CALCULATE() weiterverwendet werden kann.

Das Filterargument erlaubt auch, im Gegensatz zu CALCULATE(), den Einsatz eines booleschen Ausdrucks (WAHR / FALSCH), der für jede Zeile der Tabelle einzeln ausgewertet werden kann.

Folgende Vergleichsoptionen sind mit FILTER() zulässig:

  • Tabelle[Spalte] =     [Measure]
  • Tabelle[Spalte] =     Tabelle[Spalte]
  • [Measure] =     [Measure2]
  • UND:     <WAHR/FALSCH Ausdruck1> && <WAHR/FALSCH Ausdruck2>
  • ODER:     <WAHR/FALSCH Ausdruck1> || <WAHR/FALSCH Ausdruck2>

Als Beispiel sind in FILTER() die Ausdrücke

  • [MEASURE] > 0 oder
  • [Region] = “Frankreich”

also valabel.

Lösung:

Für unsere Aufgabe müssen wir das Measure wie folgt schreiben, um das richtige Resultat zu erhalten:

= CALCULATE(
[Total Verkäufe];
Filter('Verkäufe';
[Total Verkäufe]>3500
)
)
korrektes Measure mit Gesamtverkäufen grösser 3'500
korrektes Measure mit Gesamtverkäufen grösser 3’500

 

Aufheben von Filtern – ALL()

Falls ihr euch gefragt habt, ob nebst der Veränderung bestehender und dem Hinzufügen neuer Filter auch die Aufhebung von Filtern möglich ist, so kann ich euch beruhigen – auch das ist möglich.

Hierzu führen wir eine neue Funktion ein: ALL()

Die Funktion gibt alle Zeilen einer Tabelle oder alle Werte in einer Spalte zurück und ignoriert dabei alle allenfalls angewandten Bedingungen aus dem initialen Filter Kontext.
Diese Funktion ist daher nützlich, um Filter zu löschen und Berechnungen für alle Zeilen einer Tabelle durchzuführen.

Die Syntax lautet: ALL( {<table> | <column>[, <column>[, <column>[,…]]]} )

Aufheben des Filters auf Stufe Spalte

Machen wir wiederum ein Beispiel. Wir bilden ein neues Measure, welches immer den gesamten Umsatz zurückgibt, unabhängig davon, welche Farbe im Datenschnitt ausgewählt wurden.

Die Formel lautet:

Verkauf aller Farben:=
CALCULATE(
[Total Verkäufe];
ALL(
Produkte[Farbe]
)
)

Sehen wir uns das Ergebnis an.

Ergebnis mit allen Farben

Ergebnis mit allen Farben

Im Datenschnitt sind alle Farbe aktiviert und das Gesamtergebnis stimmt für beide Measures überein. Aber im rechten Measure ist für jede Farbe immer der gleiche Wert aufgeführt, auch für die Spalte “Grau”, obwohl keine Produkte in dieser Farbe verkauft wurden, wie aus dem leeren Wert des Measures “Total Verkäufe” ersichtlich ist.

Der Grund ist wiederum im Filter Kontext zu suchen. Der initiale Filter Kontext für die Zeile “Grau” ist Produkte[Farbe] = “Alle” aus dem Datenschnitt sowie Produkte[Farbe] = “Grau” aus der Zeilenüberschrift. Die zweite Bedingung schränkt die erste ein, so dass der initiale Filter Kontext Produkte[Farbe] = “Grau” ist.

Nun kommt CALCULATE() ins Spiel. Die ALL() Funktion hebt den initialen Filter Kontext auf der Spalte Produkte[Farbe] komplett auf und gibt alle Datensätze aus der Tabelle Produkte zurück. Daher sind in allen Zeilen des Measures “Verkauf aller Farben” die gleichen Werten aufgeführt.

Das Gesamtergebnis hat hingegen als initialen Filter Kontext lediglich Produkte[Farbe] = “Alle”, was auch der ALL(Produkte[Farbe]) Funktion entspricht.

Gib mir bitte alle Zeilen dieser Tabelle oder Spalte zurück

ALL() kann auch mit
“Gib mir bitte alle Zeilen dieser Tabelle oder Spalte zurück”
übersetzt werden.

Was geschieht nun, wenn im Datenschnitt eine oder mehrere Farben aktiviert werden?

Erwartungsgemäss verändert sich die Pivot Tabelle an sich, indem nur noch die Zeilenüberschriften für die selektierten Farben angezeigt werden. Auch das Ergebnis des Measures “Total Verkäufe” verändert sich.

Hingegen bleiben die Werte des Measures “Verkauf aller Farben” unverändert.

Ergebnis mit selektierten Slicerwerten

Ergebnis mit selektierten Slicerwerten

Dieses Verhalten kann sehr praktisch sein, wenn zum Beispiel der Umsatzanteil pro Farbe berechnet werden soll, obwohl im Datenschnitt eine Selektion getroffen wurde.

Das Measure für den Umsatzanteil lautet:

%aller Verkäufe =DIVIDE([Total Verkäufe]; [Verkauf aller Farben] ;0)
Prozentanteil der selektierten Farben

Prozentanteil der selektierten Farben



Aufheben des Filters auf Stufe Tabelle

Was geschieht aber, wenn der Datenschnitt “Farbe” nicht mehr verwendet wird und ein anderer Datenschnitt ins Spiel gebracht wird?

Schaut euch dazu die folgende Pivot Tabelle an. Die Measures sind unverändert, jedoch wurde der Datenschnitt “Farbe” durch den Datenschnitt “Kategorie” ersetzt.

Prozentanteil der selektierten Farben mit anderem Datenschnitt

Prozentanteil der selektierten Farben mit anderem Datenschnitt

Nun werden nicht mehr die gesamten Verkäufe angezeigt, da der neue Datenschnitt die Spalte Produkte[Kategorie] filtert und die Zeilenüberschrift die Spalte Produkte[Farbe].

ALL(Produkte[Farbe]) hebt hingegen ausschliesslich den initialen Filter Kontext für die erwähnte Spalte auf, aber nicht für die Spalte Produkte[Kategorie]. Als Ergebnis filtert DAX die Tabelle “Produkte” so, dass alle anderen Produkte wie Fahrräder usw. gar nicht mehr vorhanden sind.

Daher ist das Gesamtergebnis nun auch nicht mehr 29’358’677.20, sondern 339’772.61.

Wie können wir das lösen?
Indem wir DAX mitteilen, dass wir sämtliche Zeilen der Tabelle “Produkte” zurückerhalten wollen!

Wir erstellen zur besseren Visualisierung ein fast identisches Measure, passen aber die ALL() Funktion an.

Verkauf aller Zeilen, aller Farben = CALCULATE([Total Verkäufe]; ALL(Produkte))

Ergebnis mit ALL([Produkte])

Ergebnis mit ALL([Produkte])

Durch diese Anpassung liefert DAX immer alle Verkäufe zurück, unabhängig davon, wie die Tabelle “Produkte” gefiltert wurde. Um das Measure ganz zu immunisieren, müssten natürlich auch die anderen Dimensionstabellen mit ALL(Tabelle) im Measure aufgeführt werden, was aber für dieses Measure nicht notwendig ist.

ALLEXCEPT()

Die Funktion ALL() hebt wie gesehen den Filter auf der aufgeführten Spalte oder Tabelle auf. Manchmal ist es jedoch notwendig, dass ALL() auf alle bis auf eine Spalte wirken soll.

Wir können sicherlich ein Measure schreiben, welches sämtliche Spalten der Tabelle Produkte, ausser z.B. der Spalte Produkte[Farbe], berücksichtigt.

Ein solches Vorgehen würde aber eine Anweisung über unzählige Zeilen ergeben und jede Zeile würde die Syntax ALL(Produkte[SPALTENNAMEN]) enthalten. Ziemlich aufwendig, wenn wir eigentlich nur eine einzige Spalte von der ALL() Anweisung ausschliessen wollen.

Dem Schreiben dieses Bandwurms können wir mit ALLEXCEPT() entgehen. Die Funktion entfernt alle Filterbedingungen einer Tabelle mit Ausnahme von Filtern, die auf die angegebenen Spalten angewendet werden.

Die Syntax lautet: ALLEXCEPT(<table>,<column>[,<column>[,…]])

Im nächsten Beispiel soll der Verkaufswert pro Farbe immer den Wert aller Verkäufe der jeweiligen Farbe zeigen, auch wenn im Datenschnitt eine spezifische Kategorie aktiviert wird.

Mit dem Measure

Verkauf aller Zeilen, aller Farben =
CALCULATE(
[Total Verkäufe];
ALL(Produkte)
)

haben wir zwar erreicht, dass das Measure gegen alle Pivot Koordinaten (initialer Filter Kontext) immunisiert ist. Jedoch ist dies aber zugleich eine “Entweder Oder” Funktion.

Nochmals zu Erinnerung, wie das Ergebnis aller Verkäufe pro Farbe aussieht, wenn keine weiteren Filterbedingungen vorhanden sind.

Verkäufe pro Farbe

Verkäufe pro Farbe

Sobald das Measure “Verkauf aller Farben” dazukommt – dasjenige mit dem ALL-Filter, wechselt die Pivot Tabelle zu:

Verkäufe pro Farbe und alle Verkäufe

Verkäufe pro Farbe und alle Verkäufe

Nun geben wir einen Datenschnitt hinzu, der die Kategorien auf “Bekleidung” filtert.

Verkäufe pro Farbe und alle Verkäufe von Bekleidungen

Verkäufe pro Farbe und alle Verkäufe von Bekleidungen

Der Filter Kontext hat sich nun dahingehend geändert, dass nur noch die Verkäufe von Bekleidungen angezeigt werden. Jedoch haben wir die Möglichkeit verloren, wirklich ALLE Verkäufe anzeigen zu lassen.

Daher haben wir das Measure ” Verkauf aller Zeilen, aller Farben” geschrieben, dass auf die ganze Tabelle “Produkte” wirkt.

Verkäufe pro Farbe, alle Verkäufe von Bekleidungen und ALLE Verkäufe

Verkäufe pro Farbe, alle Verkäufe von Bekleidungen und ALLE Verkäufe

Diese Ausführungen waren zur Repetition gedacht. Wir möchten nun auch noch den Wert aller Verkäufe pro Farbe und zwar unabhängig von der Auswahl im Datenschnitt sehen.

Wir erfassen als Lösung das Measure

alle Verkäufe dieser Farbe :=
CALCULATE(
[Total Verkäufe];
ALLEXCEPT(
Produkte;
Produkte[Farbe]
)
)

Die Anweisung bewirkt, dass alle Spalten der Tabelle “Produkte” gemäss initialem Filter Kontext gefiltert werden, AUSSER der Spalte Produkte[Farbe].

Sobald wir das neue Measure in die Pivot Tabelle ziehen, sehen wir wieder alle Verkäufe pro Farbe.

Lösung mit ALLEXCEPT()

Lösung mit ALLEXCEPT()

 

ALLSELECTED

Fehlt noch eine letzte nützliche Funktion im Zusammenhang mit ALL: ALLSELECTED().

ALLSELECTED() entfernt den Kontextfilter aus Spalten und Zeilen, während alle anderen Bedingungen erhalten bleiben.

Die Syntax lautet: ALLSELECTED([<tableName> | <columnName>])

ALLSELECTED() wird nicht häufig verwendet, können doch mit ALL(Tabelle[Spalte]) bereits einige Konstellationen abgefangen werden. Die nachfolgende Pivot Tabelle zeigt jedoch eine Konstellation auf, welche ALLSELCTED() benötigt.

fehlende Werte für Komponenten

fehlende Werte für Komponenten

Im Datenschnitt sind zwei Kategorien aktiviert. Für die Verkäufe von Komponenten wird das Measure “Verkauf aller Farben” leer angezeigt.

Ihr erinnert euch? Leere Werte können ein Hinweis darauf sein, dass etwas mit dem Measure schiefgelaufen sein könnte.
Das ist aber hier nicht der Fall. Da noch nie Komponenten verkauft wurden (was aus dem ebenfalls leeren Measure “Total Verkäufe” ersichtlich ist), muss das Measure leer sein.

Wir wollen aber trotzdem, dass DAX auch für die Komponenten das Verkaufstotal aller Farben zurückgibt. Aber nur für diejenigen Kategorien, die im Datenschnitt selektiert (selected) sind.

Wir erfassen folgendes Measure:

Verkäufe der selektierten Produkte:=
=CALCULATE(
[Total Verkäufe];
ALLSELECTED(Produkte)
)

Dadurch geben wir DAX die Anweisung, dass nur auf den selektierten Kategorien die Berechnung durchgeführt werden soll. Dies kann ganz nützlich sein, wenn wir nur diese Werte berechnen haben möchten oder eine darauf basierende Verhältniszahl zeigen wollen.

Nachdem wir das neue Measure in die Pivot Tabelle gezogen haben, erhalten wir den gesuchten Wert für alle Zeilen.

Lösung mit ALLSELECTED()

Lösung mit ALLSELECTED()

 

Zusammenfassung

Wir haben gesehen, wie mit CALCULATE() die Funktionalität von einfachen Measures erheblich erweitert werden kann. Dabei ist jeweils zu beachten und zu verstehen, welcher Filter Kontext gerade auf die Measures wirken, damit auch das gewünschte Resultat zurückgegeben wird.

Der Filter Kontext setzt sich aus dem initialen Filter Kontext und den Bedingungen in der CALCULATE() Anweisung zusammen.

Als Eselsbrücke habe ich den FC Business Analytics – kurz FCBA eingeführt.

Der Filter Kontext kann mittels CALCULATE() ergänzt und verändert werden. Im Zusammenspiel mit

  • FILTER(),
  • ALL()
  • ALLEXCEPT() sowie
  • ALLSELECTED()

können fast die meisten möglichen Business Szenarien abgebildet werden.

Die Exceldatei mit den Beispielen kann hier heruntergeladen werden.

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

Ein Gedanke zu „CALCULATE – Teil 2

Kommentar verfassen

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.

%d Bloggern gefällt das: