Příklad vytvoření pořadí na základě více hodnot v jazyku DAX

Vytvoření pořadí na základě více hodnot může být užitečné v situacích, kdy se primární hodnoty použité pro vytvoření pořadí mohou shodovat pro více řazených položek. Za takovýchto okolností může vzniknout požadavek na použití další dodatečné hodnoty pro vytvoření pořadí. V tomto příspěvku si ukážeme příklad jak vytvořit pořadí na základě více hodnot, a to jak pomocí funkce RANK(), tak pomocí starší DAX funkce RANKX().

S funkcí RANK() je vytvoření pořadí na základě více hodnot relativně jednoduché. V této funkci stačí do funkce ORDERBY() přidat jednoduše další argumenty. Funkce RANK() je však v době psaní tohoto příspěvku stále relativně nová funkce, a nemusí proto být dostupná ve všech verzích Tabulárního modelu. Proto si v tomto příspěvku ukážeme také alternativní postup, kdy pro vytvoření pořadí na základě více hodnot použijeme v jazyku DAX starší funkci RANKX().

K tomuto tématu je k dispozici také video:

V příkladech budeme pracovat s Power BI souborem Adventure Works DW 2020.pbix, který je volně dostupný ke stažení na internetu. Soubor s řešením je dostupný ke stažení níže pod tímto příspěvkem.

Pro vytvoření pořadí budeme používat dvě měřítka, primárně měřítko [Počet produktů] a sekundárně měřítko [Prodeje].

Měřítka:

Počet produktů = COUNTROWS('Product')

Prodeje = SUM(Sales[Sales Amount])

Pokud obě nová měřítka vložíme do vizuálu Matice, spolu s kategoriemi a podkategoriemi produktů v řádcích vizuálu, výchozí report může vypadat například následovně.

V první části příspěvku si ukážeme jak vytvořit pořadí na základě více hodnot pomocí funkce RANK(). Ve druhé části příspěvku si pak ukážeme alternativní postup s využitím funkce RANKX(), protože novější funkce RANK() nemusí být (v době psaní tohoto příspěvku není) k dispozici v Power Pivot v Excelu nebo se starších verzích SSAS Tabular (k dispozici až od verze v16.0.70.21).

Pořadí na základě více hodnot s funkcí RANK

Při pohledu na vizuál Matice zobrazený na předchozím obrázku je zřejmé, že pokud vytvoříme pořadí podkategorií na základě hodnoty měřítka [Počet produktů], některé podkategorie budou v pořadí na stejném místě, protože počet produktů v těchto podkategoriích je také shodný. To si můžeme snadno ověřit. Následující měřítko bude vracet pořadí podkategorií na základě hodnot měřítka [Počet produktů], a to tak že podkategorie s vyšším počtem produktů budou zařazeny na vyšší pozici.

Měřítko:

Pořadí (podle počtu produktů) =

RANK

(

    DENSE,

    ALL('Product'[Subcategory]),

    ORDERBY([Počet produktů], DESC)

)

Pokud nové měřítko vložíme do dříve vytvořeného vizuálu Matice, výsledek bude vypadat následovně.

Jak je možné vidět na obrázku výše, měřítko [Pořadí (podle počtu produktů)] vrací stejné pořadí pro podkategorie, které mají stejnou hodnotu měřítka [Počet produktů]. Jedná se například o podkategorie "Handlebars" a "Wheels", které mají obě shodně 14 produktů, a jejich pořadové číslo je také stejné.

Pokud bychom chtěli použít pro vytvoření pořadí další dodatečné hodnoty, můžeme ve funkci RANK() jednoduše přidat další argumenty do funkce ORDERBY(). Následující měřítko tak bude vracet pořadí na základě hodnot měřítka [Počet produktů] a následně na základě hodnot měřítka [Prodeje]. Způsob řazení můžeme opět nastavit pro obě měřítka sestupně – DESC.

Měřítko:

Pořadí (podle počtu produktů a prodejů) =

RANK

(

    DENSE,

    ALL('Product'[Subcategory]),

    ORDERBY([Počet produktů], DESC, [Prodeje], DESC)

)

Pokud bude nyní měřítko [Počet produktů] vracet stejnou hodnotu u více podkategorií, tyto podkategorie budou dodatečně seřazeny podle hodnoty měřítka [Prodeje]. Nové měřítko si můžeme opět vložit do vizuálu a zobrazit si výsledek.

Zaměřit se opět můžeme na podkategorie "Wheels" a "Handlebars". Pro tyto dvě podkategorie vrací měřítko [Pořadí (podle počtu produktů)] stejnou hodnotu, a to číslo čtyři. Nové měřítko [Pořadí (podle počtu produktů a prodejů)] již ale vrací pro podkategorii "Wheels" číslo čtyři, a pro podkategorii "Handlebars" číslo pět.

Podkategorie "Wheels" je podle výsledků měřítka [Pořadí (podle počtu produktů a prodejů)] v pořadí před podkategorií "Handlebars", protože měřítko [Prodeje], které je použité jako druhý parametr pro určení pořadí, vrací pro tuto podkategorii vyšší hodnotu.

Ačkoliv měřítko [Pořadí (podle počtu produktů a prodejů)] nyní vrací pro každou podkategorii jedinečnou hodnotu, neznamená to, že po aktualizaci dat nebo po přidání dalších filtrů do reportu nemůže toto měřítko vracet opět stejné pořadí pro některé z podkategorií. To by mohlo nastat v případě, kdy by jak měřítko [Počet produktů], tak měřítko [Prodeje] vracelo pro dvě nebo více podkategorií stejné hodnoty. Garanci jedinečného pořadí bychom si zajistili pouze pokud bychom pro vytvoření pořadí použili také jedinečný identifikátor řádků vizuálu. V tomto příkladu by se mohlo jednat například o názvy podkategorií.

Vytvoření pořadí na základě více hodnot je při použití funkce RANK() relativně jednoduché. Problém ale může nastat, pokud funkce RANK() není k dispozici. Starší DAX funkce RANKX() totiž umožňuje vytvoření pořadí pouze na základě jedné hodnoty. V následující části příspěvku si proto ukážeme jeden z možných způsobů, jak toto omezení obejít a jak vytvořit pořadí na základě více hodnot také při použití funkce RANKX().

Pořadí na základě více hodnot s funkcí RANKX

Funkce RANKX() umožňuje vytvoření pořadí pouze na základě jedné hodnoty. Tuto hodnotu ale můžeme definovat libovolným způsobem. Vraťme se tedy k našemu příkladu, kde budeme chtít opět vytvořit pořadí podkategorií na základě měřítek [Počet produktů] a [Prodeje].

Měřítko [Počet produktů] je v našem příkladu primární měřítko pro určení pořadí, a proto musí mít ve výsledném výrazu vyšší váhu než měřítko [Prodeje]. Musíme tedy hodnoty měřítka [Počet produktů] vynásobit stejnou hodnotou pro každou podkategorii tak, aby výsledná hodnota byla vždy vyšší než hodnota měřítka [Prodeje], které následně k vynásobené hodnotě měřítka [Počet produktů] přičteme. Abychom si tuto úvahu mohli znázornit, vytvoříme si pomocné měřítko pouze za účelem vysvětlení celé logiky.

Měřítko:

Kritérium pro RANKX (zjednodušené) =

[Počet produktů] * 1000000000 + [Prodeje]

Pokud si nové měřítko vložíme do vizuálu, celá logika pro sestavení výrazu pro řazení ve funkci RANKX() by již měla být zřejmá.

Pokud se podíváme na výsledky měřítka [Kritérium pro RANKX (zjednodušené)] tak můžeme vidět, že z výsledných hodnot bychom nyní dokázali snadno odvodit počet produktů v podkategorii (vždy na začátku výsledného čísla) a také hodnoty prodejů (vždy na konci výsledného čísla). Současně tím že počet produktů se nachází na začátku čísla, má tato hodnota v každém řádku přirozeně vyšší váhu a primární řazení bude právě podle počtu produktů, což bylo naším cílem. Pokud bude počet produktů stejný pro více podkategorií, podkategorie bude dodatečně zařazena podle prodejů, které jsou na konci výsledného čísla a mají tedy nižší váhu.

Problémem výrazu v měřítku [Kritérium pro RANKX (zjednodušené)] je, že obsahuje konstantu pro vynásobení hodnoty měřítka [Počet produktů]. Tato hodnota určená pro vynásobení by měla být sice stejná pro každou podkategorii, ale určitě bude lepší, když bude určena dynamicky. Hodnota pro vynásobení měřítka [Počet produktů] musí být alespoň tak velká, jako je nejvyšší hodnota měřítka [Prodeje] pro jednotlivé podkategorie. Jinak řečeno potřebujeme zajistit, aby hodnota měřítka [Prodeje] nebyla nikdy vyšší než je hodnota měřítka [Počet produktů]. Výraz který použijeme pro určení pořadí pomocí funkce RANKX() tak může vypadat například následovně.

Měřítko:

Kritérium pro RANKX (použité) =

VAR Nasobek =

    MAXX

    (

        ALL('Product'[Subcategory]),

        [Prodeje]

    )

VAR Vypocet = [Počet produktů] * Nasobek + [Prodeje]

RETURN

    Vypocet

Výsledné hodnoty měřítka [Kritérium pro RANKX (použité)] již nebudou příliš čitelné a nebudeme z nich moci jednoduše odvodit kde začíná vynásobená hodnota měřítka [Počet produktů] a kde začíná hodnota měřítka [Prodeje], tak jako to bylo u předchozí zjednodušené verze.

Pro účely vytvoření pořadí má ale tato nová verze naprosto stejný význam. Násobek je navíc určen dynamicky a je nejmenší možný, který nám zajišťuje to že váha měřítka [Prodeje] nebude ve výsledném výrazu nikdy vyšší než váha měřítka [Počet produktů].

Výsledné měřítko pro určení pořadí podkategorií na základě hodnot měřítek [Počet produktů] a [Prodeje], kde měřítko [Počet produktů] má vyšší váhu a řazení je sestupné, může vypadat následovně.

Měřítko:

Pořadí (podle počtu produktů a prodejů) - RANKX =

VAR Nasobek =

    MAXX

    (

        ALL('Product'[Subcategory]),

        [Prodeje]

    )

VAR Vypocet =

    RANKX

    (

        ALL('Product'[Subcategory]),

        [Počet produktů] * Nasobek + [Prodeje],

        ,

        DESC,

        Dense

    )

VAR Vysledek =

    IF

    (

        ISINSCOPE('Product'[Subcategory]),

        Vypocet

    )

RETURN

    Vysledek

Pokud nové měřítko vložíme do vizuálu Matice spolu s měřítkem [Pořadí (podle počtu produktů a prodejů)], výsledky obou měřítek budou nyní totožné.

Hodnoty obou verzí výpočtu jsou nyní stejné, nicméně vždy když potřebujeme vytvořit pořadí na základě více hodnot, měli bychom použít funkci RANK() namísto funkce RANKX(). Funkce RANK() je nová funkce která umožňuje vytvoření pořadí na základě více hodnot velmi jednoduchým způsobem. Na druhou stranu funkce RANKX() umožňuje vytvoření pořadí pouze na základě jedné hodnoty. Výraz pro určení pořadí tedy musí být v případě více kritérií sestaven relativně komplikovaným způsobem, který může být při drobné změně v kritériích ještě výrazně složitější než v příkladu v tomto příspěvku. Pokud tedy potřebujeme vytvořit pořadí na základě více hodnot a máme k dispozici funkci RANK(), tato funkce bude vždy první volbou.

Další příklady můžete najít na stránce DAX – příklady nebo na stránce Power BI. Informace o vybraných funkcích, včetně funkcí RANK() a RANKX(), najdete na stránce Jazyk DAX.

Stáhnout soubor s řešením.
č. 91