Förstå funktionerna ORDERBY, PARTITIONBY och MATCHBY

Funktionerna ORDERBY, PARTITIONBY och MATCHBY i DAX är specialfunktioner som bara kan användas tillsammans med DAX-fönsterfunktioner: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

Att förstå ORDERBY, PARTITIONBY och MATCHBY är viktigt för att använda funktionerna Window. Exemplen som anges här använder OFFSET, men gäller på samma sätt för de andra Window-funktionerna.

Scenario

Låt oss börja med ett exempel som inte använder fönsterfunktioner alls. Nedan visas en tabell som returnerar total försäljning, per färg och kalenderår. Det finns flera sätt att definiera den här tabellen, men eftersom vi är intresserade av att förstå vad som händer i DAX använder vi en beräknad 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 beräkna SUM för kolumnen SalesAmount i tabellen FactInternetSales, med kolumnen Färg från tabellen DimProduct och kolumnen CalendarYear från tabellen DimDate. Här är resultatet:

Färg	CalendarYear	CurrentYearSales
"Svart"	2017	393885
"Svart"	2018	1818835
"Svart"	2019	3981638
"Svart"	2020	2644054
"Blå"	2019	994448
"Blå"	2020	1284648
"Flera"	2019	48622
"Flera"	2020	57849
"NA"	2019	207822
"NA"	2020	227295
"Röd"	2017	2961198
"Röd"	2018	3686935
"Röd"	2019	900175
"Röd"	2020	176022
"Silver"	2017	326399
"Silver"	2018	750026
"Silver"	2019	2165176
"Silver"	2020	1871788
"Vit"	2019	2517
"Vit"	2020	2589
"Gul"	2018	163071
"Gul"	2019	2072083
"Gul"	2020	2621602

Nu ska vi anta att vi försöker lösa affärsfrågan om att beräkna skillnaden i försäljning, år för år för varje färg. I själva verket behöver vi ett sätt att hitta försäljning för samma färg föregående år och subtrahera den från försäljningen under innevarande år, i kontext. 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 och returnera det nödvändiga värdet.

Använda OFFSET

OFFSET är perfekt för den typiska jämförelsen med tidigare typer av beräkningar som krävs för att besvara affärsfrågan som beskrivs ovan, eftersom det gör att vi kan göra en relativ rörelse. Vårt första 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 för att expandera tabellen från tidigare med en kolumn med namnet PreviousColorSales. Innehållet i kolumnen är inställt på CurrentYearSales, som är SUM(FactInternetSales[SalesAmount]), för den tidigare färgen (hämtad med OFFSET).

Resultatet är:

Färg	CalendarYear	CurrentYearSales	PreviousColorSales
"Svart"	2017	393885
"Svart"	2018	1818835	393885
"Svart"	2019	3981638	1818835
"Svart"	2020	2644054	3981638
"Blå"	2019	994448	2644054
"Blå"	2020	1284648	994448
"Flera"	2019	48622	1284648
"Flera"	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
"Silver"	2017	326399	176022
"Silver"	2018	750026	326399
"Silver"	2019	2165176	750026
"Silver"	2020	1871788	2165176
"Vit"	2019	2517	1871788
"Vit"	2020	2589	2517
"Gul"	2018	163071	2589
"Gul"	2019	2072083	163071
"Gul"	2020	2621602	2072083

Det här är ett steg närmare vårt mål, men om 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 OFFSET ORDERBY för att sortera tabellen efter Färg och CalendarYear i stigande ordning, vilket avgör vad som anses vara den föregående raden som returneras.

Anledningen till att dessa två resultat är likvärdiga är att ORDERBY automatiskt innehåller alla kolumner från relationen som inte finns i PARTITIONBY. Eftersom PARTITIONBY inte angavs är ORDERBY inställt på Color, CalendarYear och CurrentYearSales. Men eftersom paren Color och CalendarYear i relationen är unika ändrar inte resultatet att lägga till CurrentYearSales. Faktum är att även om vi bara skulle ange Färg i ORDERBY, är resultatet detsamma eftersom CalendarYear skulle läggas till automatiskt. Det beror på att funktionen lägger till så många kolumner som behövs i ORDERBY för att säkerställa att varje rad kan identifieras unikt av kolumnerna ORDERBY och 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]
        )
    )

Lägga till PARTITIONBY

För att nästan få det resultat vi är efter kan vi nu använda PARTITIONBY, som du ser i 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 innehåller alla kolumner från relationen som inte anges i PARTITIONBY. Följande uttryck returnerar alltså samma resultat eftersom ORDERBY är inställt på CalendarYear och 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]
        )
    )

Kommentar

Orderby är inställt på CalendarYear och CurrentYearSales automatiskt, men ingen garanti ges för vilken ordning de ska läggas till. Om CurrentYearSales läggs till före CalendarYear är den resulterande ordningen inte i förhållande till vad som förväntas. Var explicit när du anger ORDERBY och PARTITIONBY för att undvika förvirring och oväntade resultat.

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

Färg	CalendarYear	CurrentYearSales	PreviousYearSalesForSameColor
"Svart"	2017	393885
"Svart"	2018	1818835	393885
"Svart"	2019	3981638	1818835
"Svart"	2020	2644054	3981638
"Blå"	2019	994448
"Blå"	2020	1284648	994448
"Flera"	2019	48622
"Flera"	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
"Silver"	2017	326399
"Silver"	2018	750026	326399
"Silver"	2019	2165176	750026
"Silver"	2020	1871788	2165176
"Vit"	2019	2517
"Vit"	2020	2589	2517
"Gul"	2018	163071
"Gul"	2019	2072083	163071
"Gul"	2020	2621602	2072083

Som du ser i den här tabellen visar kolumnen PreviousYearSalesForSameColor försäljningen för föregående år för samma färg. För [Red, 2020] returnerar den försäljningen för [Red, 2019] och så vidare. Om det inte finns något föregående år, till exempel när det gäller [Red, 2017], returneras inget värde.

Du kan se PARTITIONBY som ett sätt att dela upp tabellen i delar där OFFSET-beräkningen ska köras. 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, sorterat efter CalendarYear.

Visuellt är det här:

Först resulterar anropet till PARTITIONBY i att tabellen delas in i delar, en för varje färg. Detta representeras av de ljusblå rutorna i tabellbilden. Därefter ser ORDERBY till att varje del sorteras efter CalendarYear (representeras av de orangea pilarna). Slutligen hittar OFFSET raden ovanför varje sorterad del för varje rad och returnerar värdet i kolumnen PreviousYearSalesForSameColor. Eftersom det för varje första rad i varje del inte finns någon föregående rad i samma del är resultatet i den raden för kolumnen PreviousYearSalesForSameColor tomt.

För att uppnå det slutliga resultatet måste vi helt enkelt subtrahera CurrentYearSales från föregående års försäljning för samma färg som returnerades av anropet till OFFSET. Eftersom vi inte är intresserade av att visa föregående års försäljning för samma färg, utan bara i den aktuella årsförsäljningen och skillnaden mellan år och år. 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]
        )
    )

Och här är resultatet av det uttrycket:

Färg	CalendarYear	CurrentYearSales	YoYSalesForSameColor
"Svart"	2017	393885	393885
"Svart"	2018	1818835	1424950
"Svart"	2019	3981638	2162803
"Svart"	2020	2644054	-1337584
"Blå"	2019	994448	994448
"Blå"	2020	1284648	290200
"Flera"	2019	48622	48622
"Flera"	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
"Silver"	2017	326399	326399
"Silver"	2018	750026	423627
"Silver"	2019	2165176	1415150
"Silver"	2020	1871788	-293388
"Vit"	2019	2517	2517
"Vit"	2020	2589	72
"Gul"	2018	163071	163071
"Gul"	2019	2072083	1909012
"Gul"	2020	2621602	549519

Använda MATCHBY

Du kanske har märkt att vi inte angav MATCHBY alls. I det här fallet är det inte nödvändigt. Kolumnerna i ORDERBY och PARTITIONBY (för den del de angavs i exemplen ovan) räcker för att unikt identifiera varje rad. Eftersom vi inte angav MATCHBY används kolumnerna som anges i ORDERBY och PARTITIONBY 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. Om kolumnerna i ORDERBY och PARTITIONBY inte kan identifiera varje rad unikt kan ytterligare kolumner läggas till i ORDERBY-satsen om de extra kolumnerna tillåter att varje rad identifieras unikt. Om det inte är möjligt returneras ett fel. I det här sista fallet kan det hjälpa att ange MATCHBY för att lösa felet.

Om MATCHBY anges används kolumnerna i MATCHBY och PARTITIONBY för att unikt identifiera varje rad. Om det inte är möjligt returneras ett fel. Även om MATCHBY inte krävs kan du överväga att uttryckligen ange MATCHBY för att undvika förvirring.

Om du fortsätter från exemplen ovan är det här det sista 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]
        )
    )

Om vi vill vara explicita om hur rader ska identifieras unikt kan vi ange MATCHBY enligt 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 PARTITIONBY för att unikt identifiera rader. Eftersom Color anges i både MATCHBY och PARTITIONBY motsvarar följande uttryck föregående uttryck:

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 något 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 och orderrad 1 visas på många beställningar. För varje orderrad har vi dessutom en ProductKey och en SalesAmount. Ett exempel på relevanta kolumner i tabellen ser ut så här:

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

Observera att SalesOrderNumber och SalesOrderLineNumber båda krävs för att unikt identifiera rader.

För varje order vill vi returnera det tidigare försäljningsbeloppet för samma produkt (representeras av ProductKey) som beställts av 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 fel: "OFFSET:s relationsparameter kan ha duplicerade rader, vilket inte är tillåtet."

För att det här uttrycket ska fungera måste MATCHBY anges och innehålla alla kolumner som unikt definierar en rad. MATCHBY krävs här eftersom relationen FactInternetSales inte innehåller några explicita nycklar eller unika kolumner. Kolumnerna SalesOrderNumber och SalesOrderLineNumber utgör dock tillsammans en sammansatt nyckel, där deras existens tillsammans är unik i relationen och därför unikt kan identifiera varje rad. Det räcker inte att bara ange SalesOrderNumber eller SalesOrderLineNumber eftersom båda kolumnerna innehåller upprepade värden. 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]
            )
    )

Och det här uttrycket returnerar verkligen de resultat vi är efter:

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount	Tidigare försäljningsbelopp
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

Relaterat innehåll

ORDERBY
PARTITIONBY
MATCHBY
INDEX
FÖRSKJUTNING
FÖNSTRET
FRODIGT
RADNUMMER