Tento příspěvek obsahuje syntaxi a příklady použití funkce RANK() v Power BI. Funkce RANK() je funkce určená pro vytvoření pořadí a jedná se v současné době o nejvíce pokročilou funkci, kterou můžeme pro tento účel v jazyku DAX použít. Funkce RANK() patří do kategorie takzvaných WINDOW funkcí. Všechny WINDOW funkce, mezi které řadíme také funkce INDEX(), OFFSET(), WINDOW() a ROWNUMBER(), mají několik společných jmenovatelů a hlavně přináší nový přístup k psaní DAX výpočtů.
V první části příspěvku jsou vysvětleny jednotlivé argumenty funkce RANK(). Druhá část příspěvku obsahuje vybrané příklady použití funkce RANK(). Všechny příklady v tomto příspěvku jsou vytvořeny ve cvičném Power BI souboru Adventure Works DW 2020.pbix. Soubor s řešenými příklady si můžete stáhnout pod odkazem uvedeným na konci tohoto příspěvku.
DAX funkce RANK
Než se přesuneme k vybraným příkladům s funkcí RANK(), popíšeme si jednotlivé argumenty které v této funkci můžeme používat.
Funkce RANK() má následující syntaxi.
Syntaxe funkce RANK:
Přestože může syntaxe funkce RANK() vypadat na první pohled složitě, její používání je velmi intuitivní a v porovnání se starší funkcí RANKX() relativně jednoduché, jak bude možné vidět na některých příkladech níže v tomto příspěvku.
Funkce RANK() má celkem šest argumentů, z niž jsou všechny za určitých okolností nepovinné. Za určitých okolností je myšleno tak, že při použití některých argumentů můžeme ostatní argumenty vynechat. Další zvláštností funkce RANK() je, že v určitých argumentech této funkce můžeme použít pouze jiné speciální funkce, vytvořené pouze za účelem použití ve WINDOW funkcích. Z výše uvedených důvodů si každý argument funkce RANK() zaslouží samostatnou krátkou vysvětlivku.
Vazby (ties) - volitelný argument, pomocí kterého můžeme určit, jak se budou řadit hodnoty v situaci, kdy jsou dva nebo více řádků v pořadí na stejném místě. Zadat můžeme DENSE (např. 1, 2, 3, 3, 3, 3, 4) nebo SKIP (např. 1, 2, 3, 3, 3, 3, 7). Při vynechání tohoto argumentu budou hodnoty řazeny podle pravidla SKIP.
Tabulka (relation) - tabulka nebo funkce vracející tabulku, která bude použita pro vytvoření pořadí. Tento argument je nepovinný v případě, kdy je zadán argument <řazení>. V případě vynechání tohoto argumentu ale musí sloupce v argumentu <řazení> a v argumentu <rozdělit podle> pocházet ze stejné tabulky. V takovémto případě je na pozadí místo tohoto argumentu vytvořena tabulka pomocí funkce ALLSELECTED(), se všemi sloupci zadanými v argumentu <řazení> a případně v argumentu <rozdělit podle>, pokud je použit.
Řazení (orderBy) - volitelný argument vyhrazený pro speciální funkci ORDERBY(). Pokud je tento argument vynechán, tabulka v argumentu <tabulka> musí být zadána. Argumenty ve funkci ORDERBY() se zadávají v páru výraz určený pro řazení, a způsob řazení. Pokud není zadán způsob řazení, defaultní nastavení je ASC – vzestupné. Ve funkci ORDERBY() můžeme určit řazení pomocí hodnot ve sloupci nebo řazení na základě hodnoty výrazu, tedy i měřítka. Řazení může být sestupné (DESC, 0 nebo FALSE) nebo vzestupné (ASC, 1 nebo TRUE). V argumentu určenému pro způsob řazení ve funkci ORDERBY() můžeme specifikovat také zařazení prázdných BLANK hodnot. Například výraz DESC BLANK FIRST seřadí hodnoty sestupně, ale prázdné hodnoty budou vždy v pořadí na začátku.
Prázdné hodnoty (blanks) - volitelný argument, jehož vyplněním můžeme specifikovat, jak bude zacházeno při řazení s prázdnými hodnotami BLANK. Toto nastavení platí pro všechny řazené hodnoty, kromě těch, pro které je případně určen vlastní způsob zacházení s prázdnými hodnotami ve funkci ORDERBY() v argumentu <řazení> , viz předchozí odstavec. Zadat můžeme FIRST – prázdné hodnoty vždy na začátku, LAST – prázdné hodnoty vždy na konci, DEFAULT – pro STRING hodnoty jsou hodnoty BLANK vždy na prvním místě, pro numerické hodnoty jsou BLANK hodnoty vždy mezi nulou a zápornými čísly.
Rozdělení podle (partitionBy) - volitelný argument vyhrazený pro funkci PARTITIONBY(), pomocí které můžeme určit rozdělení tabulky z argumentu <tabulka> na dílčí tabulky. Pokud je tabulka rozdělena na dílčí tabulky podle sloupce zadaného ve funkci PARTITIONBY(), řazení je vytvořeno odděleně v rámci každé dílčí tabulky.
Spárovat s (matchBy) - volitelný argument vyhrazený pro funkci MATCHBY(). Ve funkci MATCHBY() můžeme specifikovat sloupce, které budou použity pro určení aktuálního řádku, ve kterém je celá funkce RANK() vyhodnocena. Tento argument je užitečný zejména při použití v počítaných sloupcích, kde se díky specifikaci jednoho nebo více sloupců z tabulky, ve které je funkce RANK() použita, můžeme vyhnout problémům s cyklickou závislostí. Použití funkce MATCHBY() může také v některých příkladech pomoci při optimalizaci výpočtů určených pro měřítka.
Relativně velký počet argumentů, spolu s tím že v některých argumentech se používají další funkce se svými vlastními argumenty, může funkci RANK() na první pohled řadit mezi složité DAX funkce. Používání funkce RANK() však bude ve většině případů jednodušší, než se na první pohled může zdát. Důvodem je nepovinné používání jednotlivých argumentů, kdy některé z volitelných argumentů slouží pouze pro řešení specifických situací.
Velkou výhodou funkce RANK(), oproti starší funkci RANKX() je, že pořadí může být ve funkci RANK() vytvořeno na základě více hodnot. Vytvoření pořadí na základě více hodnot bylo pomocí funkce RANKX() velmi složité a pro sezónní autory DAX výpočtů téměř neřešitelné.
Další výhodou funkce RANK() je, že oproti funkci RANKX() se při určení pořadí nepoužívají přímo hodnoty určené pro řazení, ale již seřazené hodnoty z tabulky v argumentu <tabulka> . Díky tomuto odlišnému přístupu může být funkce RANK() v některých typech výpočtů rychleji vyhodnocena (nutno vždy změřit, výkon závisí vždy na mnoha různých faktorech), ale hlavně již nebude docházet k problému při určení pořadí na základě hodnot ve formátu DECIMAL (hodnoty s plovoucí desetinnou čárkou), jako tomu bylo u funkce RANKX(), kde bylo často nutné použít pro řazení hodnoty jiného datového typu nebo hodnoty ve formátu DECIMAL zaokrouhlovat.
Funkce RANK() také, na rozdíl od funkce RANKX(), nevrací výsledky v řádku souhrnů nebo v mezisoučtech řádků. V řádku souhrnů a v mezisoučtech řádků obvykle nedává smysl zobrazovat pořadí, protože jednoduše nemáme zobrazené položky určené pro seřazení. Pokud funkce RANK() nenajde ve vnějším kontextu jeden řádek odpovídající řádku v zadané tabulce v argumentu <tabulka> , jednoduše vrátí hodnotu BLANK.
Po relativně dlouhém, i když pořád v rámci možností zjednodušeném úvodu k funkci RANK() se můžeme přesunout k příkladům. Postupně si ukážeme použití funkce RANK() pro vytvoření pořadí podle hodnoty měřítka, dále vytvoření pořadí na základě hodnot ze dvou měřítek a použití funkce RANK() pro vytvoření pořadí v počítaném sloupci.
Příklady použití funkce RANK
V prvním jednoduchém příkladu si můžeme porovnat použití funkce RANK() se starší funkcí RANKX(). Uvažujme například následující vizuál Matice, kde jsou v řádcích kategorie a podkategorie produktů, a v hodnotách měřítko [Prodeje].
Měřítko:
Při použití nové funkce RANK() bude výpočet vypadat následovně.
Měřítko:
Pokud obě nová měřítka vložíme do původního vizuálu Matice, na první pohled můžeme vidět typický problém funkce RANKX(), který se při použití funkce RANK() nevyskytuje.
Funkce RANKX() vrací pořadí také v řádcích vizuálu, které nejsou součástí tabulky v prvním argumentu této funkce. Například v řádku s kategorií "Components" vrací měřítko [Pořadí podkategorií (RANKX)] číslo 4. Ve funkci RANKX() není tabulka, která by obsahovala souhrnný řádek se všemi podkategoriemi v kategorii "Components". Typickou vlastností funkce RANKX() je v takovém případě přidat chybějící hodnotu do hodnot určených pro řazení, a přiřadit aktuálnímu řádku odpovídající pořadové číslo. Díky tomu funkce RANKX() vrací pořadí také v řádcích souhrnů a v mezisoučtech řádků. Toto chování funkce RANKX() může být užitečné v určitých specifických situacích. V příkladech podobných předchozímu je však hodnota v řádku souhrnů nebo v mezisoučtech řádků, kterou vrací funkce RANKX(), obvykle nahrazována hodnotou BLANK pomocí jednoduché podmínky ve funkci IF().
Funkce RANK() naproti tomu nevrací hodnoty v řádcích souhrnů a v mezisoučtech řádků, a tak tato dodatečná práce není při použití funkce RANK() nutná.
V dalším příkladu si můžeme ukázat použití funkce PARTITIONBY() v argumentu <rozdělit podle> . Tento argument slouží k rozdělení zadané tabulky na dílčí tabulky podle určitého sloupce, a pořadí tak bude vytvořeno lokálně v rámci každé z rozdělených tabulek. Pokud bychom chtěli vytvořit pořadí podkategorií samostatně v rámci každé kategorie, můžeme použít následující výpočet.
Měřítko:
Nové měřítko si můžeme vložit do původního vizuálu spolu s měřítkem [Pořadí podkategorií (RANK)] a porovnat si jejich výsledky.
Velkou výhodou funkce RANK() je také možnost vytvořit pořadí na základě více hodnot, jak si ukážeme v dalším příkladu.
Určení pořadí na základě více hodnot pomocí funkce RANK
V tomto příkladu můžeme použít stejný vizuál s kategoriemi a podkategoriemi produktů, do kterého si navíc přidáme měřítko [Počet produktů], které má následující definici.
Měřítko:
Na základě měřítka [Počet produktů] můžeme vytvořit opět pořadí jednotlivých podkategorií, tentokrát následujícím způsobem.
Měřítko:
Pokud obě nová měřítka vložíme do vizuálu Matice s kategoriemi a podkategoriemi v řádcích, výsledek bude vypadat následovně.
Pokud bychom chtěli pro určení pořadí přidat další kritérium, řekněme například sumu inkasovanou za prodeje produktů v dané podkategorii, můžeme přidat další argumenty do funkce ORDERBY() následujícím způsobem.
Měřítko:
Pokud měřítko [Pořadí (podle počtu produktů a prodejů)] vložíme do vizuálu, výsledek bude vypadat následovně.
Obdobě bychom mohli na druhé místo do funkce ORDERBY() přidat také sloupec z tabulky zadané ve druhém argumentu funkce RANK(). Pokud bychom například místo měřítka [Prodeje] vložili na druhé místo do funkce ORDERBY() sloupec 'Product'[Subcategory], podkategorie se stejným počtem produktů by se seřadili dodatečně podle abecedního pořadí.
Použití funkce RANK() v počítaném sloupci má svá specifika, a proto si ukážeme ještě jeden příklad s vytvořením pořadí produktů v tabulce 'Product'.
Funkce RANK v počítaném sloupci
Pokud bychom chtěli vytvořit pořadí produktů v tabulce 'Product' podle sumy utracené za tyto produkty, první varianta výpočtu by mohla vypadat následovně.
Počítaný sloupec:
Takto vytvořený počítaný sloupec ale nebude fungovat, protože jsme tímto výpočtem vyvolali v tabulce 'Product' cyklickou závislost.
Tento argument je vyhrazen pro funkci MATCHBY(). Do funkce MATCHBY() můžeme v tomto případě vložit buď odkaz na sloupec s jedinečnými hodnotami pro každý řádek tabulky, nebo názvy více sloupců, jejichž kombinace odpovídá granularitě v tabulce zadané ve druhém argumentu funkce RANK().
Následující výpočet v počítaném sloupci tabulky 'Product' s použitím funkce MATCHBY() již bude vracet očekávaný výsledek.
Počítaný sloupec:
Nyní je tabulka ve druhém argumentu funkce RANK() spárována s vnější tabulkou, ve které je počítaný sloupec vytvořen, pouze prostřednictvím sloupce 'Product'[ProductKey], čímž se úspěšně vyhneme cyklické závislosti.
Shrnutí
Funkce RANK() by měla být ve většině případů první volba pro vytvoření pořadí. Jedná se v současné době o nejlepší dostupnou funkci pro vytvoření pořadí. Na rozdíl od starší funkce RANKX() můžeme pomocí funkce RANK() jednoduše vytvořit pořadí také na základě více hodnot. S funkcí RANK() také odpadá problém se zaokrouhlováním čísel ve formátu DECIMAL, který se objevoval ve funkci RANKX(). Na druhou stranu, funkci RANKX() bude stále potřeba použít v jedné specifické situaci, kdy budeme chtít zařadit hodnoty zobrazené ve vizuálu nebo v tabulce mezi hodnoty z jiné tabulky.
Informace o dalších DAX funkcích můžete najít na stránce Jazyk DAX. Praktické příklady vytvořené v Power BI můžete najít na stránce DAX příklady nebo na stránce Power BI.
Oficiální Microsoft dokumentace funkce RANK:
https://learn.microsoft.com/cs-cz/dax/rank-function-dax
Komentáře
Okomentovat