Forstå ORDERBY, PARTITIONBY, andMATCHBY funksjoner

Funksjonene ORDERBY, PARTITIONBY, andMATCHBY i DAX er spesielle funksjoner som bare kan brukes sammen med DAXWindow funksjoner: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Å forstå ORDERBY, PARTITIONBY, andMATCHBY er avgjørende for å kunne bruke Window-funksjonene. Eksemplene som er angitt her, bruker OFFSET, men gjelder på samme måte som de andre Window funksjonene.

Scenario

La oss starte med et eksempel som ikke bruker Window funksjoner på all. Vist nedenfor er en tabell som returnerer totalt salg, per farge, per calendaryear. Det finnes flere måter å define denne tabellen på, men siden vi er interessert i å forstå hva som skjer i DAX, bruker vi en beregnet tabell. Her er tabelluttrykket:

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

Du ser at dette beregnede tabelluttrykket bruker SUMMARIZECOLUMNS til å calculateSUM i SalesAmount-kolonnen i FactInternetSales-tabellen, etter Farge-kolonnen fra DimProduct-tabellen, and CalendarYear-kolonnen fra DimDate-tabellen. Her er resultatet:

Now, la oss forestille oss at vi prøver å løse forretningsspørsmålet om å beregne forskjellen i salg, year-over-year for hver farge. Effektivt trenger vi en måte å find salg for samme farge i previousyearand trekke det fra salget i gjeldende year, i kontekst. For kombinasjonen [Rød, 2019] ser vi for eksempel etter salg for [Rød, 2018]. Når vi har det, kan vi deretter trekke det fra gjeldende salg and returnere den nødvendige value.

Bruke OFFSET

OFFSET er perfekt for den typiske sammenligne med previous typer beregninger som kreves for å svare på forretningsspørsmålet beskrevet ovenfor, da det tillater oss å gjøre en relativ bevegelse. Vårt first forsøk 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]
        )
    )

Mye skjer med dette uttrykket. Vi brukte ADDCOLUMNS til å expand tabellen fra før med en kolonne kalt PreviousColorSales. Innholdet i denne kolonnen er satt til CurrentYearSales, som er SUM(FactInternetSales[SalesAmount]), for previous Farge (hentet ved hjelp av OFFSET).

Resultatet er:

Dette er ett skritt nærmere målet vårt, men if vi ser nøye på, samsvarer det ikke nøyaktig med det vi er ute etter. For [Silver, 2017] er for eksempel PreviousColorSales satt til [Rød, 2020].

Legge til ORDERBY

Denne definisjonen ovenfor tilsvarer:

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 tilfellet bruker kallet til OFFSETORDERBY til å bestille tabellen etter Color and CalendarYear i stigende rekkefølge, som bestemmer hva som regnes som den previous raden som returneres.

Årsaken til at disse to resultatene tilsvarer, skyldes at ORDERBY automatisk containsall kolonner fra relasjonen som ikke er i PARTITIONBY. Siden PARTITIONBY ikke ble angitt, er ORDERBY satt til Color, CalendarYear, and CurrentYearSales. Men siden Color and CalendarYear-parene i relasjonen er unike, endrer ikke det å legge til CurrentYearSales resultatet. I factevenif vi bare skulle angi Farge i ORDERBY, er resultatene de samme siden CalendarYear legges til automatisk. Dette er fordi funksjonen vil legge til så mange kolonner som nødvendig for å ORDERBY for å sikre at hver rad kan identifiseres unikt av de ORDERBYandPARTITIONBY kolonnene:

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

Legge til PARTITIONBY

Now, for å nesten få resultatet vi er ute etter, kan vi bruke PARTITIONBY, som vist i følgende beregnede tabelluttrykk:

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

Legg merke til at det er valgfritt å angi ORDERBY her fordi ORDERBY automatisk containsall kolonnene fra relasjonen som ikke er angitt i PARTITIONBY. Så følgende uttrykk returnerer de samme resultatene fordi ORDERBY er satt til CalendarYear and CurrentYearSales automatisk:

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

Begge uttrykkene returnerer resultatet vi er ute etter:

Som du ser i denne tabellen, viser Kolonnen PreviousYearSalesForSameColor salg for previousyear for samme farge. For [Rød, 2020], returnerer den salget for [Rød, 2019], and så videre. If det ikke finnes noen previousyear, for eksempel når det gjelder [Rød, 2017], returneres ingen value.

Du kan tenke på PARTITIONBY som en måte å divide tabellen i deler der du kan utføre OFFSET beregningen. I eksemplet ovenfor er tabellen delt inn i så mange deler som det finnes farger, én for hver farge. Deretter beregnes OFFSET i hver del, sortert etter CalendarYear.

Visuelt er det som skjer dette:

Firstfører kallet til PARTITIONBY til at tabellen deles inn i deler, én for hver farge. Dette representeres av de lyseblå boksene i tabellbildet. Nextsørger ORDERBY for at hver del er sortert etter CalendarYear (representert av de oransje pilene). Til slutt finner OFFSET raden over hver sortert del, and returnerer den value i Kolonnen PreviousYearSalesForSameColor. Siden for hver first rad i hver del er det ingen previous rad i den samme delen, er resultatet i raden for PreviousYearSalesForSameColor-kolonnen tom.

For å oppnå det endelige resultatet må vi ganske enkelt trekke fra CurrentYearSales fra previousyear salg for samme farge som ble returnert av kallet til OFFSET. Siden vi er not interessert i å vise previousyear salg for samme farge, men bare i gjeldende year salg andyear over year forskjell. Her er det endelige beregnede tabelluttrykket:

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 av uttrykket:

Bruke MATCHBY

Du har kanskje lagt merke til at vi ikke spesifiserte MATCHBY på all. I dette tilfellet er det ikke nødvendig. Kolonnene i ORDERBYandPARTITIONBY (så langt de ble angitt i eksemplene ovenfor) er tilstrekkelige til å identifisere hver rad unikt. Siden vi ikke angir MATCHBY, brukes kolonnene som er angitt i ORDERBYandPARTITIONBY til å identifisere hver rad unikt, slik at de kan sammenlignes for å aktivere OFFSET for å gi et meningsfullt resultat. If kolonnene i ORDERBYandPARTITIONBY ikke unikt kan identifisere hver rad, kan du legge til flere kolonner i ORDERBY setningsdelen if disse ekstra kolonnene tillater at hver rad identifiseres unikt. If det er not mulig, returneres en error. I dette last tilfellet kan det å angi MATCHBY bidra til å løse error.

If MATCHBY er angitt, brukes kolonnene i MATCHBYandPARTITIONBY til å identifisere hver rad unikt. If det er not mulig, returneres en error. Even if MATCHBY ikke er nødvendig, bør du vurdere å eksplisitt angi MATCHBY for å unngå forvirring.

Hvis du fortsetter fra eksemplene ovenfor, kan du se uttrykket last:

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 ønsker å være eksplisitte om hvordan rader skal identifiseres unikt, kan vi angi MATCHBY som vist i følgende tilsvarende uttrykk:

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

Siden MATCHBY er angitt, brukes både kolonnene som er angitt i MATCHBY og i PARTITIONBY til å identifisere rader unikt. Siden farge er angitt i begge MATCHBYandPARTITIONBY, tilsvarer følgende uttrykk previous-uttrykket:

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

Siden det ikke er nødvendig å angi MATCHBY i eksemplene vi har sett på så langt, kan vi se på et litt annet eksempel som krever MATCHBY. I dette tilfellet har vi en liste over ordrelinjer. Hver rad representerer en ordrelinje for en ordre. En ordre kan ha flere ordrelinjer and ordrelinje 1 vises på mange ordrer. For hver ordrelinje har vi i tillegg en ProductKey-and en SalesAmount. En sample av de relevante kolonnene i tabellen ser slik ut:

Legg merke til SalesOrderNumber and SalesOrderLineNumber kreves begge for å identifisere rader unikt.

For hver ordre ønsker vi å returnere det previous salgsbeløpet på samme product (representert av ProductKey) bestilt av SalesAmount i synkende rekkefølge. Følgende uttrykk fungerer ikke fordi det er potensielt flere rader i vRelation når det sendes inn i OFFSET:

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

Dette uttrykket returnerer en error: "OFFSETrelasjonsparameteren kan ha dupliserte rader, som ikke er tillatt."

Hvis du vil at dette uttrykket skal fungere, må MATCHBY angis and må inneholde all kolonner som unikt define en rad. MATCHBY kreves her fordi relasjonen FactInternetSales ikke inneholder noen eksplisitte nøkler or unike kolonner. Kolonnene SalesOrderNumber and SalesOrderLineNumber utgjør imidlertid en sammensatt nøkkel, der deres eksistens sammen er unik i relasjonen and kan derfor unikt identifisere hver rad. Bare angi SalesOrderNumber or SalesOrderLineNumber er ikke nok, da begge kolonnene inneholder gjentatte values. Følgende uttrykk 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 dette uttrykket returnerer faktisk resultatene vi er ute etter:

Related innhold

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER