De functies ORDERBY, PARTITIONBY en MATCHBY

De functies ORDERBY, PARTITIONBY en MATCHBY in DAX zijn speciale functies die alleen kunnen worden gebruikt in combinatie met DAX-vensterfuncties: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Informatie over ORDERBY, PARTITIONBY en MATCHBY is essentieel voor het gebruik van de vensterfuncties. De voorbeelden die hier worden gegeven, gebruiken OFFSET, maar zijn vergelijkbaar van toepassing op de andere vensterfuncties.

Scenario

Laten we beginnen met een voorbeeld dat helemaal geen vensterfuncties gebruikt. Hieronder ziet u een tabel die de totale verkoop, per kleur, per kalenderjaar retourneert. Er zijn meerdere manieren om deze tabel te definiëren, maar omdat we graag weten wat er in DAX gebeurt, gebruiken we een berekende tabel. Dit is de tabelexpressie:

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

Deze berekende tabelexpressie gebruikt SUMMARIZECOLUMNS om de SOM van de kolom SalesAmount in de tabel FactInternetSales te berekenen, op basis van de kolom Color uit de tabel DimProduct en de kolom CalendarYear uit de tabel DimDate. Dit is het resultaat:

Color	CalendarYear	CurrentYearSales
"Zwart"	2017	393885
"Zwart"	2018	1818835
"Zwart"	2019	3981638
"Zwart"	2020	2644054
"Blauw"	2019	994448
"Blauw"	2020	1284648
"Multi"	2019	48622
"Multi"	2020	57849
"NA"	2019	207822
"NA"	2020	227295
"Rood"	2017	2961198
"Rood"	2018	3686935
"Rood"	2019	900175
"Rood"	2020	176022
"Zilver"	2017	326399
"Zilver"	2018	750026
"Zilver"	2019	2165176
"Zilver"	2020	1871788
"Wit"	2019	2517
"Wit"	2020	2589
"Geel"	2018	163071
"Geel"	2019	2072083
"Geel"	2020	2621602

Stel nu dat we proberen de bedrijfsvraag op te lossen van het berekenen van het verschil in verkoop, jaar-op-jaar voor elke kleur. In feite hebben we een manier nodig om de verkoop voor dezelfde kleur in het vorige jaar te vinden en die af te trekken van de verkoop in het huidige jaar, in context. Voor de combinatie [Rood, 2019] zijn we bijvoorbeeld op zoek naar verkoop voor [Rood, 2018]. Zodra we dat hebben, kunnen we deze aftrekken van de huidige verkoop en de vereiste waarde retourneren.

OFFSET gebruiken

OFFSET is perfect voor de typische vergelijking met eerdere typen berekeningen die nodig zijn om de hierboven beschreven bedrijfsvraag te beantwoorden, omdat we hiermee een relatieve beweging kunnen uitvoeren. Onze eerste poging kan zijn:

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

Er gebeurt veel met deze expressie. We hebben ADDCOLUMNS gebruikt om de tabel uit te vouwen van vóór met een kolom met de naam PreviousColorSales. De inhoud van die kolom is ingesteld op CurrentYearSales( SUM(FactInternetSales[SalesAmount]), voor de vorige kleur (opgehaald met OFFSET).

Het resultaat is:

Color	CalendarYear	CurrentYearSales	PreviousColorSales
"Zwart"	2017	393885
"Zwart"	2018	1818835	393885
"Zwart"	2019	3981638	1818835
"Zwart"	2020	2644054	3981638
"Blauw"	2019	994448	2644054
"Blauw"	2020	1284648	994448
"Multi"	2019	48622	1284648
"Multi"	2020	57849	48622
"NA"	2019	207822	57849
"NA"	2020	227295	207822
"Rood"	2017	2961198	227295
"Rood"	2018	3686935	2961198
"Rood"	2019	900175	3686935
"Rood"	2020	176022	900175
"Zilver"	2017	326399	176022
"Zilver"	2018	750026	326399
"Zilver"	2019	2165176	750026
"Zilver"	2020	1871788	2165176
"Wit"	2019	2517	1871788
"Wit"	2020	2589	2517
"Geel"	2018	163071	2589
"Geel"	2019	2072083	163071
"Geel"	2020	2621602	2072083

Dit is een stap dichter bij ons doel, maar als we goed kijken, komt het niet precies overeen met wat we hebben. Voor [Silver, 2017] is de PreviousColorSales bijvoorbeeld ingesteld op [Rood, 2020].

ORDERBY toevoegen

De bovenstaande definitie is gelijk aan:

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 dit geval gebruikt de aanroep van OFFSET ORDERBY om de tabel op kleur en calendaryear in oplopende volgorde te orden, waarmee wordt bepaald wat wordt beschouwd als de vorige rij die wordt geretourneerd.

De reden waarom deze twee resultaten gelijkwaardig zijn, is omdat ORDERBY automatisch alle kolommen bevat uit de relatie die zich niet in PARTITIONBY bevindt. Omdat PARTITIONBY niet is opgegeven, is ORDERBY ingesteld op Color, CalendarYear en CurrentYearSales. Omdat de kleuren- en CalendarYear-paren in de relatie echter uniek zijn, verandert het resultaat niet door CurrentYearSales toe te voegen. Zelfs als we alleen Kleur in ORDERBY zouden opgeven, zijn de resultaten hetzelfde omdat CalendarYear automatisch wordt toegevoegd. Dit komt doordat de functie zoveel kolommen toevoegt als nodig is aan ORDERBY om ervoor te zorgen dat elke rij uniek kan worden geïdentificeerd door de kolommen ORDERBY en PARTITIONBY:

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]
        )
    )

PARTITIONBY toevoegen

Om bijna het resultaat te krijgen dat we na hebben, kunnen we PARTITIONBY gebruiken, zoals wordt weergegeven in de volgende berekende tabelexpressie:

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]
        )
    )

U ziet dat het opgeven van ORDERBY hier optioneel is, omdat ORDERBY automatisch alle kolommen van de relatie bevat die niet zijn opgegeven in PARTITIONBY. De volgende expressie retourneert dus dezelfde resultaten omdat ORDERBY automatisch is ingesteld op CalendarYear en CurrentYearSales:

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]
        )
    )

Notitie

Hoewel ORDERBY automatisch is ingesteld op CalendarYear en CurrentYearSales, wordt er geen garantie gegeven over de volgorde waarin ze worden toegevoegd. Als CurrentYearSales vóór CalendarYear wordt toegevoegd, is de resulterende order niet inline met wat er wordt verwacht. Wees expliciet wanneer u ORDERBY en PARTITIONBY opgeeft om verwarring en onverwachte resultaten te voorkomen.

Beide expressies retourneren het resultaat dat we hebben:

Color	CalendarYear	CurrentYearSales	PreviousYearSalesForSameColor
"Zwart"	2017	393885
"Zwart"	2018	1818835	393885
"Zwart"	2019	3981638	1818835
"Zwart"	2020	2644054	3981638
"Blauw"	2019	994448
"Blauw"	2020	1284648	994448
"Multi"	2019	48622
"Multi"	2020	57849	48622
"NA"	2019	207822
"NA"	2020	227295	207822
"Rood"	2017	2961198
"Rood"	2018	3686935	2961198
"Rood"	2019	900175	3686935
"Rood"	2020	176022	900175
"Zilver"	2017	326399
"Zilver"	2018	750026	326399
"Zilver"	2019	2165176	750026
"Zilver"	2020	1871788	2165176
"Wit"	2019	2517
"Wit"	2020	2589	2517
"Geel"	2018	163071
"Geel"	2019	2072083	163071
"Geel"	2020	2621602	2072083

Zoals u in deze tabel ziet, toont de kolom PreviousYearSalesForSameColor de verkoop voor het vorige jaar voor dezelfde kleur. Voor [Rood, 2020], wordt de verkoop voor [Rood, 2019] enzovoort geretourneerd. Als er geen vorig jaar is, bijvoorbeeld in het geval van [Rood, 2017], wordt er geen waarde geretourneerd.

U kunt PARTITIONBY beschouwen als een manier om de tabel te verdelen in delen waarin de OFFSET-berekening moet worden uitgevoerd. In het bovenstaande voorbeeld is de tabel onderverdeeld in zoveel delen als er kleuren zijn, één voor elke kleur. Vervolgens wordt binnen elk deel de VERSCHUIVING berekend, gesorteerd op CalendarYear.

Wat er visueel gebeurt, is dit:

Eerst resulteert de aanroep van PARTITIONBY in de tabel die wordt onderverdeeld in delen, één voor elke kleur. Dit wordt vertegenwoordigd door de lichtblauwe vakken in de tabelafbeelding. Vervolgens zorgt ORDERBY ervoor dat elk onderdeel wordt gesorteerd op CalendarYear (vertegenwoordigd door de oranje pijlen). Ten slotte zoekt OFFSET in elk gesorteerd deel voor elke rij de rij erboven en retourneert deze waarde in de kolom PreviousYearSalesForSameColor. Omdat voor elke eerste rij in elk deel geen vorige rij in hetzelfde deel is, is het resultaat in die rij voor de kolom PreviousYearSalesForSameColor leeg.

Om het uiteindelijke resultaat te bereiken, moeten we CurrentYearSales aftrekken van de omzet van het vorige jaar voor dezelfde kleur die door de aanroep naar OFFSET wordt geretourneerd. Omdat we niet geïnteresseerd zijn in het weergeven van de verkoop van het vorige jaar voor dezelfde kleur, maar alleen in de omzet van het huidige jaar en het verschil in jaar na jaar. Dit is de uiteindelijke expressie voor de berekende tabel:

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]
        )
    )

En dit is het resultaat van die expressie:

Color	CalendarYear	CurrentYearSales	YoYSalesForSameColor
"Zwart"	2017	393885	393885
"Zwart"	2018	1818835	1424950
"Zwart"	2019	3981638	2162803
"Zwart"	2020	2644054	-1337584
"Blauw"	2019	994448	994448
"Blauw"	2020	1284648	290200
"Multi"	2019	48622	48622
"Multi"	2020	57849	9227
"NA"	2019	207822	207822
"NA"	2020	227295	19473
"Rood"	2017	2961198	2961198
"Rood"	2018	3686935	725737
"Rood"	2019	900175	-2786760
"Rood"	2020	176022	-724153
"Zilver"	2017	326399	326399
"Zilver"	2018	750026	423627
"Zilver"	2019	2165176	1415150
"Zilver"	2020	1871788	-293388
"Wit"	2019	2517	2517
"Wit"	2020	2589	72
"Geel"	2018	163071	163071
"Geel"	2019	2072083	1909012
"Geel"	2020	2621602	549519

MATCHBY gebruiken

Mogelijk hebt u gemerkt dat we MATCHBY helemaal niet hebben opgegeven. In dit geval is het niet nodig. De kolommen in ORDERBY en PARTITIONBY (voor zover deze zijn opgegeven in de bovenstaande voorbeelden) zijn voldoende om elke rij uniek te identificeren. Omdat we MATCHBY niet hebben opgegeven, worden de kolommen die zijn opgegeven in ORDERBY en PARTITIONBY gebruikt om elke rij uniek te identificeren, zodat ze kunnen worden vergeleken om OFFSET in te schakelen om een zinvol resultaat te geven. Als de kolommen in ORDERBY en PARTITIONBY elke rij niet uniek kunnen identificeren, kunnen extra kolommen worden toegevoegd aan de ORDERBY-component als deze extra kolommen elke rij uniek kunnen identificeren. Als dat niet mogelijk is, wordt er een fout geretourneerd. In dit laatste geval kan het opgeven van MATCHBY helpen om de fout op te lossen.

Als MATCHBY is opgegeven, worden de kolommen in MATCHBY en PARTITIONBY gebruikt om elke rij uniek te identificeren. Als dat niet mogelijk is, wordt er een fout geretourneerd. Zelfs als MATCHBY niet is vereist, kunt u OVERWEGEN OM MATCHBY expliciet op te geven om verwarring te voorkomen.

Als u verdergaat met de bovenstaande voorbeelden, ziet u hier de laatste expressie:

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]
        )
    )

Als we expliciet willen zijn over hoe rijen uniek moeten worden geïdentificeerd, kunnen we MATCHBY opgeven, zoals wordt weergegeven in de volgende equivalente expressie:

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]
        )
    )

Omdat MATCHBY is opgegeven, worden zowel de kolommen die zijn opgegeven in MATCHBY als in PARTITIONBY gebruikt om rijen op unieke wijze te identificeren. Omdat Color is opgegeven in zowel MATCHBY als PARTITIONBY, is de volgende expressie gelijk aan de vorige expressie:

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]
        )
    )

Aangezien het opgeven van MATCHBY niet nodig is in de voorbeelden die we tot nu toe hebben bekeken, gaan we een iets ander voorbeeld bekijken waarvoor MATCHBY vereist is. In dit geval hebben we een lijst met orderregels. Elke rij vertegenwoordigt een orderregel voor een order. Een order kan meerdere orderregels bevatten en orderregel 1 wordt weergegeven op veel orders. Daarnaast hebben we voor elke orderregel een ProductKey en een SalesAmount. Een voorbeeld van de relevante kolommen in de tabel ziet er als volgt uit:

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

U ziet dat SalesOrderNumber en SalesOrderLineNumber beide vereist zijn om rijen uniek te identificeren.

Voor elke order willen we het vorige verkoopbedrag van hetzelfde product (vertegenwoordigd door de ProductKey) retourneren dat is besteld door SalesAmount in aflopende volgorde. De volgende expressie werkt niet omdat er mogelijk meerdere rijen in vRelation zijn, omdat deze wordt doorgegeven aan OFFSET:

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

Deze expressie retourneert een fout: 'OFFSET's Relation parameter may have duplicate rows, which is niet allowed.'

Als u deze expressie wilt laten werken, moet MATCHBY worden opgegeven en moeten alle kolommen bevatten die een rij uniek definiëren. MATCHBY is hier vereist omdat de relatie, FactInternetSales, geen expliciete sleutels of unieke kolommen bevat. De kolommen SalesOrderNumber en SalesOrderLineNumber vormen echter een samengestelde sleutel, waarbij hun bestaan uniek is in de relatie en daarom elke rij uniek kan identificeren. Het opgeven van SalesOrderNumber of SalesOrderLineNumber is niet voldoende omdat beide kolommen herhalende waarden bevatten. Met de volgende expressie wordt het probleem opgelost:

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

En deze expressie retourneert inderdaad de resultaten die we hebben:

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount	Vorige verkoopbedrag
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

Gerelateerde inhoud

ORDERBY
PARTITIONBY
MATCHBY
INDEX
OFFSET
VENSTER
RANG
RIJNUMMER