Freigeben über

Grundlegendes zu den Funktionen ORDERBY, PARTITIONBY und MATCHBY

  • Artikel

Die Funktionen ORDERBY, PARTITIONBY und MATCHBY in DAX sind spezielle Funktionen, die nur zusammen mit DAX-Fensterfunktionen verwendet werden können: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Das Verständnis von ORDERBY, PARTITIONBY und MATCHBY ist für eine erfolgreiche Verwendung der Fensterfunktionen entscheidend. Die hier dargestellten Beispiele verwenden OFFSET, gelten aber auch genauso für die anderen Fensterfunktionen.

Szenario

Beginnen wir mit einem Beispiel, in dem überhaupt keine Fensterfunktionen verwendet werden. Unten sehen Sie eine Tabelle, die den Gesamtumsatz pro Kalenderjahr für jede Farbe angibt. Es gibt mehrere Möglichkeiten, diese Tabelle zu definieren. Da wir aber verstehen wollen, was in DAX geschieht, verwenden wir eine berechnete Tabelle. Hier ist der Tabellenausdruck:

BasicTable = 
    SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
    )

Dieser berechnete Tabellenausdruck verwendet SUMMARIZECOLUMNS, um den Wert SUM der Spalte SalesAmount in der Tabelle FactInternetSales, die Spalte Color in der Tabelle DimProduct und die Spalte CalendarYear in der Tabelle DimDate zu berechnen. Das Ergebnis sieht wie folgt aus:

Color CalendarYear CurrentYearSales
„Schwarz“ 2017 393885
„Schwarz“ 2018 1818835
„Schwarz“ 2019 3981638
„Schwarz“ 2020 2644054
"Blue" 2019 994448
"Blue" 2020 1284648
„Multi“ 2019 48622
„Multi“ 2020 57849
„NA“ 2019 207822
„NA“ 2020 227295
„Rot“ 2017 2961198
„Rot“ 2018 3686935
„Rot“ 2019 900175
„Rot“ 2020 176022
„Silber“ 2017 326399
„Silber“ 2018 750026
„Silber“ 2019 2165176
„Silber“ 2020 1871788
„Weiß“ 2019 2517
„Weiß“ 2020 2589
„Gelb“ 2018 163071
„Gelb“ 2019 2072083
„Gelb“ 2020 2621602

Nehmen wir nun an, wir wollten den Umsatzunterschied von Jahr zu Jahr für jede Farbe berechnen. Dafür benötigen wir eine Möglichkeit, Umsätze für die gleiche Farbe im Vorjahr zu finden und diese von den Umsätzen im aktuellen Jahr zu subtrahieren. Für die Kombination [Rot, 2019] suchen wir beispielsweise nach Umsätzen für [Rot, 2018]. Danach können wir diesen Wert von den aktuellen Umsätzen subtrahieren und den erforderlichen Wert zurückgeben.

Verwendung von OFFSET

OFFSET eignet sich bestens für den typischen Vergleich mit vorherigen Berechnungen, der erforderlich ist, um die oben beschriebene Berechnung auszuführen, da es uns ermöglicht, eine relative Bewegung durchzuführen. Unser erster Versuch könnte wie folgt aussehen:

1stAttempt = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation
            ),
            [CurrentYearSales]
        )
    )

Mit diesem Ausdruck geschieht viel. Wir haben ADDCOLUMNS verwendet, um die Tabelle von zuvor um die Spalte PreviousColorSales zu erweitern. Der Inhalt dieser Spalte ist auf CurrentYearSales, was (SUM(FactInternetSales[SalesAmount]) entspricht, für die vorherige Farbe festgelegt (mit OFFSET abgerufen).

Es wird folgendes Ergebnis ausgegeben:

Color CalendarYear CurrentYearSales PreviousColorSales
„Schwarz“ 2017 393885
„Schwarz“ 2018 1818835 393885
„Schwarz“ 2019 3981638 1818835
„Schwarz“ 2020 2644054 3981638
"Blue" 2019 994448 2644054
"Blue" 2020 1284648 994448
„Multi“ 2019 48622 1284648
„Multi“ 2020 57849 48622
„NA“ 2019 207822 57849
„NA“ 2020 227295 207822
„Rot“ 2017 2961198 227295
„Rot“ 2018 3686935 2961198
„Rot“ 2019 900175 3686935
„Rot“ 2020 176022 900175
„Silber“ 2017 326399 176022
„Silber“ 2018 750026 326399
„Silber“ 2019 2165176 750026
„Silber“ 2020 1871788 2165176
„Weiß“ 2019 2517 1871788
„Weiß“ 2020 2589 2517
„Gelb“ 2018 163071 2589
„Gelb“ 2019 2072083 163071
„Gelb“ 2020 2621602 2072083

Damit sind wir unserem Ziel einen Schritt näher. Wenn wir uns das Ergebnis aber genauer ansehen, stimmt es nicht genau mit dem überein, was wir wollen. Für [Silber, 2017] ist PreviousColorSales beispielsweise auf [Rot, 2020] festgelegt.

Hinzufügen von ORDERBY

Diese obige Definition entspricht folgendem:

1stAttemptWithORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([Color], ASC, [CalendarYear], ASC, [CurrentYearSales], ASC)      
            ),
            [CurrentYearSales]
        )
    )

In diesem Fall verwendet der Aufruf von OFFSET ORDERBY, um die Tabelle nach Farbe und Kalenderjahr in aufsteigender Reihenfolge zu sortieren, wodurch bestimmt wird, was als die vorherige zurückgegebene Zeile betrachtet wird.

Der Grund, warum diese beiden Ergebnisse äquivalent sind, ist, dass ORDERBY automatisch alle Spalten aus der Beziehung enthält, die sich nicht in PARTITIONBY befinden. Da PARTITIONBY nicht angegeben wurde, ist ORDERBY auf Color, CalendarYear und CurrentYearSales festgelegt. Da die Paare zwischen Color und CalendarYear in der Beziehung jedoch eindeutig sind, ändert das Hinzufügen von CurrentYearSales das Ergebnis nicht. Selbst wenn wir Color nur in ORDERBY angeben würden, sind die Ergebnisse identisch, da CalendarYear automatisch hinzugefügt würde. Dies liegt daran, dass die Funktion ORDERBY so viele Spalten wie erforderlich hinzufügt, um sicherzustellen, dass jede Zeile durch die Spalten ORDERBY und PARTITIONBY eindeutig identifiziert werden kann:

1stAttemptWithORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS(
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

Hinzufügen von PARTITIONBY

Um nun fast das Ergebnis zu erhalten, können wir PARTITIONBY verwenden, wie im folgenden berechneten Tabellenausdruck dargestellt:

UsingPARTITIONBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]), 
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

Beachten Sie, dass die Angabe von ORDERBY hier optional ist, da ORDERBY automatisch alle Spalten aus der Beziehung enthält, die nicht in PARTITIONBY angegeben sind. Daher gibt der folgende Ausdruck die gleichen Ergebnisse zurück, da ORDERBY automatisch auf CalendarYear und CurrentYearSales festgelegt ist:

UsingPARTITIONBYWithoutORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

Hinweis

ORDERBY ist zwar automatisch auf CalendarYear und CurrentYearSales festgelegt, aber es gibt keine Garantie, in welcher Reihenfolge sie hinzugefügt werden. Wenn CurrentYearSales vor CalendarYear hinzugefügt wird, entspricht die resultierende Reihenfolge nicht den Erwartungen. Geben Sie ORDERBY und PARTITIONBY explizit an, um Verwirrung und unerwartete Ergebnisse zu vermeiden.

Beide Ausdrücke geben das Ergebnis zurück, das wir wollen:

Color CalendarYear CurrentYearSales PreviousYearSalesForSameColor
„Schwarz“ 2017 393885
„Schwarz“ 2018 1818835 393885
„Schwarz“ 2019 3981638 1818835
„Schwarz“ 2020 2644054 3981638
"Blue" 2019 994448
"Blue" 2020 1284648 994448
„Multi“ 2019 48622
„Multi“ 2020 57849 48622
„NA“ 2019 207822
„NA“ 2020 227295 207822
„Rot“ 2017 2961198
„Rot“ 2018 3686935 2961198
„Rot“ 2019 900175 3686935
„Rot“ 2020 176022 900175
„Silber“ 2017 326399
„Silber“ 2018 750026 326399
„Silber“ 2019 2165176 750026
„Silber“ 2020 1871788 2165176
„Weiß“ 2019 2517
„Weiß“ 2020 2589 2517
„Gelb“ 2018 163071
„Gelb“ 2019 2072083 163071
„Gelb“ 2020 2621602 2072083

Wie sie in dieser Tabelle sehen, zeigt die Spalte PreviousYearSalesForSameColor den Umsatz für das Vorjahr für dieselbe Farbe an. Für [Red, 2020] werden die Umsätze für [Red, 2019] zurückgegeben usw. Wenn kein vorheriges Jahr vorhanden ist, z. B. im Fall von [Red, 2017], wird kein Wert zurückgegeben.

Sie können PARTITIONBY als eine Möglichkeit ansehen, die Tabelle in Teile zu unterteilen, in denen die OFFSET-Berechnung ausgeführt werden soll. Im obigen Beispiel ist die Tabelle in so viele Teile unterteilt, wie Farben vorhanden sind, eine für jede Farbe. Anschließend wird innerhalb jedes Teils der OFFSET berechnet, sortiert nach CalendarYear.

Visuell geschieht folgendes:

Table showing OFFSET by Calendar Year

Erstens führt der Aufruf von PARTITIONBY dazu, dass die Tabelle in Teile unterteilt wird, einen für jede Farbe. Dies wird durch die hellblauen Felder in der Tabelle dargestellt. Als Nächstes stellt ORDERBY sicher, dass jeder Teil nach CalendarYear (dargestellt durch die orangefarbenen Pfeile) sortiert ist. Schließlich findet OFFSET in jedem sortierten Teil für jede Zeile die Zeile darüber und gibt diesen Wert in der Spalte PreviousYearSalesForSameColor zurück. Da für jede erste Zeile in jedem Teil keine vorherige Zeile in demselben Teil vorhanden ist, ist das Ergebnis in dieser Zeile für die Spalte PreviousYearSalesForSameColor leer.

Um das Endergebnis zu erzielen, müssen wir einfach den Wert von CurrentYearSales vom Umsatz des Vorjahres für die gleiche Farbe subtrahieren, der durch den Aufruf von OFFSET zurückgegeben wurde. Wir wollen nicht den Vorjahresumsatz für dieselbe Farbe anzeigen, sondern nur den aktuellen Jahresumsatz und den Unterschied zwischen Jahr zu Jahr. Hier sehen Sie den endgültig berechneten Tabellenausdruck:

FinalResult = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

Und hier ist das Ergebnis dieses Ausdrucks:

Color CalendarYear CurrentYearSales YoYSalesForSameColor
„Schwarz“ 2017 393885 393885
„Schwarz“ 2018 1818835 1424950
„Schwarz“ 2019 3981638 2162803
„Schwarz“ 2020 2644054 -1337584
"Blue" 2019 994448 994448
"Blue" 2020 1284648 290200
„Multi“ 2019 48622 48622
„Multi“ 2020 57849 9227
„NA“ 2019 207822 207822
„NA“ 2020 227295 19473
„Rot“ 2017 2961198 2961198
„Rot“ 2018 3686935 725737
„Rot“ 2019 900175 -2786760
„Rot“ 2020 176022 -724153
„Silber“ 2017 326399 326399
„Silber“ 2018 750026 423627
„Silber“ 2019 2165176 1415150
„Silber“ 2020 1871788 -293388
„Weiß“ 2019 2517 2517
„Weiß“ 2020 2589 72
„Gelb“ 2018 163071 163071
„Gelb“ 2019 2072083 1909012
„Gelb“ 2020 2621602 549519

Verwendung von MATCHBY

Eventuell haben Sie bemerkt, dass wir MATCHBY überhaupt nicht angegeben haben. In diesem Fall ist dies nicht erforderlich. Die Spalten in ORDERBY und PARTITIONBY (sofern sie in den obigen Beispielen angegeben wurden) reichen aus, um jede Zeile eindeutig zu identifizieren. Da MATCHBY nicht angegeben wurde, werden die in ORDERBY und PARTITIONBY angegebenen Spalten verwendet, um jede Zeile eindeutig zu identifizieren. So können diese verglichen werden, um bei OFFSET ein aussagekräftiges Ergebnis zu ermöglichen. Wenn die Spalten in ORDERBY und PARTITIONBY nicht jede Zeile eindeutig identifizieren können, können der ORDERBY-Klausel zusätzliche Spalten hinzugefügt werden, wenn diese zusätzlichen Spalten die eindeutige Identifizierung jeder Zeile ermöglichen. Wenn dies nicht möglich ist, wird ein Fehler zurückgegeben. Im letzten Fall kann die Angabe von MATCHBY helfen, den Fehler zu beheben.

Wenn MATCHBY angegeben ist, werden die Spalten in MATCHBY und PARTITIONBY verwendet, um jede Zeile eindeutig zu identifizieren. Wenn dies nicht möglich ist, wird ein Fehler zurückgegeben. Auch wenn MATCHBY nicht erforderlich ist, sollten Sie MATCHBY explizit angeben, um Verwechslungen zu vermeiden.

Ausgehend von den obigen Beispielen ist der letzte Ausdruck wie folgt:

FinalResult = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

Wenn wir explizit angeben möchten, wie Zeilen eindeutig identifiziert werden sollen, können wir MATCHBY angeben, wie im folgenden äquivalenten Ausdruck gezeigt:

FinalResultWithExplicitMATCHBYOnColorAndCalendarYear = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color]),
                MATCHBY ([Color], [CalendarYear])
            ),
            [CurrentYearSales]
        )
    )

Da MATCHBY angegeben ist, werden sowohl die in MATCHBY als auch in PARTITIONBY angegebenen Spalten verwendet, um Zeilen eindeutig zu identifizieren. Da Color sowohl in MATCHBY als auch PARTITIONBY angegeben ist, entspricht der folgende Ausdruck dem vorherigen Ausdruck:

FinalResultWithExplicitMATCHBYOnCalendarYear = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color]),
                MATCHBY ([CalendarYear])
            ),
            [CurrentYearSales]
        )
    )

Da die Angabe von MATCHBY in den bisher betrachteten Beispielen nicht erforderlich ist, sehen wir uns ein anderes Beispiel an, bei dem MATCHBY erforderlich ist. In diesem Fall verfügen wir über eine Liste der Auftragspositionen. Jede Zeile stellt eine Auftragsposition für eine Bestellung dar. Eine Bestellung kann mehrere Auftragspositionen aufweisen. Die Auftragsposition 1 wird in vielen Bestellungen angezeigt. Darüber hinaus verfügen wir für jede Auftragsposition über einen ProductKey und eine SalesAmount. Ein Beispiel für die relevanten Spalten in der Tabelle sieht wie folgt aus:

SalesOrderNumber SalesOrderLineNumber ProductKey SalesAmount
SO51900 1 528 4,99
SO51948 1 528 5,99
SO52043 1 528 4,99
SO52045 1 528 4,99
SO52094 1 528 4,99
SO52175 1 528 4,99
SO52190 1 528 4,99
SO52232 1 528 4,99
SO52234 1 528 4,99
SO52234 2 529 3,99

Beachten Sie, dass SalesOrderNumber und SalesOrderLineNumber beide erforderlich sind, um Zeilen eindeutig zu identifizieren.

Für jede Bestellung möchten wir den vorherigen Umsatzbetrag desselben Produkts (dargestellt durch den ProductKey) zurückgeben, der von SalesAmount in absteigender Reihenfolge bestellt wurde. Der folgende Ausdruck funktioniert nicht, da in vRelation möglicherweise mehrere Zeilen vorhanden sind, wenn er an OFFSET übergeben wird:

ThisExpressionFailsBecauseMATCHBYIsMissing = 
    ADDCOLUMNS (
        FactInternetSales,
        "Previous Sales Amount",
            SELECTCOLUMNS (
                OFFSET (
                    -1,
                    FactInternetSales,
                    ORDERBY ( FactInternetSales[SalesAmount], DESC ),
                    PARTITIONBY ( FactInternetSales[ProductKey] )
                ),
                FactInternetSales[SalesAmount]
            )
    )

Der Ausdruck gibt einen Fehler zurück: „Der Parameter von OFFSET kann doppelte Zeilen enthalten, was nicht zulässig ist.“

Damit dieser Ausdruck funktioniert, muss MATCHBY angegeben werden und alle Spalten enthalten, die eine Zeile eindeutig definieren. MATCHBY ist hier erforderlich, da die Beziehung FactInternetSales keine expliziten Schlüssel oder eindeutigen Spalten enthält. Die Spalten SalesOrderNumber und SalesOrderLineNumber bilden jedoch zusammen einen zusammengesetzten Schlüssel, in dem ihre gemeinsame Existenz in der Beziehung eindeutig ist und daher jede Zeile eindeutig identifizieren kann. Die Angabe von SalesOrderNumber oder SalesOrderLineNumber reicht nicht aus, da beide Spalten wiederholte Werte enthalten. Der folgende Ausdruck löst das Problem:

ThisExpressionWorksBecauseOfMATCHBY = 
    ADDCOLUMNS (
        FactInternetSales,
        "Previous Sales Amount",
            SELECTCOLUMNS (
                OFFSET (
                    -1,
                    FactInternetSales,
                    ORDERBY ( FactInternetSales[SalesAmount], DESC ),
                    PARTITIONBY ( FactInternetSales[ProductKey] ),
                    MATCHBY ( FactInternetSales[SalesOrderNumber], 
                                FactInternetSales[SalesOrderLineNumber] )
                ),
                FactInternetSales[SalesAmount]
            )
    )

Dieser Ausdruck gibt die Ergebnisse zurück, die wir wollen:

SalesOrderNumber SalesOrderLineNumber ProductKey SalesAmount Vorheriger Umsatzbetrag
SO51900 1 528 5,99
SO51948 1 528 4,99 5,99
SO52043 1 528 4,99 4,99
SO52045 1 528 4,99 4,99
SO52094 1 528 4,99 4,99
SO52175 1 528 4,99 4,99
SO52190 1 528 4,99 4,99
SO52232 1 528 4,99 4,99
SO52234 1 528 4,99 4,99
SO52234 2 529 3,99

Zugehöriger Inhalt

ORDERBY
PARTITIONBY
MATCHBY
INDEX
OFFSET
WINDOW
RANK
ROWNUMBER