Verständnis von Funktionen ORDERBY, PARTITIONBYund MATCHBY

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

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

Szenario

Beginnen wir mit einem Beispiel, das überhaupt keine Fensterfunktionen verwendet. Unten dargestellt ist eine Tabelle, die den Gesamtumsatz pro Farbe pro Kalenderjahr zurückgibt. Es gibt mehrere Möglichkeiten, diese Tabelle zu definieren, aber da wir daran interessiert sind, zu verstehen, was passiert DAX, 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 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. Hier sehen Sie das Ergebnis:

Stellen wir uns nun vor, wir versuchen, die Geschäftsfrage zu lösen, die Differenz im Umsatz, jahr-über-Jahr für jede Farbe zu 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. Zum Beispiel suchen wir bei der Kombination [Rot, 2019] nach den Verkaufszahlen für [Rot, 2018]. Sobald wir das haben, können wir ihn von den aktuellen Verkäufen subtrahieren und den erforderlichen Wert zurückgeben.

Verwenden von OFFSET

OFFSET ist perfekt für den typischen Vergleich mit früheren Berechnungsarten, die erforderlich sind, um die oben beschriebene Geschäftsfrage zu beantworten, da es uns ermöglicht, eine relative Bewegung durchzuführen. Unser erster Versuch könnte sein:

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 eine Spalte mit dem Namen PreviousColorSales zu erweitern. Der Inhalt dieser Spalte ist auf CurrentYearSales eingestellt, was SUM(FactInternetSales[SalesAmount]), for the previous Color (retrieved using OFFSET) entspricht.

Das Ergebnis ist:

Dies ist ein Schritt näher an unserem Ziel, aber wenn wir genau hinsehen, entspricht es nicht genau dem, wonach wir streben. Zum Beispiel wird für [Silver, 2017] der Wert von PreviousColorSales auf [Red, 2020] gesetzt.

Hinzufügen von ORDERBY

Diese Definition ist gleichbedeutend mit:

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 wird der Aufruf zu OFFSET verwendetORDERBY, um die Tabelle nach Color und CalendarYear in aufsteigender Reihenfolge zu sortieren, wodurch bestimmt wird, was als die vorherige Zeile gilt, die zurückgegeben wird.

Der Grund, warum diese beiden Ergebnisse gleichwertig sind, besteht darin, dass ORDERBY automatisch alle Spalten aus der Beziehung enthalten sind, die nicht enthalten PARTITIONBYsind. Da PARTITIONBY nicht angegeben wurde, ORDERBY ist die Einstellung "Color", "CalendarYear" und "CurrentYearSales" festgelegt. Da die Farb- und CalendarYear-Paare in der Beziehung jedoch eindeutig sind, ändert das Hinzufügen von CurrentYearSales das Ergebnis nicht. Selbst wenn wir "Farbe" nur in ORDERBYangeben würden, sind die Ergebnisse identisch, da "CalendarYear" automatisch hinzugefügt würde. Dies liegt daran, dass die Funktion beliebig viele Spalten ORDERBY hinzu addiert, um sicherzustellen, dass jede Zeile durch die ORDERBY spalten 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, das wir anstreben, können wir PARTITIONBY verwenden, wie im folgenden berechneten Tabellenausdruck gezeigt:

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 ORDERBY hier optional ist, da ORDERBY automatisch alle Spalten aus der Beziehung enthält, die nicht in PARTITIONBYangegeben sind. Der folgende Ausdruck gibt also dieselben 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]
        )
    )

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

Wie sie in dieser Tabelle sehen, zeigt die Spalte "PreviousYearSalesForSameColor" den Umsatz für das Vorjahr für dieselbe Farbe an. Für [Rot, 2020] gibt sie den Umsatz für [Rot, 2019] zurück usw. Wenn kein Vorjahr vorhanden ist, z. B. im Fall von [Rot, 2017], wird kein Wert zurückgegeben.

Sie können sich PARTITIONBY als eine Möglichkeit vorstellen, die Tabelle in Teile aufzuteilen, in denen die OFFSET Berechnung ausgeführt wird. Im obigen Beispiel wird die Tabelle in so viele Teile unterteilt, wie es Farben gibt, eine für jede Farbe. Anschließend wird die OFFSET innerhalb jedes Teils nach CalendarYear berechnet.

Visuell betrachtet passiert Folgendes:

Zuerst führt der Aufruf von PARTITIONBY dazu, dass die Tabelle in Teile unterteilt wird, eine für jede Farbe. Dies wird durch die hellblauen Felder im Tabellenbild dargestellt. Stellen Sie als Nächstes sicher, ORDERBY dass jeder Teil nach CalendarYear sortiert ist (dargestellt durch die orangefarbenen Pfeile). 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 im selben Teil vorhanden ist, ist das Ergebnis in dieser Zeile für die Spalte "PreviousYearSalesForSameColor" leer.

Um das Endergebnis zu erzielen, müssen wir "CurrentYearSales" einfach von dem Umsatz des Vorjahrs für dieselbe Farbe subtrahieren, die vom Aufruf OFFSETzurückgegeben wird. Da wir nicht daran interessiert sind, den Umsatz des Vorjahrs für dieselbe Farbe anzuzeigen, sondern nur im aktuellen Jahr und im Vergleich zum Vorjahr. Hier ist der endgültige berechnete 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:

Verwenden von MATCHBY

Möglicherweise haben Sie bemerkt, dass wir überhaupt nicht angegeben MATCHBY haben. In diesem Fall ist es nicht erforderlich. Die Spalten in ORDERBY und PARTITIONBY (soweit sie in den obigen Beispielen angegeben wurden) reichen aus, um jede Zeile eindeutig zu identifizieren. Da wir MATCHBY nicht angegeben haben, werden die in ORDERBY und PARTITIONBY angegebenen Spalten verwendet, um jede Zeile eindeutig zu identifizieren, damit sie vergleichbar sind, um ein aussagekräftiges Ergebnis in OFFSET zu erzielen. Wenn die Spalten in ORDERBY und PARTITIONBY nicht eindeutig jede Zeile identifizieren können, können der Klausel zusätzliche Spalten hinzugefügt ORDERBY werden, wenn diese zusätzlichen Spalten zulassen, dass jede Zeile eindeutig identifiziert werden kann. Wenn dies nicht möglich ist, wird ein Fehler zurückgegeben. In diesem letzten Fall kann die Angabe MATCHBY hilfreich sein, um den Fehler zu beheben.

Wenn MATCHBY angegeben, 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 dies nicht erforderlich ist, sollten Sie explizit angeben MATCHBY, um Verwirrung zu vermeiden.

Anschließend der letzte Ausdruck aus den obigen Beispielen:

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 wie im folgenden äquivalenten Ausdruck angegeben angeben MATCHBY :

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 wird, werden sowohl die in MATCHBY und in PARTITIONBY angegebenen Spalten verwendet, um Zeilen eindeutig zu identifizieren. Da "Color" sowohl in MATCHBY als auch in 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 MATCHBY in den bisher behandelten Beispielen nicht erforderlich ist, sehen wir’uns ein etwas anderes Beispiel an, das MATCHBYerfordert. In diesem Fall verfügen wir über eine Liste der Auftragspositionen. Jede Zeile stellt eine Auftragszeile für eine Bestellung dar. Eine Bestellung kann mehrere Bestellpositionen enthalten und Bestellzeile 1 erscheint in vielen Bestellungen. Darüber hinaus verfügen wir für jede Bestellzeile über einen ProductKey und einen SalesAmount. Ein Beispiel für die relevanten Spalten in der Tabelle sieht wie folgt aus:

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

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

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

Dieser Ausdruck gibt einen Fehler zurück: "OFFSETDer Relation-Parameter hat möglicherweise doppelte Zeilen, die nicht zulässig sind."

Damit dieser Ausdruck funktioniert, MATCHBY muss 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, bei dem ihr Vorhandensein 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]
            )
    )

Und dieser Ausdruck liefert tatsächlich die Ergebnisse, die wir suchen.

Verwandte Inhalte

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER