Az ORDERBY, PARTITIONBY és MATCHBY függvények ismertetése

A DAX ORDERBY, PARTITIONBY és MATCHBY függvényei olyan speciális függvények, amelyek csak a DAX-ablakfüggvényekkel együtt használhatók: INDEX, KI Standard kiadás T, ABLAK, RANG, SORSZÁM.

Az ORDERBY, a PARTITIONBY és a MATCHBY ismerete kritikus fontosságú a Window-függvények sikeres használatához. Az itt szereplő példák a KI Standard kiadás T függvényt használják, de a többi ablakfüggvényre is érvényesek.

Eset

Kezdjük egy példával, amely egyáltalán nem használja a Windows függvényeket. Az alábbiakban egy táblázat látható, amely a teljes értékesítést adja vissza, színenként, naptári évenként. Ezt a táblát többféleképpen is definiálhatjuk, de mivel szeretnénk megérteni, hogy mi történik a DAX-ban, egy számított táblát fogunk használni. A táblakifejezés a következő:

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

Ez a számított táblakifejezés a SUMMARIZECOLUMNS függvényt használja a FactInternetSales tábla SalesAmount oszlopának ÖSSZEGének kiszámításához, a DimProduct tábla Szín oszlopa és a DimDate tábla CalendarYear oszlopa alapján. Az eredmény a következő:

Szín	CalendarYear	CurrentYearSales
"Fekete"	2017	393885
"Fekete"	2018	1818835
"Fekete"	2019	3981638
"Fekete"	2020	2644054
"Kék"	2019	994448
"Kék"	2020	1284648
"Több"	2019	48622
"Több"	2020	57849
"NA"	2019	207822
"NA"	2020	227295
"Piros"	2017	2961198
"Piros"	2018	3686935
"Piros"	2019	900175
"Piros"	2020	176022
"Ezüst"	2017	326399
"Ezüst"	2018	750026
"Ezüst"	2019	2165176
"Ezüst"	2020	1871788
"Fehér"	2019	2517
"Fehér"	2020	2589
"Sárga"	2018	163071
"Sárga"	2019	2072083
"Sárga"	2020	2621602

Tegyük fel, hogy megpróbáljuk megoldani az üzleti kérdést az értékesítések különbségének kiszámítására, évről évre az egyes színek esetében. Gyakorlatilag módot kell találnunk arra, hogy az előző év azonos színéhez tartozó értékesítéseket keressük, és kivonjuk az aktuális év értékesítéséből a kontextusban. Például a [Red, 2019] kombinációhoz a [Red, 2018] értékesítést keressük. Ezt követően kivonhatjuk az aktuális értékesítésből, és visszaadhatjuk a szükséges értéket.

A KI Standard kiadás T használata

KI Standard kiadás T tökéletes a korábbi típusú számításokhoz, amelyek a fent leírt üzleti kérdés megválaszolásához szükségesek, mivel lehetővé teszi számunkra, hogy relatív mozgást hajtsunk végre. Az első kísérlet a következő lehet:

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

Sok minden történik ezzel a kifejezéssel. Az ADDCOLUMNS használatával kibontottuk a táblázatot a Korábbi színeladások nevű oszlopban. Az oszlop tartalma a CurrentYearSales(FactInternetSales[SalesAmount]) értékre van állítva az előző színhez (kikapcsolva Standard kiadás T használatával kérhető le).

Az eredmény a következő:

Szín	CalendarYear	CurrentYearSales	PreviousColorSales
"Fekete"	2017	393885
"Fekete"	2018	1818835	393885
"Fekete"	2019	3981638	1818835
"Fekete"	2020	2644054	3981638
"Kék"	2019	994448	2644054
"Kék"	2020	1284648	994448
"Több"	2019	48622	1284648
"Több"	2020	57849	48622
"NA"	2019	207822	57849
"NA"	2020	227295	207822
"Piros"	2017	2961198	227295
"Piros"	2018	3686935	2961198
"Piros"	2019	900175	3686935
"Piros"	2020	176022	900175
"Ezüst"	2017	326399	176022
"Ezüst"	2018	750026	326399
"Ezüst"	2019	2165176	750026
"Ezüst"	2020	1871788	2165176
"Fehér"	2019	2517	1871788
"Fehér"	2020	2589	2517
"Sárga"	2018	163071	2589
"Sárga"	2019	2072083	163071
"Sárga"	2020	2621602	2072083

Ez egy lépéssel közelebb áll a célunkhoz, de ha közelebbről megnézzük, az nem egyezik meg pontosan azzal, amit keresünk. A [Silver, 2017] esetében például a PreviousColorSales értéke [Piros, 2020].

ORDERBY hozzáadása

Ez a fenti definíció a következőnek felel meg:

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

Ebben az esetben a KI Standard kiadás T hívás az ORDERBY használatával rendezi a táblázatot szín és CalendarYear szerint növekvő sorrendben, amely meghatározza, hogy mi tekinthető az előző visszaadott sornak.

Ennek a két eredménynek az az oka, hogy az ORDERBY automatikusan tartalmazza a PARTÍCIÓBY-ban nem szereplő reláció összes oszlopát. Mivel a PARTITIONBY nincs megadva, az ORDERBY értéke Color, CalendarYear és CurrentYearSales. Mivel azonban a reláció Szín és CalendarYear párjai egyediek, a CurrentYearSales hozzáadása nem változtatja meg az eredményt. Valójában még akkor is, ha csak színt adnánk meg az ORDERBY-ben, az eredmények megegyeznek, mivel a CalendarYear automatikusan hozzáadódik. Ennek az az oka, hogy a függvény annyi oszlopot ad hozzá az ORDERBY-hez, amennyi szükséges ahhoz, hogy minden sor egyedileg azonosítható legyen az ORDERBY és a PARTITIONBY oszlopokkal:

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 hozzáadása

Most, hogy majdnem megkapjuk az eredményt, miután használhatjuk a PARTITIONBY-t, az alábbi számított táblakifejezésben látható módon:

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

Figyelje meg, hogy az ORDERBY megadása nem kötelező, mert az ORDERBY automatikusan tartalmazza a PARTÍCIÓBY-ban nem megadott kapcsolat összes oszlopát. A következő kifejezés tehát ugyanazokat az eredményeket adja vissza, mivel az ORDERBY értéke Automatikusan a CalendarYear és a 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]
        )
    )

Megjegyzés:

Bár az ORDERBY automatikusan a CalendarYear és a CurrentYearSales értékre van állítva, nincs garancia arra nézve, hogy milyen sorrendben lesznek hozzáadva. Ha a CurrentYearSales a CalendarYear előtt van hozzáadva, az eredményül kapott sorrend nem a várthoz van összhangban. Legyen explicit az ORDERBY és a PARTITIONBY megadásakor a félreértések és váratlan eredmények elkerülése érdekében.

Mindkét kifejezés a következő eredményt adja vissza:

Szín	CalendarYear	CurrentYearSales	PreviousYearSalesForSameColor
"Fekete"	2017	393885
"Fekete"	2018	1818835	393885
"Fekete"	2019	3981638	1818835
"Fekete"	2020	2644054	3981638
"Kék"	2019	994448
"Kék"	2020	1284648	994448
"Több"	2019	48622
"Több"	2020	57849	48622
"NA"	2019	207822
"NA"	2020	227295	207822
"Piros"	2017	2961198
"Piros"	2018	3686935	2961198
"Piros"	2019	900175	3686935
"Piros"	2020	176022	900175
"Ezüst"	2017	326399
"Ezüst"	2018	750026	326399
"Ezüst"	2019	2165176	750026
"Ezüst"	2020	1871788	2165176
"Fehér"	2019	2517
"Fehér"	2020	2589	2517
"Sárga"	2018	163071
"Sárga"	2019	2072083	163071
"Sárga"	2020	2621602	2072083

Ahogy ebben a táblázatban látható, a PreviousYearSalesForSameColor oszlop az előző év azonos színre vonatkozó értékesítéseit jeleníti meg. A [Red, 2020] esetében a [Red, 2019] és így tovább. Ha nincs előző év, például a [Piros, 2017] esetében, a függvény nem ad vissza értéket.

A PARTITIONBY-ra úgy gondolhat, hogy a táblát olyan részekre osztja, amelyekben végrehajtja a KI Standard kiadás T számítást. A fenti példában a táblázat annyi részre van osztva, amennyi szín van, egy-egy színre. Ezután minden részen belül a KI Standard kiadás T kiszámítása a CalendarYear szerint történik.

Vizuálisan a következő történik:

Először a PARTITIONBY hívása azt eredményezi, hogy a táblázat részekre van osztva, egy-egy színre. Ezt a táblázat képének világoskék mezői jelölik. Ezután az ORDERBY gondoskodik arról, hogy az egyes részeket naptárév szerint rendezze (a narancssárga nyilakkal jelölve). Végül minden egyes sor sorában a KI Standard kiadás T megkeresi a fölötte lévő sort, és visszaadja ezt az értéket a PreviousYearSalesForSameColor oszlopban. Mivel minden egyes rész első sorában nincs előző sor ugyanabban a részben, a PreviousYearSalesForSameColor oszlopban lévő eredmény üres.

A végeredmény eléréséhez egyszerűen ki kell vonnunk a CurrentYearSales értéket az előző évi értékesítésekből az OFF Standard kiadás T hívás által visszaadott színre. Mivel nem szeretnénk az előző évi értékesítéseket azonos színnel ábrázolni, csak az aktuális évi értékesítésekben és az év közbeni különbségben. Íme az utolsó számított táblakifejezés:

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

A kifejezés eredménye pedig a következő:

Szín	CalendarYear	CurrentYearSales	YoySalesForSameColor
"Fekete"	2017	393885	393885
"Fekete"	2018	1818835	1424950
"Fekete"	2019	3981638	2162803
"Fekete"	2020	2644054	-1337584
"Kék"	2019	994448	994448
"Kék"	2020	1284648	290200
"Több"	2019	48622	48622
"Több"	2020	57849	9227
"NA"	2019	207822	207822
"NA"	2020	227295	19473
"Piros"	2017	2961198	2961198
"Piros"	2018	3686935	725737
"Piros"	2019	900175	-2786760
"Piros"	2020	176022	-724153
"Ezüst"	2017	326399	326399
"Ezüst"	2018	750026	423627
"Ezüst"	2019	2165176	1415150
"Ezüst"	2020	1871788	-293388
"Fehér"	2019	2517	2517
"Fehér"	2020	2589	72
"Sárga"	2018	163071	163071
"Sárga"	2019	2072083	1909012
"Sárga"	2020	2621602	549519

A MATCHBY használata

Lehet, hogy észrevette, hogy egyáltalán nem adtunk meg MATCHBY értéket. Ebben az esetben nem szükséges. Az ORDERBY és a PARTITIONBY oszlopai (amennyiben a fenti példákban meg vannak adva) elegendőek az egyes sorok egyedi azonosításához. Mivel nem használtuk a MATCHBY függvényt, az ORDERBY és a PARTITIONBY mezőben megadott oszlopok segítségével egyedileg azonosíthatók az egyes sorok, így összehasonlíthatók a KI Standard kiadás T funkcióval, hogy értelmes eredményt adjanak. Ha az ORDERBY és a PARTITIONBY oszlopai nem tudják egyedileg azonosítani az egyes sorokat, további oszlopok is hozzáadhatók az ORDERBY záradékhoz, ha ezek a további oszlopok lehetővé teszik az egyes sorok egyedi azonosítását. Ha ez nem lehetséges, hibaüzenet jelenik meg. Ebben az utolsó esetben a MATCHBY megadása segíthet a hiba megoldásában.

Ha a MATCHBY meg van adva, a MATCHBY és a PARTITIONBY oszlopai az egyes sorok egyedi azonosítására szolgálnak. Ha ez nem lehetséges, hibaüzenet jelenik meg. Még ha a MATCHBY nem is szükséges, fontolja meg a MATCHBY explicit megadását a félreértések elkerülése érdekében.

A fenti példákat folytatva az utolsó kifejezés:

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

Ha explicit módon szeretnénk meghatározni, hogyan kell egyedileg azonosítani a sorokat, megadhatja a MATCHBY értéket az alábbi egyenértékű kifejezésben látható módon:

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

Mivel a MATCHBY meg van adva, a MATCHBY-ben és a PARTITIONBY-ban megadott oszlopok egyaránt a sorok egyedi azonosítására szolgálnak. Mivel a szín a MATCHBY és a PARTITIONBY függvényben is meg van adva, a következő kifejezés egyenértékű az előző kifejezéssel:

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

Mivel a MATCHBY megadása nem szükséges az eddig megvizsgált példákban, nézzünk meg egy kicsit más példát, amely megköveteli a MATCHBY-t. Ebben az esetben a rendelési sorok listája van. Minden sor egy rendeléssort jelöl. A rendelések több rendeléssort is tartalmazhatnak, és az 1. rendeléssor számos rendelésen megjelenik. Emellett minden rendeléssorhoz tartozik egy ProductKey és egy SalesAmount. A táblázat megfelelő oszlopainak mintája a következőképpen néz ki:

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

Figyelje meg, hogy a SalesOrderNumber és a SalesOrderLineNumber is szükséges a sorok egyedi azonosításához.

Minden megrendeléshez a SalesAmount által csökkenő sorrendben megrendelt ugyanazon termék (a ProductKey által képviselt) előző értékesítési összegét szeretnénk visszaadni. A következő kifejezés nem fog működni, mert a vRelationban több sor is lehet, mivel az ki van kapcsolva Standard kiadás T:

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

Ez a kifejezés egy hibát ad vissza: "KI Standard kiadás T Relációs paramétere ismétlődő sorokat tartalmazhat, amelyek nem engedélyezettek."

A kifejezés működéséhez meg kell adni a MATCHBY értéket, és tartalmaznia kell az összes olyan oszlopot, amely egyedileg definiál egy sort. A MATCHBY azért szükséges, mert a FactInternetSales reláció nem tartalmaz explicit kulcsokat vagy egyedi oszlopokat. A SalesOrderNumber és a SalesOrderLineNumber oszlopok azonban egy összetett kulcsot alkotnak, ahol a relációban egyedi a létezésük, ezért egyedien azonosítják az egyes sorokat. A SalesOrderNumber vagy a SalesOrderLineNumber megadása nem elegendő, mivel mindkét oszlop ismétlődő értékeket tartalmaz. A következő kifejezés megoldja a problémát:

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

És ez a kifejezés valóban visszaadja a következő eredményeket:

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount	Előző értékesítési összeg
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

Kapcsolódó tartalom

ORDERBY
PARTITIONBY
MATCHBY
INDEX
KI STANDARD KIADÁS T
ABLAK
RANGOT
ROWNUMBER