Om funktionerne ORDERBY, PARTITIONBYog MATCHBY

Funktionerne ORDERBY, PARTITIONBYog MATCHBY i DAX er specialfunktioner, der kun kan bruges sammen med funktionerne DAX Window: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Det er vigtigt at forstå ORDERBY, PARTITIONBYog MATCHBY for at kunne bruge funktionerne Vindue. De eksempler, der er angivet her, bruger OFFSET, men gælder også for de andre vinduesfunktioner.

Scenario

Lad os starte med et eksempel, der slet ikke bruger vinduesfunktioner. Vist nedenfor er en tabel, der returnerer det samlede salg pr. farve pr. kalenderår. Der er flere måder at definere denne tabel på, men da vi er interesseret i at forstå, hvad der sker i DAX, bruger vi en beregnet tabel. Her er tabeludtrykket:

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

Du kan se, at dette beregnede tabeludtryk bruger SUMMARIZECOLUMNS til at beregne SUM af kolonnen SalesAmount i tabellen FactInternetSales efter kolonnen Color fra tabellen DimProduct og kolonnen CalendarYear fra tabellen DimDate. Her er resultatet:

Farve	Kalenderår	CurrentYearSales
"Sort"	2017	393885
"Sort"	2018	1818835
"Sort"	2019	3981638
"Sort"	2020	2644054
"Blå"	2019	994448
"Blå"	2020	1284648
"Flere"	2019	48622
"Flere"	2020	57849
"NA"	2019	207822
"NA"	2020	227295
"Rød"	2017	2961198
"Rød"	2018	3686935
"Rød"	2019	900175
"Rød"	2020	176022
"Sølv"	2017	326399
"Sølv"	2018	750026
"Sølv"	2019	2165176
"Sølv"	2020	1871788
"Hvid"	2019	2517
"Hvid"	2020	2589
"Gul"	2018	163071
"Gul"	2019	2072083
"Gul"	2020	2621602

Lad os nu forestille os, at vi forsøger at løse det forretningsmæssige spørgsmål om at beregne forskellen i salg år for år for hver farve. Faktisk har vi brug for en måde at finde salg for samme farve i det foregående år og trække det fra salget i det aktuelle år, i kontekst. For kombinationen [Red, 2019] leder vi f.eks. efter salg for [Red, 2018]. Når vi har det, kan vi derefter trække det fra det aktuelle salg og returnere den påkrævede værdi.

Brug af OFFSET

OFFSET er perfekt til de typiske sammenligne med tidligere typer beregninger, der kræves for at besvare det forretningsspørgsmål, der er beskrevet ovenfor, da det giver os mulighed for at foretage en relativ bevægelse. Vores første forsøg kan være:

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

Der sker meget med dette udtryk. Vi brugte ADDCOLUMNS til at udvide tabellen fra før med en kolonne med navnet PreviousColorSales. Indholdet af kolonnen er angivet til CurrentYearSales, som er SUM(FactInternetSales[SalesAmount]) for den forrige Farve (hentet ved hjælp af OFFSET).

Resultatet er:

Farve	Kalenderår	CurrentYearSales	PreviousColorSales
"Sort"	2017	393885
"Sort"	2018	1818835	393885
"Sort"	2019	3981638	1818835
"Sort"	2020	2644054	3981638
"Blå"	2019	994448	2644054
"Blå"	2020	1284648	994448
"Flere"	2019	48622	1284648
"Flere"	2020	57849	48622
"NA"	2019	207822	57849
"NA"	2020	227295	207822
"Rød"	2017	2961198	227295
"Rød"	2018	3686935	2961198
"Rød"	2019	900175	3686935
"Rød"	2020	176022	900175
"Sølv"	2017	326399	176022
"Sølv"	2018	750026	326399
"Sølv"	2019	2165176	750026
"Sølv"	2020	1871788	2165176
"Hvid"	2019	2517	1871788
"Hvid"	2020	2589	2517
"Gul"	2018	163071	2589
"Gul"	2019	2072083	163071
"Gul"	2020	2621602	2072083

Dette er et skridt tættere på vores mål, men hvis vi ser nærmere på det, stemmer det ikke nøjagtigt overens med det, vi er ude efter. For [Silver, 2017] er PreviousColorSales f.eks. angivet til [Red, 2020].

Tilføjelse af ORDERBY

Ovenstående definition svarer til:

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

I dette tilfælde bruger kaldet til OFFSETORDERBY til at sortere tabellen efter Farve og CalendarYear i stigende rækkefølge, hvilket bestemmer, hvad der anses for at være den forrige række, der returneres.

Årsagen til, at disse to resultater svarer til hinanden, er, at ORDERBY automatisk indeholder alle kolonner fra relationen, der ikke findes i PARTITIONBY. Da PARTITIONBY ikke blev angivet, er ORDERBY angivet til Color, CalendarYear og CurrentYearSales. Men da parene Color og CalendarYear i relationen er entydige, ændrer tilføjelsen af CurrentYearSales ikke resultatet. Selvom vi kun skulle angive Farve i ORDERBY, er resultaterne faktisk de samme, da CalendarYear automatisk tilføjes. Det skyldes, at funktionen tilføjer lige så mange kolonner, der er nødvendige for at ORDERBY, for at sikre, at hver række kan identificeres entydigt af kolonnerne ORDERBY og 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]
        )
    )

Tilføjelse af PARTITIONBY

Hvis du nu vil næsten få det resultat, vi er ude efter, kan vi bruge PARTITIONBY, som vist i følgende beregnede tabeludtryk:

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

Bemærk, at det er valgfrit at angive ORDERBY her, fordi ORDERBY automatisk indeholder alle de kolonner fra relationen, der ikke er angivet i PARTITIONBY. Følgende udtryk returnerer derfor de samme resultater, fordi ORDERBY automatisk er angivet til CalendarYear og 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]
        )
    )

Seddel

Selvom ORDERBY er angivet til CalendarYear og CurrentYearSales automatisk, gives der ingen garanti for, hvilken rækkefølge de vil blive tilføjet i. Hvis CurrentYearSales tilføjes før CalendarYear, er den resulterende ordre ikke i overensstemmelse med det, der forventes. Vær eksplicit, når du angiver ORDERBY og PARTITIONBY for at undgå forvirring og uventede resultater.

Begge udtryk returnerer det resultat, vi er efter:

Farve	Kalenderår	CurrentYearSales	PreviousYearSalesForSameColor
"Sort"	2017	393885
"Sort"	2018	1818835	393885
"Sort"	2019	3981638	1818835
"Sort"	2020	2644054	3981638
"Blå"	2019	994448
"Blå"	2020	1284648	994448
"Flere"	2019	48622
"Flere"	2020	57849	48622
"NA"	2019	207822
"NA"	2020	227295	207822
"Rød"	2017	2961198
"Rød"	2018	3686935	2961198
"Rød"	2019	900175	3686935
"Rød"	2020	176022	900175
"Sølv"	2017	326399
"Sølv"	2018	750026	326399
"Sølv"	2019	2165176	750026
"Sølv"	2020	1871788	2165176
"Hvid"	2019	2517
"Hvid"	2020	2589	2517
"Gul"	2018	163071
"Gul"	2019	2072083	163071
"Gul"	2020	2621602	2072083

Som du kan se i denne tabel, viser kolonnen PreviousYearSalesForSameColor salget for det foregående år for den samme farve. For [Red, 2020] returnerer den salget for [Red, 2019] osv. Hvis der ikke er noget forrige år, f.eks. i tilfælde af [Red, 2017], returneres der ingen værdi.

Du kan tænke på PARTITIONBY som en måde at opdele tabellen i dele, hvor du kan udføre OFFSET beregningen. I eksemplet ovenfor er tabellen opdelt i lige så mange dele, som der er farver, én for hver farve. Derefter beregnes OFFSET i hver del sorteret efter CalendarYear.

Visuelt, hvad der sker, er dette:

Først medfører kaldet til PARTITIONBY, at tabellen opdeles i dele, én for hver Farve. Dette repræsenteres af de lyseblå felter på tabelbilledet. Derefter sørger ORDERBY for, at hver del sorteres efter CalendarYear (repræsenteret af de orange pile). Til sidst finder OFFSET rækken over hver sorteret del for hver række og returnerer denne værdi i kolonnen PreviousYearSalesForSameColor. Da der for hver første række i hver del ikke er nogen forrige række i den samme del, er resultatet i den pågældende række for kolonnen PreviousYearSalesForSameColor tom.

For at opnå det endelige resultat skal vi blot trække CurrentYearSales fra det forrige års salg for den samme farve, der returneres af kaldet til OFFSET. Da vi ikke er interesseret i at vise det forrige års salg for samme farve, men kun i det aktuelle års salg og forskellen år for år. Her er det endelige beregnede tabeludtryk:

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

Og her er resultatet af dette udtryk:

Farve	Kalenderår	CurrentYearSales	YoYSalesForSameColor
"Sort"	2017	393885	393885
"Sort"	2018	1818835	1424950
"Sort"	2019	3981638	2162803
"Sort"	2020	2644054	-1337584
"Blå"	2019	994448	994448
"Blå"	2020	1284648	290200
"Flere"	2019	48622	48622
"Flere"	2020	57849	9227
"NA"	2019	207822	207822
"NA"	2020	227295	19473
"Rød"	2017	2961198	2961198
"Rød"	2018	3686935	725737
"Rød"	2019	900175	-2786760
"Rød"	2020	176022	-724153
"Sølv"	2017	326399	326399
"Sølv"	2018	750026	423627
"Sølv"	2019	2165176	1415150
"Sølv"	2020	1871788	-293388
"Hvid"	2019	2517	2517
"Hvid"	2020	2589	72
"Gul"	2018	163071	163071
"Gul"	2019	2072083	1909012
"Gul"	2020	2621602	549519

Brug af MATCHBY

Du har måske bemærket, at vi slet ikke har angivet MATCHBY. I dette tilfælde er det ikke nødvendigt. Kolonnerne i ORDERBY og PARTITIONBY (for så vidt de er angivet i ovenstående eksempler) er tilstrækkelige til entydigt at identificere hver række. Da vi ikke har angivet MATCHBY, bruges de kolonner, der er angivet i ORDERBY og PARTITIONBY, til entydigt at identificere hver række, så de kan sammenlignes, så OFFSET giver et meningsfuldt resultat. Hvis kolonnerne i ORDERBY og PARTITIONBY ikke entydigt kan identificere hver række, kan der føjes flere kolonner til delsætningen ORDERBY, hvis disse ekstra kolonner gør det muligt at identificere hver række entydigt. Hvis det ikke er muligt, returneres der en fejl. I dette sidste tilfælde kan angivelse af MATCHBY hjælpe med at løse fejlen.

Hvis MATCHBY er angivet, bruges kolonnerne i MATCHBY og PARTITIONBY til entydigt at identificere hver række. Hvis det ikke er muligt, returneres der en fejl. Selvom MATCHBY ikke er påkrævet, kan du overveje eksplicit at angive MATCHBY for at undgå forvirring.

Hvis du fortsætter fra eksemplerne ovenfor, er dette det sidste udtryk:

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

Hvis vi vil være eksplicitte om, hvordan rækker skal identificeres entydigt, kan vi angive MATCHBY som vist i følgende tilsvarende udtryk:

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 er angivet, bruges både de kolonner, der er angivet i MATCHBY, samt i PARTITIONBY til entydigt at identificere rækker. Da Color er angivet i både MATCHBY og PARTITIONBY, svarer følgende udtryk til det forrige udtryk:

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 det ikke er nødvendigt at angive MATCHBY i de eksempler, vi har kigget på indtil videre, kan vi se på et lidt andet eksempel, der kræver MATCHBY. I dette tilfælde har vi en liste over ordrelinjer. Hver række repræsenterer en ordrelinje for en ordre. En ordre kan have flere ordrelinjer, og ordrelinje 1 vises på mange ordrer. Derudover har vi for hver ordrelinje en ProductKey og en SalesAmount. Et eksempel på de relevante kolonner i tabellen ser sådan ud:

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

Bemærk, at Både SalesOrderNumber og SalesOrderLineNumber er nødvendige for entydigt at identificere rækker.

For hver ordre vil vi returnere det tidligere salgsbeløb for det samme produkt (repræsenteret af ProductKey), der er bestilt af SalesAmount i faldende rækkefølge. Følgende udtryk fungerer ikke, fordi der muligvis er flere rækker i vRelation, da det overføres til OFFSET:

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

Dette udtryk returnerer en fejl: "OFFSET's relationsparameter kan have dublerede rækker, hvilket ikke er tilladt."

Hvis dette udtryk skal fungere, skal MATCHBY angives og skal indeholde alle kolonner, der entydigt definerer en række. MATCHBY er påkrævet her, fordi relationen FactInternetSales ikke indeholder eksplicitte nøgler eller entydige kolonner. Kolonnerne SalesOrderNumber og SalesOrderLineNumber tilsammen udgør dog en sammensat nøgle, hvor deres eksistens sammen er entydig i relationen og derfor entydigt kan identificere hver række. Det er ikke nok blot at angive SalesOrderNumber eller SalesOrderLineNumber, da begge kolonner indeholder gentagne værdier. Følgende udtryk løser problemet:

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

Og dette udtryk returnerer faktisk de resultater, vi er ude efter:

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount	Tidligere salgsbeløb
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

Relateret indhold

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER