Om funktionerne ORDERBY, PARTITIONBY, andMATCHBY

Funktionerne ORDERBY, PARTITIONBY, andMATCHBY i DAX er specialfunktioner, der kun kan bruges sammen med DAXWindow funktioner: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Det er vigtigt at forstå ORDERBY, PARTITIONBYandMATCHBY for at kunne bruge funktionerne til Window. De eksempler, der er angivet her, bruger OFFSET, men gælder på samme måde for de andre Window funktioner.

Scenario

Lad os starte med et eksempel, der ikke bruger Window funktioner på all. Vist nedenfor er en tabel, der returnerer det samlede salg pr. farve pr. calendaryear. Der er flere måder at define 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 calculateSUM af kolonnen SalesAmount i tabellen FactInternetSales af kolonnen Color fra tabellen DimProduct and 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

Nowkan vi forestille os, at vi forsøger at løse det forretningsmæssige spørgsmål om at beregne forskellen i salg, year-over-year for hver farve. Faktisk har vi brug for en måde at find salg for den samme farve i previousyearand trække det fra salget i den aktuelle yeari 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 and returnere de påkrævede value.

Brug af OFFSET

OFFSET er perfekt til de typiske sammenligne med previous 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 first 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 expand tabellen fra før med en kolonne med navnet PreviousColorSales. Indholdet af kolonnen er angivet til CurrentYearSales, som er SUM(FactInternetSales[SalesAmount]) for den previous Color (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 if ser vi nærmere på det, stemmer ikke 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 Color and CalendarYear i stigende rækkefølge, hvilket bestemmer, hvad der anses for at være den previous række, der returneres.

Årsagen til, at disse to resultater svarer til hinanden, er, at ORDERBY automatisk containsall kolonner fra relationen, der ikke findes i PARTITIONBY. Da PARTITIONBY ikke blev angivet, er ORDERBY angivet til Color, CalendarYear and CurrentYearSales. Men da color-and CalendarYear-par i relationen er entydige, ændrer tilføjelsen af CurrentYearSales ikke resultatet. I factevenif vi kun skulle angive Farve i ORDERBY, er resultaterne 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 de ORDERBYandPARTITIONBY kolonner:

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

Nowfor at 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 containsall kolonnerne fra den relation, der ikke er angivet i PARTITIONBY. Følgende udtryk returnerer derfor de samme resultater, fordi ORDERBY automatisk er angivet til CalendarYear and 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 and CurrentYearSales automatisk, gives der ingen garanti for, hvilken rækkefølge de tilføjes i. If CurrentYearSales tilføjes før CalendarYear, er den resulterende ordre ikke i overensstemmelse med det forventede. Vær eksplicit, når du angiver ORDERBYandPARTITIONBY for at undgå forvirring and 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 previousyear for den samme farve. For [Red, 2020] returnerer den salget for [Red, 2019], and osv. If der ikke er nogen previousyear, f.eks. i tilfælde af [Red, 2017], returneres der ingen value.

Du kan tænke på PARTITIONBY som en måde at divide tabellen i dele, hvor du kan udføre den OFFSET beregning. 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:

Firstmedfører kaldet til PARTITIONBY, at tabellen opdeles i dele, én for hver Farve. Dette repræsenteres af de lyseblå felter på tabelbilledet. Nextsø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 and returnerer den value i kolonnen PreviousYearSalesForSameColor. Da der for hver first række i hver del ikke er nogen previous række i den samme del, er resultatet af den pågældende række for kolonnen PreviousYearSalesForSameColor tom.

For at opnå det endelige resultat skal vi blot trække CurrentYearSales fra det previousyear salg for den samme farve, der returneres af kaldet til OFFSET. Da vi not interesseret i at vise previousyear salg for samme farve, men kun i det aktuelle year salg, andyear over year forskel. 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]
        )
    )

And 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 ikke har angivet MATCHBY på all. I dette tilfælde er det ikke nødvendigt. Kolonnerne i ORDERBYandPARTITIONBY (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 ORDERBYandPARTITIONBY, til entydigt at identificere hver række, så de kan sammenlignes for at gøre det muligt for OFFSET at give et meningsfuldt resultat. If kolonnerne i ORDERBYandPARTITIONBY ikke entydigt kan identificere hver række, kan der føjes flere kolonner til ORDERBY-delsætningen, if de ekstra kolonner gør det muligt at identificere hver række entydigt. If der er not muligt, returneres der en error. I dette last tilfælde kan angivelse af MATCHBY hjælpe med at løse error.

If MATCHBY er angivet, bruges kolonnerne i MATCHBYandPARTITIONBY til entydigt at identificere hver række. If der er not muligt, returneres der en error. Even if MATCHBY er ikke påkrævet, kan du overveje eksplicit at angive MATCHBY for at undgå forvirring.

Hvis du fortsætter fra eksemplerne ovenfor, er her det last 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]
        )
    )

If 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 begge MATCHBYandPARTITIONBY, svarer følgende udtryk til udtrykket previous:

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 and ordrelinje 1 vises på mange ordrer. Derudover har vi for hver ordrelinje en ProductKey-and en SalesAmount. En sample af 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 SalesOrderNumber and SalesOrderLineNumber begge er nødvendige for entydigt at identificere rækker.

For hver ordre vil vi returnere det previous salgsbeløb for samme product (repræsenteret af ProductKey), der er sorteret efter 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 error: "OFFSET's relationsparameter kan have dublerede rækker, hvilket ikke er tilladt."

Hvis dette udtryk skal fungere, skal MATCHBY angives, and skal indeholde all kolonner, der entydigt define en række. MATCHBY er påkrævet her, fordi relationen FactInternetSales ikke indeholder nogen eksplicitte nøgler or entydige kolonner. Kolonnerne SalesOrderNumber and SalesOrderLineNumber udgør dog en sammensat nøgle, hvor deres eksistens sammen er entydig i relationen and derfor entydigt kan identificere hver række. Det er ikke nok at angive SalesOrderNumber or SalesOrderLineNumber, da begge kolonner indeholder gentagne values. 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]
            )
    )

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

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

Related indhold

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER