DAX - RANKX Argument 3 (Value)

Von Lukas Hillesheim, 18. August 2023
Blog Article & Copyright by Lukas Hillesheim
In Internet-Artikeln wird meist eine vereinfachte Verwendung der Funktion RANKX gezeigt, bei der von den 5 möglichen Argumenten nur die Argumente 1, 2 und 4 belegt werden. Dabei ist unklar, welche Aufgabe Argument 3 (Value) hat. Der folgende Artikel zeigt, welche Auswirkung die Verwendung von Argument 3 hat und wie man sich die Interna der RANKX-Funktion vorstellen kann.

1. Standardmäßige Verwendung von RANKX (Argument 1, 2, 4)

Auf Grund einer Liste von Städten und deren Amount wird ein Ranking durchgeführt:
Figure 1: DAX code to compute a list of cities along with their total amount and rank
Figure 1: DAX code to compute a list of cities along with their total amount and rank
Figure 2: List of cities
Figure 2: List of cities

2. RANKX Internals

Die Ablauf-Logik kann man sich wie folgt vorstellen:

Schritt 1 - Kontext. Mit Hilfe von SUMMARIZECOLUMNS wird ein Grid aufgebaut. SUMMARIZECOLUMNS wurde deshalb in meinem Beispiel verwendet, weil viele Visuals im Hintergrund SUMMARIZECOLUMNS in ihrem DAX-Code aufrufen. Durch SUMMARIZECOLUMNS wird pro City ein (Filter-) Kontext erzeugt, der sowohl auf die Tabelle [City], als auch auf die Tabelle [FactSales01] einschränkend wirkt.
Schritt 2 - Aufbau des Lookup-Arrays. Pro City wird das RANK-Measure aufgerufen. RANKX erstellt zunächst eine Liste aller Städte (= Argument 1). Würde die Tabelle [City] ohne weitere Modifikation referenziert, käme der Filter des äußeren Kontexts zum Zuge und die Expression "City" würde nur noch eine Zeile bzw. nur noch eine einzelne Stadt zurückgeben. Daher stellt RANKX mit Hilfe von "ALL(City)" sicher, dass dieser Filter aufgehoben wird. Die Expression "ALL(City)" gibt also die vollständige Tabelle [City] zurück, wobei pro Stadt jeweils eine Zeile existiert. Alternativ könnte man an dieser Stelle auch mit ALLSELECTED arbeiten, um die Wirkung eines Slicers auf die Tabelle [City] nachzubilden. Auf diese zusätzlich Aufgabe wird hier verzichtet, da sie schon in vielen Internet-Artikeln gelöst wurde.
Schritt 3 - Iteration. RANKX iteriert nun über die Liste der Städte. Pro Schleifen-Durchgang wird Argument 2 der RANKX-Funktion - "[SumAmount]" - evaluiert. [SumAmount] wiederum gibt den Total Amount der jeweiligen Stadt zurück. In den Interna der Funktion RANKX ist festgelegt, dass der Wert einem Array hinzugefügt wird. Ein entsprechender OOP-Code, der diese Schleifen-Logik darstellt, könnte folgendermassen aussehen:
  • Decimal[] lookupArray = {};
  • ForEach ( Row r in City.Rows ) {
  • lookupArray += TotalAmount(r[CityName]);
  • }
Schritt 4 - Sort. Nachdem die Liste der City-Amounts erstellt wurde, muss sie noch sortiert werden. Durch den Sortiervorgang ergibt sich indirekt auch der Rank. Durch den Sortiervorgang ergibt sich, dass der höchste City-Amount an erster Stelle steht, der zweit-höchste an der zweiten Stelle etc. Der Rank-Value entspricht dem Index des Elements + 1. Der OOP-Code hierzu könnte folgendermassen aussehen:
  • lookupArray.Sort()
Schritt 5 - Zuweisung des Lookup-Arrays an jede Zeile. Der Inhalt des Arrays entspricht dem, was auch im Grid ausgegeben wird: { 18125, 10802, 9198 ... }. Da der Inhalt des Arrays konstant ist und sich im Rahmen der äußeren Iteration nicht ändert, wird vermutlich durch eine Optimierung der Engine sichergestellt sein, dass das Array nur einmal physikalisch erzeugt wird. Logisch jedoch wird das Lookup-Array für jede Zeile generiert, da die Generierung Teil der Measure-Expression ist:
Figure 3: Lookup array per row
Figure 3: Lookup array per row
Schritt 6 - Evaluierung von Argument 3. In Schritt 6 wird Argument 3 ausgewertet. Da Argument 3 im Aufruf nicht angegeben wurde, nimmt der Parser Argument 2 und setzt es an der Stelle von Argument 3 ein:
Unvollständige Übergabe (ohne Argument 3):
  • MEASURE FactSales01[Rank] =
  • RANKX(
  • ALL(City), // Arg. 1 - Set
  • [SumAmount], // Arg. 2 - Expression
  • , DESC) // Arg. 4 - Sort order
Vervollständigung durch den Parser:
  • MEASURE FactSales01[Rank] =
  • RANKX(
  • ALL(City), // Arg. 1 - Set
  • [SumAmount], // Arg. 2 - Expression
  • [SumAmount], // Arg. 3 - Value
  • DESC) // Arg. 4 - Sort order
Für die Zeile 3 mit "Prague" ergibt sich dadurch folgendes:
  • - "Prague" wird zur Konstruktion eines Filters verwendet
  • - Argument 3 mit [SumAmount] berechnet auf Grund dieses Filters einen Wert; in diesem Fall 9198
  • - Der Wert 9198 wird im Array gesucht und an Index 2 gefunden
  • - Der Rank-Value (2 + 1 = 3) wird ausgegeben

3. Übergabe eines konstanten Werts

Im Folgenden wird die Measure-Definition leicht verändert und der konstante Wert 9198 für Argument 3 eingesetzt. Das Array { 18125, 10802, 9198 ... } entsteht wieder auf die oben beschriebene Weise. Da nun für jede Stadt der Wert 9198 an Index 2 gefunden wird, ergibt sich für jede Stadt der Rank-Value 3.
Figure 4: Constant value 9198 for arg. 3
Figure 4: Constant value 9198 for arg. 3

4. Übergabe von NULL bzw. BLANK()

Der nächste Test zeigt, dass der NULL-Wert bzw. BLANK() dazu führt, dass der nächste Rank nach dem letzten existierenden zurückgegeben wird. Da sich 8 Elemente im Array befinden, erhält der BLANK()-Value den Rank 9.
Figure 5: Constant null value for arg. 3
Figure 5: Constant null value for arg. 3

5. Nicht-exakter Match

In diesem Beispiel wird gezeigt, dass ein Lookup-Value, der nicht im Array existiert, zunächst einer Range zugeordnet wird und dann der LowerBound der Range zurückgegeben wird. Im Beispiel wird der Wert 10801 im Array gesucht und in der Range 10802-9198 gefunden. Obwohl 10801 "sehr nahe" am UpperBound 10802 liegt, wird nicht der Index von 10802 berücksichtigt, sondern der LowerBound der Range mit dem Wert 9189. Dessen Index ist 2 und es wird der Rank-Value 3 zurückgegeben.
Figure 6: Return lower bound
Figure 6: Return lower bound

6. Out-Of-Range Wert

Sonderfälle von 5. sind Werte, die oberhalb des Max-Values oder unterhalb des Min-Values liegen. Werte, die größer als das Maximum sind, erhalten den Rank-Value 1. Werte, die kleiner als das Minimum sind, erhalten den letzten existierenden Rank-Value + 1. Im Beispiel wird der konstante Wert 100000 übergeben, wobei der Maximum-Wert 18125 beträgt.
Figure 7: Out-of-range value
Figure 7: Out-of-range value
  • - Argument 1 und 2 werden verwendet, um das Lookup-Array zu generieren
  • - Argument 3 wird verwendet, um einen Suchwert gegen das Array zu matchen
  • - Die restlichen Argumente steuern Sortierung und Density. Diese Argument werden hier nicht behandelt
In Internet-Artikeln wird meist eine vereinfachte Verwendung der Funktion RANKX gezeigt, bei der von den 5 möglichen Argumenten nur die Argumente 1, 2 und 4 belegt werden. Dabei ist unklar, welche Aufgabe Argument 3 (Value) hat. Der folgende Artikel zeigt, welche Auswirkung die Verwendung von Argument 3 hat und wie man sich die Interna der RANKX-Funktion vorstellen kann.

1. Standardmäßige Verwendung von RANKX (Argument 1, 2, 4)

Auf Grund einer Liste von Städten und deren Amount wird ein Ranking durchgeführt:
Figure 1: DAX code to compute a list of cities along with their total amount and rank
Figure 1: DAX code to compute a list of cities along with their total amount and rank
Figure 2: List of cities
Figure 2: List of cities

2. RANKX Internals

Die Ablauf-Logik kann man sich wie folgt vorstellen:

Schritt 1 - Aufbau des Grids. Mit Hilfe von SUMMARIZECOLUMNS wird ein Grid aufgebaut. SUMMARIZECOLUMNS wurde deshalb in meinem Beispiel verwendet, weil viele Visuals im Hintergrund SUMMARIZECOLUMNS in ihrem DAX-Code aufrufen. Durch SUMMARIZECOLUMNS wird pro City ein (Filter-) Kontext erzeugt, der sowohl auf die Tabelle [City], als auch auf die Tabelle [FactSales01] einschränkend wirkt.
Schritt 2 - Aufbau des Lookup-Arrays. Pro City wird das RANK-Measure aufgerufen. RANKX erstellt zunächst eine Liste aller Städte (= Argument 1). Würde die Tabelle [City] ohne weitere Modifikation referenziert, käme der Filter des äußeren Kontexts zum Zuge und die Expression "City" würde nur noch eine Zeile bzw. nur noch eine einzelne Stadt zurückgeben. Daher stellt RANKX mit Hilfe von "ALL(City)" sicher, dass dieser Filter aufgehoben wird. Die Expression "ALL(City)" gibt also die vollständige Tabelle [City] zurück, wobei pro Stadt jeweils eine Zeile existiert. Alternativ könnte man an dieser Stelle auch mit ALLSELECTED arbeiten, um die Wirkung eines Slicers auf die Tabelle [City] nachzubilden. Auf diese zusätzlich Aufgabe wird hier verzichtet, da sie schon in vielen Internet-Artikeln gelöst wurde.
Schritt 3 - Iteration. RANKX iteriert nun über die Liste der Städte. Pro Schleifen-Durchgang wird Argument 2 der RANKX-Funktion - "[SumAmount]" - evaluiert. [SumAmount] wiederum gibt den Total Amount der jeweiligen Stadt zurück. In den Interna der Funktion RANKX ist festgelegt, dass der Wert einem Array hinzugefügt wird. Ein entsprechender OOP-Code, der diese Schleifen-Logik darstellt, könnte folgendermassen aussehen:
  • Decimal[] lookupArray = {};
  • ForEach ( Row r in City.Rows ) {
  • lookupArray += TotalAmount(r[CityName]);
  • }
Schritt 4 - Sort. Nachdem die Liste der City-Amounts erstellt wurde, muss sie noch sortiert werden. Durch den Sortiervorgang ergibt sich indirekt auch der Rank. Durch den Sortiervorgang ergibt sich, dass der höchste City-Amount an erster Stelle steht, der zweit-höchste an der zweiten Stelle etc. Der Rank-Value entspricht dem Index des Elements + 1. Der OOP-Code hierzu könnte folgendermassen aussehen:
  • lookupArray.Sort()
Schritt 5 - Zuweisung des Lookup-Arrays an jede Zeile. Der Inhalt des Arrays entspricht dem, was auch im Grid ausgegeben wird: { 18125, 10802, 9198 ... }. Da der Inhalt des Arrays konstant ist und sich im Rahmen der äußeren Iteration nicht ändert, wird vermutlich durch eine Optimierung der Engine sichergestellt sein, dass das Array nur einmal physikalisch erzeugt wird. Logisch jedoch wird das Lookup-Array für jede Zeile generiert, da die Generierung Teil der Measure-Expression ist:
Figure 3: Lookup array per row
Figure 3: Lookup array per row
Schritt 6 - Evaluierung von Argument 3. In Schritt 6 wird Argument 3 ausgewertet. Da Argument 3 im Aufruf nicht angegeben wurde, nimmt der Parser Argument 2 und setzt es an der Stelle von Argument 3 ein:
Unvollständige Übergabe (ohne Argument 3):
  • MEASURE FactSales01[Rank] =
  • RANKX(
  • ALL(City), // Arg. 1 - Set
  • [SumAmount], // Arg. 2 - Expression
  • , DESC) // Arg. 4 - Sort order
Vervollständigung durch den Parser:
  • MEASURE FactSales01[Rank] =
  • RANKX(
  • ALL(City), // Arg. 1 - Set
  • [SumAmount], // Arg. 2 - Expression
  • [SumAmount], // Arg. 3 - Value
  • DESC) // Arg. 4 - Sort order
Für die Zeile 3 mit "Prague" ergibt sich dadurch folgendes:
  • - "Prague" wird zur Konstruktion eines Filters verwendet
  • - Argument 3 mit [SumAmount] berechnet auf Grund dieses Filters einen Wert; in diesem Fall 9198
  • - Der Wert 9198 wird im Array gesucht und an Index 2 gefunden
  • - Der Rank-Value (2 + 1 = 3) wird ausgegeben

3. Übergabe eines konstanten Werts

Im Folgenden wird die Measure-Definition leicht verändert und der konstante Wert 9198 für Argument 3 eingesetzt. Das Array { 18125, 10802, 9198 ... } entsteht wieder auf die oben beschriebene Weise. Da nun für jede Stadt der Wert 9198 an Index 2 gefunden wird, ergibt sich für jede Stadt der Rank-Value 3.
Figure 4: Constant value 9198 for arg. 3
Figure 4: Constant value 9198 for arg. 3

4. Übergabe von NULL bzw. BLANK()

Der nächste Test zeigt, dass der NULL-Wert bzw. BLANK() dazu führt, dass der nächste Rank nach dem letzten existierenden zurückgegeben wird. Da sich 8 Elemente im Array befinden, erhält der BLANK()-Value den Rank 9.
Figure 5: Constant null value for arg. 3
Figure 5: Constant null value for arg. 3

5. Nicht-exakter Match

In diesem Beispiel wird gezeigt, dass ein Lookup-Value, der nicht im Array existiert, zunächst einer Range zugeordnet wird und dann der LowerBound der Range zurückgegeben wird. Im Beispiel wird der Wert 10801 im Array gesucht und in der Range 10802-9198 gefunden. Obwohl 10801 "sehr nahe" am UpperBound 10802 liegt, wird nicht der Index von 10802 berücksichtigt, sondern der LowerBound der Range mit dem Wert 9189. Dessen Index ist 2 und es wird der Rank-Value 3 zurückgegeben.
Figure 6: Return lower bound
Figure 6: Return lower bound

6. Out-Of-Range Wert

Sonderfälle von 5. sind Werte, die oberhalb des Max-Values oder unterhalb des Min-Values liegen. Werte, die größer als das Maximum sind, erhalten den Rank-Value 1. Werte, die kleiner als das Minimum sind, erhalten den letzten existierenden Rank-Value + 1. Im Beispiel wird der konstante Wert 100000 übergeben, wobei der Maximum-Wert 18125 beträgt.
Figure 7: Out-of-range value
Figure 7: Out-of-range value
Zusammenfassung - Wofür sind die Argument von RANKX zuständig?:
  • - Argument 1 und 2 werden verwendet, um das Lookup-Array zu generieren
  • - Argument 3 wird verwendet, um einen Suchwert gegen das Array zu matchen
  • - Die restlichen Argumente steuern Sortierung und Density. Diese Argument werden hier nicht behandelt