ORDERBY-, PARTITIONBY- ja MATCHBY-funktioiden ymmärtäminen

DAX ORDERBY-, PARTITIONBY- ja MATCHBY-funktiot ovat erikoisfunktioita, joita voidaan käyttää vain DAX -ikkunafunktioiden kanssa: INDEX, OFFSET, WINDOW, RANK, ROWNUMBER.

On tärkeää ymmärtää ORDERBY, PARTITIONBYja MATCHBY, jotta voit käyttää Window-funktioita. Tässä esitetyissä esimerkeissä käytetään OFFSET, mutta niitä voidaan soveltaa samoin kuin muita Window-funktioita.

Skenaario

Aloitetaan esimerkistä, jossa ei käytetä Ikkuna-funktioita lainkaan. Alla on taulukko, joka palauttaa kokonaismyynnin väriä ja kalenterivuotta kohden. Tämä taulukko voidaan määrittää monella tavalla, mutta koska olemme kiinnostuneita siitä, mitä DAXtapahtuu, käytämme laskettua taulukkoa. Tässä on taulukkolauseke:

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

Näet, että tämä laskettu taulukkolauseke käyttää SUMMARIZECOLUMNS Laskettaessa FactInternetSales-taulukon SalesAmount-sarakkeen SUM DimProduct-taulukon Color-sarakkeen ja DimDate-taulukon CalendarYear-sarakkeen mukaan. Tässä on tulos:

Väri	Kalenterivuosi	CurrentYearSales
"Musta"	2017	393885
"Musta"	2018	1818835
"Musta"	2019	3981638
"Musta"	2020	2644054
"Sininen"	2019	994448
"Sininen"	2020	1284648
"Moni"	2019	48622
"Moni"	2020	57849
"NA"	2019	207822
"NA"	2020	227295
"Punainen"	2017	2961198
"Punainen"	2018	3686935
"Punainen"	2019	900175
"Punainen"	2020	176022
"Hopeinen"	2017	326399
"Hopeinen"	2018	750026
"Hopeinen"	2019	2165176
"Hopeinen"	2020	1871788
"Valkoinen"	2019	2517
"Valkoinen"	2020	2589
"Keltainen"	2018	163071
"Keltainen"	2019	2072083
"Keltainen"	2020	2621602

Ajatellaanpa, että yritämme ratkaista liiketoimintakysymystä, joka koskee myynnin eron laskemista vuoden aikana kullekin värille. Käytännössä tarvitset tavan löytää edellisen vuoden saman värisen myynnin ja vähentää sen nykyisen vuoden myynnistä kontekstissa. Esimerkiksi yhdistelmälle [Red, 2019] etsitään myyntiä tuotteille [Red, 2018]. Kun se on saatu, voimme vähentää sen nykyisestä myynnistä ja palauttaa vaaditun arvon.

OFFSET käyttäminen

OFFSET sopii täydellisesti tyypillisiin verrattuna aiempiin laskutoimitusten tyyppeihin, joita tarvitaan edellä kuvattuun liiketoimintakysymykseen vastaamiseksi, koska se mahdollistaa suhteellisen liikkumisen. Ensimmäinen yrityksemme voi olla:

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

Tällä lausekkeella tapahtuu paljon. Käytimme ADDCOLUMNS taulukon laajentamiseen aiempaan sarakkeella nimeltä PreviousColorSales. Kyseisen sarakkeen sisällöksi määritetään CurrentYearSales, joka on SUM(FactInternetSales[SalesAmount]) edelliselle värille (noudettu käyttämällä OFFSET).

Tulos on:

Väri	Kalenterivuosi	CurrentYearSales	PreviousColorSales
"Musta"	2017	393885
"Musta"	2018	1818835	393885
"Musta"	2019	3981638	1818835
"Musta"	2020	2644054	3981638
"Sininen"	2019	994448	2644054
"Sininen"	2020	1284648	994448
"Moni"	2019	48622	1284648
"Moni"	2020	57849	48622
"NA"	2019	207822	57849
"NA"	2020	227295	207822
"Punainen"	2017	2961198	227295
"Punainen"	2018	3686935	2961198
"Punainen"	2019	900175	3686935
"Punainen"	2020	176022	900175
"Hopeinen"	2017	326399	176022
"Hopeinen"	2018	750026	326399
"Hopeinen"	2019	2165176	750026
"Hopeinen"	2020	1871788	2165176
"Valkoinen"	2019	2517	1871788
"Valkoinen"	2020	2589	2517
"Keltainen"	2018	163071	2589
"Keltainen"	2019	2072083	163071
"Keltainen"	2020	2621602	2072083

Tämä on yksi askel lähempänä tavoitettamme, mutta jos katsomme tarkasti, se ei vastaa tarkalleen mitä haluamme. Esimerkiksi kaavalle [Silver, 2017] PreviousColorSales on määritetty arvoksi [Red, 2020].

ORDERBY lisääminen

Edellä oleva määritelmä vastaa:

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

Tässä tapauksessa OFFSET kutsu käyttää ORDERBY järjestääkseen taulukon Color- ja CalendarYear-funktioiden mukaan nousevassa järjestyksessä, mikä määrittää, mitä pidetään edellisenä palautettavana rivinä.

Nämä kaksi tulosta vastaavat toisiaan, koska ORDERBY sisältää automaattisesti kaikki vertailun sarakkeet, jotka eivät ole PARTITIONBY. Koska PARTITIONBY ei määritetty, ORDERBY on määritetty arvoksi Väri, Kalenterivuosi ja CurrentYearSales. Koska väri- ja kalenterivuosiparit suhteessa ovat kuitenkin yksilöllisiä, CurrentYearSales-sarakkeen lisääminen ei muuta tulosta. Vaikka määrittäisit värin vain ORDERBY, tulokset ovat samat, koska Kalenterivuosi lisätään automaattisesti. Tämä johtuu siitä, että funktio lisää niin monta saraketta kuin tarvitaan ORDERBY, jotta ORDERBY ja PARTITIONBY-sarakkeet voivat tunnistaa kunkin rivin yksilöllisesti:

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

PARTITIONBY lisääminen

Nyt voit lähes saada tulokseksi tuloksemme, jonka jälkeen voimme käyttää PARTITIONBY, seuraavassa lasketun taulukon lausekkeessa esitetyllä tavalla:

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

Huomaa, että ORDERBY määrittäminen on tässä valinnaista, koska ORDERBY sisältää automaattisesti kaikki suhteen sarakkeet, joita ei ole määritetty PARTITIONBY. Seuraava lauseke palauttaa siis samat tulokset, koska ORDERBY-arvoksi on määritetty automaattisesti CalendarYear ja 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]
        )
    )

Muistiinpano

Vaikka ORDERBY on määritetty automaattisesti Kalenterivuosi- ja Kuluva vuosimyynti -arvoihin, niiden lisäysjärjestyksestä ei anneta mitään takuuta. Jos CurrentYearSales lisätään ennen kalenterivuotta, tuloksena saatu tilaus ei vastaa odotettua. Määritä ORDERBY ja PARTITIONBY eksplisiittisesti sekaannusten ja odottamattomien tulosten välttämiseksi.

Molemmat lausekkeet palauttavat etsimämme tuloksen:

Väri	Kalenterivuosi	CurrentYearSales	PreviousYearSalesForSameColor
"Musta"	2017	393885
"Musta"	2018	1818835	393885
"Musta"	2019	3981638	1818835
"Musta"	2020	2644054	3981638
"Sininen"	2019	994448
"Sininen"	2020	1284648	994448
"Moni"	2019	48622
"Moni"	2020	57849	48622
"NA"	2019	207822
"NA"	2020	227295	207822
"Punainen"	2017	2961198
"Punainen"	2018	3686935	2961198
"Punainen"	2019	900175	3686935
"Punainen"	2020	176022	900175
"Hopeinen"	2017	326399
"Hopeinen"	2018	750026	326399
"Hopeinen"	2019	2165176	750026
"Hopeinen"	2020	1871788	2165176
"Valkoinen"	2019	2517
"Valkoinen"	2020	2589	2517
"Keltainen"	2018	163071
"Keltainen"	2019	2072083	163071
"Keltainen"	2020	2621602	2072083

Kuten tässä taulukossa näkyy, PreviousYearSalesForSameColor-sarake näyttää edellisen vuoden myynnin samalle värille. Funktiolle [Red, 2020] se palauttaa esimerkiksi myynnin arvolle [Red, 2019]. Jos edellistä vuotta ei ole, esimerkiksi jos kyseessä on [Red, 2017], mitään arvoa ei palauteta.

Voit ajatella PARTITIONBY keinona jakaa taulukko osiin, joissa OFFSET laskutoimitus suoritetaan. Yllä olevassa esimerkissä taulukko on jaettu niin moneen osaan kuin väreihin, yksi kuhunkin väriin. Sitten OFFSET lasketaan kunkin osan sisällä kalenterivuoden mukaan lajiteltuna.

Visuaalisesti tämä on seuraava tapahtuma:

mukaan

PARTITIONBY kutsu jakaa ensin taulukon osiin, yhden kullekin Värille. Tätä ilmaisevat taulukon kuvan vaaleansiniset ruudut. Seuraavaksi ORDERBY varmistaa, että jokainen osa lajitellaan kalenterivuoden mukaan (oranssit nuolet edustavat sitä). Lopuksi kunkin lajitellun osan sisällä kullekin riville OFFSET löytää sen yläpuolella olevan rivin ja palauttaa tämän arvon PreviousYearSalesForSameColor-sarakkeessa. Koska kunkin osan jokaisen ensimmäisen rivin kohdalla ei ole edellistä riviä samassa osassa, tämä PreviousYearSalesForSameColor-sarakkeen rivi on tyhjä.

Jotta saavutetaan lopullinen tulos, meidän on yksinkertaisesti vähennettävä CurrentYearSales edellisen vuoden myynnistä samalle värille, jonka kutsu palautti OFFSET. Koska emme halua näyttää edellisen vuoden myyntiä samalla värillä, vaan vain nykyisen vuoden myynnissä ja vuosi-erossa. Tässä on lopullinen lasketun taulukon lauseke:

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

Tässä on lausekkeen tulos:

Väri	Kalenterivuosi	CurrentYearSales	YoYSalesForSameColor
"Musta"	2017	393885	393885
"Musta"	2018	1818835	1424950
"Musta"	2019	3981638	2162803
"Musta"	2020	2644054	-1337584
"Sininen"	2019	994448	994448
"Sininen"	2020	1284648	290200
"Moni"	2019	48622	48622
"Moni"	2020	57849	9227
"NA"	2019	207822	207822
"NA"	2020	227295	19473
"Punainen"	2017	2961198	2961198
"Punainen"	2018	3686935	725737
"Punainen"	2019	900175	-2786760
"Punainen"	2020	176022	-724153
"Hopeinen"	2017	326399	326399
"Hopeinen"	2018	750026	423627
"Hopeinen"	2019	2165176	1415150
"Hopeinen"	2020	1871788	-293388
"Valkoinen"	2019	2517	2517
"Valkoinen"	2020	2589	72
"Keltainen"	2018	163071	163071
"Keltainen"	2019	2072083	1909012
"Keltainen"	2020	2621602	549519

MATCHBY käyttäminen

Olet ehkä huomannut, että emme määrittäneet MATCHBY ollenkaan. Tässä tapauksessa se ei ole tarpeen. ORDERBY ja PARTITIONBY sarakkeet (yllä esimerkeissä määritetyn mukaisesti) riittävät yksilöimään kunkin rivin. Koska emme määrittäneet MATCHBY, ORDERBY määritettyjä sarakkeita ja PARTITIONBY käytetään yksilöimään kukin rivi, jotta niitä voidaan verrata siihen, OFFSET mahdollistavat merkityksellisen tuloksen. Jos ORDERBY ja PARTITIONBY sarakkeita ei pysty yksilöimään kutakin riviä, ORDERBY-lauseeseen voidaan lisätä lisäsarakkeita, jos näiden lisäsarakkeiden avulla kukin rivi voidaan tunnistaa yksilöllisesti. Jos se ei ole mahdollista, palautetaan virhe. Tässä viimeisessä tapauksessa MATCHBY määrittäminen voi auttaa ratkaisemaan virheen.

Jos MATCHBY on määritetty, MATCHBY ja PARTITIONBY sarakkeita käytetään yksilöimään kukin rivi. Jos se ei ole mahdollista, palautetaan virhe. Vaikka MATCHBY ei olisi pakollista, harkitse MATCHBY eksplisiittistä määrittämistä sekaannuksien välttämiseksi.

Yllä olevista esimerkeistä jatkaen tässä on viimeinen lauseke:

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

Jos haluamme määrittää tarkasti, miten rivit tulee yksilöidä, voimme määrittää MATCHBY seuraavassa vastaavassa lausekkeessa esitetyllä tavalla:

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

Koska määritetään MATCHBY, sekä MATCHBY että PARTITIONBY määritettyjä sarakkeita käytetään rivien yksilöimiseen. Koska Color määritetään sekä MATCHBY että PARTITIONBY, seuraava lauseke vastaa edellistä lauseketta:

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

Koska MATCHBY määrittäminen ei ole tarpeen esimerkeissä, joita olemme tähän mennessä tarkastelleet, katsotaanpa hieman erilaista esimerkkiä, joka edellyttää MATCHBY. Tässä tapauksessa meillä on luettelo tilausriveistä. Jokainen rivi edustaa tilauksen tilausriviä. Tilaukseen voi olla useita tilausrivejä, ja tilausrivi 1 näkyy useissa tilauksissa. Lisäksi jokaisella tilausrivillä on ProductKey- ja SalesAmount-arvot. Esimerkki taulukon asiaankuuluvista sarakkeista näyttää tältä:

SalesOrderNumber	SalesOrderLineNumber	Tuoteavain	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

Huomaa, että rivien yksilöimiseen tarvitaan sekä SalesOrderNumber että SalesOrderLineNumber.

Haluamme palauttaa kullekin tilaukselle saman tuotteen edellisen myyntisumman (jota edustaa Tuoteavain), jonka SalesAmount tilasi laskevassa järjestyksessä. Seuraava lauseke ei toimi, koska vRelationissa on mahdollisesti useita rivejä, koska se välitetään OFFSET:

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

Tämä lauseke palauttaa virheen: "OFFSET:n Relation-parametrissa voi olla rivien kaksoiskappaleita, mitä ei sallita."

Jotta tämä lauseke toimisi, MATCHBY on määritettävä ja siihen on sisällytettävä kaikki sarakkeet, jotka määrittävät rivin yksilöllisesti. MATCHBY tässä vaaditaan, koska suhde FactInternetSales ei sisällä eksplisiittisiä avaimia tai yksilöllisiä sarakkeita. SalesOrderNumber- ja SalesOrderLineNumber-sarakkeet muodostavat kuitenkin yhdessä yhdistelmäavaimen, jossa niiden olemassaolo yhdessä on yksilöllinen suhteessa ja siten tunnistaa kukin rivi yksilöllisesti. Pelkkä SalesOrderNumber- tai SalesOrderLineNumber-arvon määrittäminen ei riitä, koska molemmat sarakkeet sisältävät toistuvia arvoja. Seuraava lauseke ratkaisee ongelman:

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

Ja tämä lauseke todellakin palauttaa seuraamme seuraavat tulokset:

SalesOrderNumber	SalesOrderLineNumber	Tuoteavain	SalesAmount	Edellisen myynnin summa
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

Aiheeseen liittyvä sisältö

ORDERBY PARTITIONBY MATCHBY INDEX OFFSET WINDOW RANK ROWNUMBER