Förstå funktionerna ORDERBY, PARTITIONBY, andMATCHBY

Funktionerna ORDERBY, PARTITIONBY, andMATCHBY i DAX är specialfunktioner som bara kan användas tillsammans med DAXWindow funktioner: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Att förstå ORDERBY, PARTITIONBY, andMATCHBY är viktigt för att kunna använda Window-funktionerna. Exemplen här använder OFFSET, men gäller på samma sätt för andra Window funktioner.

Scenario

Låt oss börja med ett exempel som inte använder Window funktioner på all. Nedan visas en tabell som returnerar total försäljning, per färg, per calendaryear. Det finns flera sätt att define den här tabellen, men eftersom vi vill förstå vad som händer i DAXanvänder vi en kalkylerad tabell. Här är tabelluttrycket:

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

Du ser att det här beräknade tabelluttrycket använder SUMMARIZECOLUMNS för att calculateSUM för kolumnen SalesAmount i tabellen FactInternetSales, efter kolumnen Färg från tabellen DimProduct, and kolumnen CalendarYear från tabellen DimDate. Här är resultatet:

Now, låt oss föreställa oss att vi försöker lösa affärsfrågan om att beräkna skillnaden i försäljning, year- över -year för varje färg. I själva verket behöver vi hitta ett sätt att subtrahera find försäljning för samma färg i previousyearand från försäljningen i den aktuella yeari sammanhanget. För kombinationen [Red, 2019] letar vi till exempel efter försäljning för [Red, 2018]. När vi har det kan vi sedan subtrahera det från den aktuella försäljningen and och returnera den nödvändiga value.

Använda OFFSET

OFFSET är perfekt för de typiska -previous--beräkningar som krävs för att besvara affärsfrågan som beskrivs ovan, eftersom det tillåter oss att göra en relativ rörelse. Vårt first-försök kan vara:

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

Mycket händer med det här uttrycket. Vi använde ADDCOLUMNS till expand den tidigare tabellen med en kolumn som heter PreviousColorSales. Innehållet i kolumnen är inställt på CurrentYearSales, som är SUM(FactInternetSales[SalesAmount]), för färgen previous (hämtas med OFFSET).

Resultatet är:

Detta är ett steg närmare vårt mål, men if vi tittar noga matchar det inte exakt vad vi är efter. Till exempel för [Silver, 2017] är PreviousColorSales inställt på [Red, 2020].

Lägga till ORDERBY

Definitionen ovan motsvarar:

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 det här fallet använder anropet till OFFSETORDERBY för att sortera tabellen efter Color and CalendarYear i stigande ordning, vilket fastställer vilken som anses vara den previous rad som returneras.

Anledningen till att dessa två resultat är likvärdiga är att ORDERBY automatiskt containsall kolumner från relationen som inte finns i PARTITIONBY. Eftersom PARTITIONBY inte har specificerats, är ORDERBY satt till Color, CalendarYear, and CurrentYearSales. Men eftersom paren Color and CalendarYear i relationen är unika ändrar inte resultatet att lägga till CurrentYearSales. I fact, evenif skulle vi bara ange Färg i ORDERBY, blev resultatet detsamma eftersom CalendarYear skulle automatiskt läggas till. Det beror på att funktionen lägger till så många kolumner som behövs till ORDERBY för att säkerställa att varje rad kan identifieras unikt av ORDERBYandPARTITIONBY kolumnerna.

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

Lägga till PARTITIONBY

Now, för att nästan få det resultat vi är efter kan vi använda PARTITIONBY, enligt följande beräknade tabelluttryck:

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

Observera att det är valfritt att ange ORDERBY här eftersom ORDERBY automatiskt containsall kolumnerna från relationen som inte anges i PARTITIONBY. Följande uttryck returnerar alltså samma resultat eftersom ORDERBY anges till CalendarYear and CurrentYearSales automatiskt:

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

Båda uttrycken returnerar det resultat vi är efter:

Som du ser i den här tabellen visar kolumnen PreviousYearSalesForSameColor försäljningen för previousyear för samma färg. För [Red, 2020] returnerar den försäljningen för [Red, 2019], and och så vidare. If det finns ingen previousyear, till exempel när det gäller [Red, 2017], returneras inga value.

Du kan se PARTITIONBY som ett sätt att divide tabellen i delar där du kan köra OFFSET-beräkningen. I exemplet ovan är tabellen uppdelad i så många delar som det finns färger, en för varje färg. Sedan beräknas OFFSET inom varje del och sorteras efter CalendarYear.

Visuellt är det här:

Firstresulterar anropet till PARTITIONBY i att tabellen blir uppdelad i sektioner, en för varje färg. Detta representeras av de ljusblå rutorna i tabellbilden. Nextkontrollerar ORDERBY att varje del sorteras efter CalendarYear (representeras av de orangea pilarna). Slutligen, inom varje sorterad del, för varje rad, hittar OFFSET raden ovanför den and returnerar den value i kolumnen PreviousYearSalesForSameColor. Eftersom det för varje first rad i varje del inte finns någon previous rad i samma del är resultatet i raden för kolumnen PreviousYearSalesForSameColor tomt.

För att uppnå det slutliga resultatet behöver vi bara dra av CurrentYearSales från försäljningen för previousyear för samma färg som returneras av anropet till OFFSET. Eftersom vi är not intresserade av att visa previousyear försäljning för samma färg, men bara i den aktuella year försäljning andyear över year skillnad. Här är det slutliga beräknade tabelluttrycket:

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 här är resultatet av uttrycket:

Använda MATCHBY

Du kanske har märkt att vi inte angav MATCHBY på all. I det här fallet är det inte nödvändigt. Kolumnerna i ORDERBYandPARTITIONBY (i den mån de angavs i exemplen ovan) räcker för att unikt identifiera varje rad. Eftersom vi inte angav MATCHBYanvänds de kolumner som anges i ORDERBYandPARTITIONBY för att unikt identifiera varje rad så att de kan jämföras för att aktivera OFFSET för att ge ett meningsfullt resultat. If kolumnerna i ORDERBYandPARTITIONBY inte kan identifiera varje rad unikt kan ytterligare kolumner läggas till i ORDERBY-satsen if dessa extra kolumner gör att varje rad kan identifieras unikt. If om det är not möjligt returneras en error. I det här last fallet kan det hjälpa att ange MATCHBY för att lösa error.

If MATCHBY anges används kolumnerna i MATCHBYandPARTITIONBY för att unikt identifiera varje rad. If om det är not möjligt, returneras en error. Even if MATCHBY är inte nödvändigt, så du bör uttryckligen ange MATCHBY för att undvika förvirring.

Fortsätt från exemplen ovan, här är last-uttrycket:

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 vill vara tydliga med hur rader ska identifieras unikt kan vi ange MATCHBY som visas i följande motsvarande uttryck:

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

Eftersom MATCHBY har angetts används både de kolumner som anges i MATCHBY och i PARTITIONBY för att unikt identifiera rader. Eftersom Color anges i både MATCHBY,andochPARTITIONBY, motsvarar följande uttryck uttrycket 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]
        )
    )

Eftersom det inte är nödvändigt att ange MATCHBY i de exempel som vi har tittat på hittills ska vi titta på ett lite annorlunda exempel som kräver MATCHBY. I det här fallet har vi en lista över orderrader. Varje rad representerar en orderrad för en order. En order kan ha flera orderrader and orderrad 1 visas på många beställningar. Dessutom har vi för varje orderrad en Produktnyckel and och ett Försäljningsbelopp. En sample av relevanta kolumner i tabellen ser ut så här:

Observera att SalesOrderNumber and SalesOrderLineNumber krävs för att unikt identifiera rader.

För varje order vill vi returnera previous försäljningsbeloppet för samma product (som representeras av ProductKey) sorterade efter SalesAmount i fallande ordning. Följande uttryck fungerar inte eftersom det potentiellt finns flera rader i vRelation när det skickas till OFFSET:

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

Det här uttrycket returnerar ett error: "OFFSETrelationsparameter kan ha dubbletter av rader, vilket inte är tillåtet."

För att det här uttrycket ska fungera måste MATCHBY anges and måste innehålla all kolumner som unikt define en rad. MATCHBY krävs här eftersom relationen FactInternetSales inte innehåller några explicita nycklar or unika kolumner. Kolumnerna SalesOrderNumber and och SalesOrderLineNumber bildar dock tillsammans en sammansatt nyckel, där deras existens tillsammans är unik i relationen and och därför kan dessa unikt identifiera varje rad. Det räcker inte att bara ange SalesOrderNumber or SalesOrderLineNumber eftersom båda kolumnerna innehåller upprepande values. Följande uttryck 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 det här uttrycket returnerar verkligen de resultat vi är efter:

Related innehåll

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER