Measure oder virtuelle Tabelle als Filter für CALCULATE verwenden

October 17, 2021
Denis Selimovic
DAXcomment 3Created with Sketch Beta.2 comments

This post is also available in: Englisch


Vergangene Woche hatte ein Arbeitskollege bei einem Kunden eine interessante Fragestellung. Dieser wollte eine Berechnung per CALCULATE durchführen, allerdings nur auf die Datensätze der Tabelle, bei denen ein Measure einen bestimmten Wert hat. Oder einfacher ausgedrückt, er möchte die Tabelle nach einem Measure filtern und dann auf die gefilterte Tabelle die Berechnung ausführen.

Measure benötigt immer Filterkontext

Eine Besonderheit bei Measures ist hierbei, dass diese immer einen Filterkontext benötigen. Um ein einfaches Beispiel zu machen, wenn mein Measure die Formel hat Sales Amount = SUM( myTable[Sales Amount] ) , dann benötigt diese Formel einen Filterkontext. Möchte ich die Verkaufszahlen pro Produkt oder nach Jahr? Oder vielleicht doch nach Land oder nach Kunde? All diese Fragestellungen lassen sich mit der gerade genannten Formel lösen, aber das Ergebnis ergibt sich aus dem Filterkontext.

Bestes Produkt nach Kategorie

Für dieses Szenario möchte ich ein einfaches Beispiel verwenden. Wir gehen davon aus ich habe verschiedene Früchte und diese sind in Farbkategorien eingeteilt:

Color Product
Green Kiwi
Green Avocado
Orange Orange
Orange Mandarin
Orange Peach
Red Apple
Red Cherry
Yellow Banana
Yellow Lemon

Natürlich interessieren mich die Verkäufe meiner Früchte, weshalb ich diese in Power BI analysiere. Genauer interessiert es mich, wie pro Produktfarbe der jeweilige Bestseller abschneidet. Den Bestseller pro Produktfarbe bekomme ich mit einem Measure, welches mir den Rang zurückgibt. Visuell bedeutet das ich möchte für 2020 nur die gelb markierten Zeilen berücksichtigen:

Ranking im Jahr 2020
Ranking im Jahr 2020

Die Logik soll dabei abhängig vom Filterkontext funktionieren. Wenn ich also die Werte für 2021 analysiere, dann kann es sein, dass sich das Ranking ändert, wie beispielsweise für die Farben Green und Orange:

Geändertes Ranking im Jahr 2021
Geändertes Ranking im Jahr 2021

Basierend auf den Top-Produkten aller Farbkategorien möchte ich nun ein Measure erstellen, beispielweise den durchschnittlichen Umsatz dieser Top-Produkte.

Erstellung einer virtuellen Tabelle mit relevantem Measure

Wie ist es nun möglich, dass wir nun in einem Measure immer auf die Zeilen mit dem Rank 1 filtern? Ein Measure wie unser Ranking-Measure benötigt schließlich immer einen Filterkontext. Die Antwort ist relativ einfach, wir müssen uns unseren Filterkontext manuell auch im Measure zusammenbauen, mithilfe von virtuellen Tabellen.

Um die Zwischenschritte der Entwicklung besser nachverfolgen zu können, werden wir das Measure im DAX Studio entwickeln. Hierzu schauen wir uns mit der EVALUATE Funktion zuerst ein Mal die Tabelle Products an.

Darstellung Produkt-Tabelle
Darstellung Produkt-Tabelle

Dieser Tabelle möchten wir im nächsten Schritt unser Ranking hinzufügen. Dies können wir mit der ADDCOLUMNS Funktion erreichen:

Hinzufügen des Rankings zur virtuellen Tabelle
Hinzufügen des Rankings zur virtuellen Tabelle

Diese virtuelle Tabelle können wir uns in einer Variablen speichern, damit der Code somit übersichtlicher wird. Mit dem Befehl DEFINE VAR können wir eine Variable mit dem Namen vRankTable erstellen und dieser weisen wir die virtuelle Tabelle zu. Die Tabelle geben wir am Ende per EVALUATE Befehl wieder aus. Diese sieht noch identisch aus:

Hinzufügen des Rankings per ADDCOLUMNS Funktion
Hinzufügen des Rankings per ADDCOLUMNS Funktion

Nun möchten wir die Tabelle auf die Produkte reduzieren, welche in ihrer Farbkategorie den Rank 1 haben. Hierzu verwenden wir die FILTER Funktion und filtern auf Rang 1. Als zu filternde Tabelle nehmen wir unsere gerade in der Variablen gespeicherte virtuelle Tabelle:

Filterung der virtuellen Tabelle
Filterung der virtuellen Tabelle

Diese gefilterte Tabelle können wir zur besseren Übersicht ebenfalls in einer Variablen speichern:

Speichern der gefilterten Tabelle als Variable
Speichern der gefilterten Tabelle als Variable

Verwendung der gefilterten Tabelle als Basis für CALCULATE

In unserem Fall haben wir nun die Produkt-Tabelle auf die Top-Produkte im aktuellen Filterkontext gefiltert. Nun stellt sich die Frage, wie wir dies in CALCULATE integrieren können, schließlich benötigt CALCULATE keine Tabelle als Parameter, sondern nur Filterkriterien. Die Antwort ist simpel und eventuell überraschend, aber Tabellen sind in Power BI Filter!

Dies klingt auf den ersten Blick wenig intuitiv, da man häufig Funktionen wie FILTER oder REMOVEFILTERS einsetzt, um den Filterkontext zu modifizieren. Aber im Grunde liefern die meisten Funktionen einfach Tabellen zurück, die den Filterkontext des aktuellen Measures überschreiben. Hierzu empfehle ich, sich mit dem Thema Expanded Tables auseinanderzusetzen. Dies hilft enorm beim Verständnis von DAX.

Expanded Tables wäre allerdings ein Thema für eine ganze Artikel-Serie, weshalb ich jetzt nicht auf die Grundlagen, sondern einfach auf die Verwendung von Tabellen als Filter eingehen möchte.

In unserem Beispiel bedeutet das, dass wir die virtuelle Tabelle einfach als Filter für CALCULATE verwenden können. Da CALCULATE einen einzelnen Wert zurückliefert, wir im DAX Studio aber eine Tabelle liefern müssen, können wir das CALCULATE mit geschweiften Klammern in Schnellschreibweise als Tabelle zurückgeben:

Verwendung der virtuellen Tabelle als Filter für CALCULATE
Verwendung der virtuellen Tabelle als Filter für CALCULATE

Als Beispiel erzielen unsere Top-Produkte im Durchschnitt einen Verkauf von 2’833.33 USD. Wenn wir dies vergleichen mit dem allgemeinen Durchschnitt aller Produkte, so ist der allgemeine leicht darunter:

Ergebnis ohne weitere Filterung
Ergebnis ohne weitere Filterung

Integration in neues Measure

Am Ende müssen wir das gerade entwickelte Measure noch ins Power BI überführen. Hierzu kann das DEFINE durch den Measure-Namen ersetzt und das EVALUATE durch ein RETURN ersetzt werden. Die geschweiften Klammern benötigen wir nicht mehr, da das Measure nun einen einzelnen Wert zurückgeben muss und keine Tabelle wie im DAX Studio:

Average Top Products = 
VAR vRankTable =
    ADDCOLUMNS (
        Products,
        "@Rank", [Rank Color]
    )
VAR vFilteredTable =
    FILTER (
        vRankTable,
        [@Rank] = 1
    )
RETURN
    CALCULATE (
        AVERAGE ( Sales[Amount] ) ,
        vFilteredTable
    )

Als Ergebnis können wir in unserem CALCULATE alle möglichen Funktionen verwenden. In unserem Fall beispielsweise mit einem Durchschnitt auf die Verkäufe des Top Produktes in jeder Farbkategorie. Und das Measure berücksichtigt vollkommen dynamisch jeglichen Filterkontext:

Anzeige der Top-Produkte abhängig von Filterkontext
Anzeige der Top-Produkte abhängig von Filterkontext

Download Beispieldatei