Begrip van functies ORDERBY, PARTITIONBYen MATCHBY

De functies ORDERBY, PARTITIONBYen MATCHBY in DAX zijn speciale functies die alleen samen met DAX Vensterfuncties kunnen worden gebruikt: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Inzicht in ORDERBY, PARTITIONBYen 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 willen weten wat er in DAXgebeurt, gebruiken we een berekende tabel. Dit is de tabeluitdrukking:

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

Deze berekende tabelexpressie gebruikt SUMMARIZECOLUMNS om de SUM 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:

Kleur	Kalenderjaar	VerkoopVanHetHuidigeJaar
"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 vergelijken 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 eerdere tabel uit te breiden met een kolom genaamd PreviousColorSales. De inhoud van deze kolom is namelijk ingesteld op 'CurrentYearSales', wat overeenkomt met SUM(FactInternetSales[SalesAmount]), voor de vorige kleur (opgehaald met behulp van OFFSET).

Het resultaat is:

Kleur	Kalenderjaar	VerkoopVanHetHuidigeJaar	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 OFFSETORDERBY om de tabel op Colour en CalendarYear in oplopende volgorde te ordenen, wat bepaalt welke rij wordt beschouwd als de vorige die wordt geretourneerd.

De reden waarom deze twee resultaten gelijkwaardig zijn, is omdat ORDERBY automatisch alle kolommen uit de relatie bevat die zich niet in PARTITIONBYbevinden. 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 ORDERBYzouden opgeven, zijn de resultaten hetzelfde omdat CalendarYear automatisch zou worden toegevoegd. Dit komt doordat de functie zoveel kolommen aan ORDERBY toevoegt als nodig is 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

Nu, om bijna het gewenste resultaat te krijgen, kunnen we PARTITIONBYgebruiken, zoals 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 uit 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. Expliciet zijn bij het opgeven van ORDERBY en PARTITIONBY om verwarring en onverwachte resultatente voorkomen.

Beide uitdrukkingen leveren het gewenste resultaat op.

Kleur	Kalenderjaar	VerkoopVanHetHuidigeJaar	VerkoopVorigeJaarVoorZelfdeKleur
"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] worden de verkoopgegevens voor [Rood, 2019] en dergelijke gegeven. 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 in elk gedeelte de OFFSET berekend, gesorteerd op CalendarYear.

Wat er visueel gebeurt, is dit:

Eerst leidt de aanroep van PARTITIONBY ertoe dat de tabel wordt opgedeeld 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 vindt OFFSET in elk gesorteerd deel 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 gewoon aftrekken van de omzet van het vorige jaar voor dezelfde kleur die via de oproep naar OFFSETwordt teruggegeven. 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:

Kleur	Kalenderjaar	VerkoopVanHetHuidigeJaar	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 helemaal geen MATCHBY 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 MATCHBYniet 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 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 vereist is, kunt u overwegen om expliciet MATCHBY 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]
        )
    )

Aangezien MATCHBY is opgegeven, worden zowel de kolommen die zijn opgegeven in MATCHBY als in PARTITIONBY gebruikt om rijen uniek te identificeren. Omdat kleur 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 MATCHBYvereist is. In dit geval hebben we een lijst met orderregels. Elke rij vertegenwoordigt een orderregel voor een bestelling. 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:

Verkoopordernummer	Verkooporderregelnummer	ProductKey	Verkoopbedrag
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: 'De parameter vanOFFSET's Relatie kan dubbele rijen bevatten, wat niet is toegestaan.'

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 inderdaad retourneert deze uitdrukking de resultaten waar we naar op zoek zijn.

Verkoopordernummer	Verkooporderregelnummer	ProductKey	Verkoopbedrag	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

Verwante inhoud

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER