Práce s Parent/Child hierarchií v Power BI a DAX

Práce s Parent/Child hierarchií v Power BI a DAX

Tento příspěvek obsahuje ukázku, jakým způsobem můžeme pomocí jazyka DAX transformovat Parent-Child hierarchii (hierarchii rodič-potomek) do podoby klasické hierarchie, kdy je každá úroveň v hierarchii uložená v samostatném sloupci.

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

Parent-Child hierarchie je hierarchie uložená typicky ve dvou sloupcích, kdy každý potomek má ve stejném řádku odkaz pouze na svého nejbližšího rodiče.

Práce s Parent/Child hierarchií v Power BI a DAX 2

Na obrázku výše můžete vidět ukázku takovéto hierarchie. Zvýrazněné buňky pak znázorňují všechny nadřízené jednoho konkrétního zaměstnance se jménem Jae Pak. Tento zaměstnanec s hodnotou 291 ve sloupci 'Employee'[EmployeeKey], stejně jako kterýkoliv jiný, má ve svém řádku odkaz pouze na svého nejbližšího přímého nadřízeného, kterým je zaměstnanec s číslem 290. Pokud bychom chtěli zjistit jméno tohoto zaměstnance s číslem 290, musíme se přesunout do jeho řádku kde zjistíme, že zaměstnanec číslo 290 se jmenuje Amy Alberts a tento zaměstnanec má také svého nadřízeného, tentokrát s číslem 277. Takto bychom mohli pokračovat dále až k zaměstnanci na nejvyšší úrovni, kterým je Ken Sánchez.

Tímto způsobem jsou často vytvořeny hierarchie uložené v databázových serverech, například na SQL Serveru. Vztah mezi jednotlivými úrovněmi hierarchie je v takto vytvořené tabulce vyjádřen takzvanou Self-Reference relací, tedy relací, kdy jeden sloupec z dané tabulky odkazuje na druhý sloupec ze stejné tabulky. Tento způsob uložení dat v databázových serverech má své opodstatněné důvody, kterými se ale v tomto příspěvku nemusíme zabývat. 

Power BI, nebo obecně Tabulární model, který běží na pozadí stále více analytických nástrojů, ale nepodporuje Self-Reference relace. Na druhou stranu, v jazyku DAX jsou k dispozici funkce, které umožňují práci s Parent-Child hierarchií a pomocí kterých můžeme Parent-Child hierarchii upravit do podoby klasické hierarchie.

Práce s Parent/Child hierarchií v Power BI a DAX 3

V následující části příspěvku si ukážeme postup, jak Parent-Child hierarchii takzvaně zploštit do podoby klasické hierarchie, zachycené na obrázku výše. S takto upravenou strukturou již můžeme pohodlně pracovat v Power BI nebo dalších analytických nástrojích.

Přeměna Parent-Child hierarchie na klasickou hierarchii v Power BI

V použitém cvičném souboru, který si můžete stáhnout pod odkazem uvedeným níže pod tímto příspěvkem, jsou pro zjednodušení pouze dvě tabulky. Tabulka prodejů 'Sales' a tabulka zaměstnanců 'Employee'. Tabulka zaměstnanců 'Employee' obsahuje tři sloupce - jedinečný identifikátor každého zaměstnance, jedinečný identifikátor nadřízeného zaměstnance a jméno aktuálního zaměstnance.

Práce s Parent/Child hierarchií v Power BI a DAX 4

Pokud bychom v takto vytvořené tabulce použili sloupec se jmény zaměstnanců v reportu ve vizuálu Tabulka, spolu s částkou za prodeje produktů přes tyto zaměstnance, výsledný vizuál by mohl vypadat například následovně.

Práce s Parent/Child hierarchií v Power BI a DAX 5

Na obrázku výše můžeme vidět zaměstnance z tabulky 'Employee' a jejich prodeje. V použitém vizuálu se ale vůbec nezobrazují nadřízení těchto zaměstnanců, protože žádný z nadřízených nemá žádné prodeje. Dále v takto vytvořené tabulce chybí informace o tom, jak je daný zaměstnanec zařazený v organizační struktuře. Cílem příkladu v tomto příspěvku bude vytvořit následující pohled na prodeje jednotlivých zaměstnanců.

Práce s Parent/Child hierarchií v Power BI a DAX 6

Na obrázku výše již můžeme vidět, jakého má každý zaměstnanec nadřízeného a nově také sumu za prodeje produktů u každého vedoucího. Tato celková částka za prodeje u vedoucích zaměstnanců se skládá z jednotlivých dílčích částek vyprodukovaných zaměstnanci, kteří jsou v hierarchii pod aktuálním nadřízeným. Pro zaměstnance Ken Sánchez, který je v hierarchii na nejvyšším místě a je tedy nadřízeným pro každého dalšího zaměstnance, ať už přímím nebo nepřímím, vrací měřítko [Prodeje] celkovou sumu prodejů za všechny zaměstnance.

Prvním krokem ke zploštění Parent-Child hierarchie bude vytvoření pomocného sloupce v tabulce 'Employee', který bude obsahovat celou cestu aktuálního zaměstnance v hierarchii. Takovýto sloupec můžeme vytvořit pomocí funkce PATH(). Funkce PATH() má dva povinné argumenty. Prvním argumentem je jedinečný identifikátor tabulky, kterým je v našem příkladu sloupec 'Employee'[EmployeeKey] s jedinečným identifikátorem každého zaměstnance. Druhým argumentem funkce PATH() je sloupec s identifikátorem rodiče pro aktuální záznam v tabulce. V našem příkladu se bude jednat o sloupec s identifikátory nadřízených, tedy o sloupec 'Employee'[ParentEmployeeKey].

Počítaný sloupec:

Hierarchie celá cesta = PATH(Employee[EmployeeKey], Employee[ParentEmployeeKey])

Funkce PATH() projde rekurzivně celou tabulku a sestaví pro každého zaměstnance celou jeho cestu v hierarchii následujícím způsobem.

Práce s Parent/Child hierarchií v Power BI a DAX 7

Na základě nového pomocného sloupce 'Employee'[Hierarchie celá cesta] již můžeme vytvořit jednotlivé úrovně hierarchie. Každá úroveň bude v samostatném sloupci. Pro první úroveň můžeme použít následující výpočet.

Počítaný sloupec:

Úroveň 1 =
VAR AktualniUroven = 1
VAR ZamestnanecKey = PATHITEM( Employee[Hierarchie celá cesta], AktualniUroven, INTEGER)
VAR ZamestnanecJmeno = LOOKUPVALUE( Employee[Name], Employee[EmployeeKey], ZamestnanecKey)
RETURN
    ZamestnanecJmeno

Ve funkci PATHITEM() v proměnné ZamestnanecKey je v prvním argumentu odkaz na pomocný sloupec 'Employee'[Hierarchie celá cesta], který obsahuje celou cestu v hierarchii každého zaměstnance. Z této cesty pak budeme chtít v první úrovni vybrat zaměstnance na prvním místě. Výsledkem proměnné ZamestnanecKey tedy bude identifikátor zaměstnance na prvním místě v hierarchii. Tato hodnota pak bude použita v proměnné ZamestnanecJmeno, kde pomocí funkce LOOKUPVALUE() načteme jméno zaměstnance s tímto jedinečným identifikátorem.

Podobným způsobem vytvoříme také další sloupce s dalšími úrovněmi. Jedinou změnou bude první proměnná, ve které budeme měnit číslo aktuální úrovně podle toho, pro jakou úroveň je aktuální sloupec vytvořen.

Počítané sloupce:

Úroveň 2 =
VAR AktualniUroven = 2
VAR ZamestnanecKey = PATHITEM(Employee[Hierarchie celá cesta], AktualniUroven, INTEGER)
VAR ZamestnanecJmeno = LOOKUPVALUE(Employee[Name], Employee[EmployeeKey], ZamestnanecKey)
RETURN
    ZamestnanecJmeno

Úroveň 3 =
VAR AktualniUroven = 3
VAR ZamestnanecKey = PATHITEM(Employee[Hierarchie celá cesta], AktualniUroven, INTEGER)
VAR ZamestnanecJmeno = LOOKUPVALUE(Employee[Name], Employee[EmployeeKey], ZamestnanecKey)
RETURN
    ZamestnanecJmeno

Úroveň 4 =
VAR AktualniUroven = 4
VAR ZamestnanecKey = PATHITEM(Employee[Hierarchie celá cesta], AktualniUroven, INTEGER)
VAR ZamestnanecJmeno = LOOKUPVALUE(Employee[Name], Employee[EmployeeKey], ZamestnanecKey)
RETURN
    ZamestnanecJmeno

V tomto příkladu je maximální hloubka hierarchie 4. To znamená, že pro každou úroveň jsme vytvořili jeden sloupec, celkem čtyři sloupce. V reálném modelu by pak bylo vhodné doplnit do rezervy další sloupce, protože hloubka hierarchie se může v čase měnit.

Nyní zbývá vložit jednotlivé sloupce postupně od úrovně jedna po úroveň čtyři do vizuálu Matice. Pokud do vizuálu vložíme také měřítko [Prodeje], výsledek bude vypadat následovně.

Práce s Parent/Child hierarchií v Power BI a DAX 8

Nyní se již ve vizuálu zobrazují také nadřízení a v řádku každého nadřízeného můžeme vidět sumu za prodeje produktů, které byly prodány přes jeho podřízené zaměstnance. Zbývá dořešit jeden problém, který se vyskytne v případě, kdy je hloubka hierarchie aktuálního zaměstnance menší než je maximální hloubka hierarchie. Tento problém můžeme vidět například u zaměstnance jménem Pamela Ansman-Wolfe.

Práce s Parent/Child hierarchií v Power BI a DAX 9

Pamela Ansman-Wolfe je zaměstnanec přímo podřízený nejvyššímu zaměstnanci v hierarchii a současně pod sebou nemá žádné podřízené. Pamela Ansman-Wolfe proto končí v hierarchii na úrovni číslo dvě. Ostatní úrovně jsou pro tohoto zaměstnance prázdné, to znamená mají hodnotu BLANK. 

V některých analytických nástrojích, jako například v SSAS Tabularním modelu, je pro tento případ k dispozici vlastnost, pomocí které můžeme v hierarchii skrýt řádky, které mají prázdné hodnoty BLANK. Tato vlastnost se jmenuje "Hide Members" a řešením je zvolit nastavení "Hide blank members". V Power BI není v době psaní tohoto příspěvku toto nastavení k dispozici, a proto si ještě vytvoříme měřítko, pomocí kterého budeme skrývat řádky s prázdnými hodnotami BLANK.

Poznámka: Výše zmíněná vlastnost Hide Members se nastavuje pro objekt Hierarchie, a ne pro jednotlivé sloupce které tvoří tuto hierarchii.

Toto pomocné měřítko určené pouze pro použití v rámci konkrétní hierarchie bude obsahovat jednoduchou logiku. V měřítku budeme ověřovat, jestli jsme v aktuální úrovni hierarchie a jestli je hodnota v aktuální úrovni rovna hodnotě BLANK. Pokud ano, jednoduše nahradíme měřítko [Prodeje] prázdnou hodnotou BLANK, a takovýto řádek se následně vůbec nebude ve vizuálu zobrazovat. Procházet budeme postupně každou úroveň hierarchie, a proto ověření pro jednotlivé úrovně vložíme do funkce SWITCH().

Měřítko:

Prodeje (v hierarchii) =
SWITCH
(
    TRUE(),
    ISINSCOPE(Employee[Úroveň 5]) && SELECTEDVALUE(Employee[Úroveň 5]) = BLANK(), BLANK(),
    ISINSCOPE(Employee[Úroveň 4]) && SELECTEDVALUE(Employee[Úroveň 4]) = BLANK(), BLANK(),
    ISINSCOPE(Employee[Úroveň 3]) && SELECTEDVALUE(Employee[Úroveň 3]) = BLANK(), BLANK(),
    ISINSCOPE(Employee[Úroveň 2]) && SELECTEDVALUE(Employee[Úroveň 2]) = BLANK(), BLANK(),
    ISINSCOPE(Employee[Úroveň 1]) && SELECTEDVALUE(Employee[Úroveň 1]) = BLANK(), BLANK(),
    [Prodeje]
)

Pokud nové měřítko vložíme do původního vizuálu matice namísto měřítka [Prodeje], řádky obsahující prázdné hodnoty, tedy řádky se zaměstnanci jejichž hloubka hierarchie je menší než je maximální hloubka hierarchie, se již nebudou zobrazovat.

Práce s Parent/Child hierarchií v Power BI a DAX 10

V reálném modelu by ještě zbývalo vytvořit z jednotlivých úrovní Hierarchii (objekt). Tu je možné v Power BI vytvořit například pomocí kontextové nabídky, která se zobrazí po kliknutí pravým tlačítkem myši na některý ze sloupců, který chceme vložit do hierarchie.

Práce s Parent/Child hierarchií v Power BI a DAX 11

Následně bychom mohli přidat do nově vytvořené Hierarchie všechny sloupce pro každou úroveň, a ostatní pomocné sloupce v tabulce zaměstnanců skrýt, aby se nezobrazovali v uživatelských nástrojích.

Shrnutí

Přeměna Parent-Child hierarchie do podoby klasické hierarchie, kde je každá úroveň uložena v samostatném sloupci, není díky dostupnosti speciálních DAX funkcí vytvořených za tímto účelem příliš složitá. Míra složitosti při práci s Parent-Child hierarchiemi se ale může výrazně zvyšovat s každou malou odchylkou nebo s každým drobným požadavkem na změnu nebo výjimku, což může v reálném modelu nastat velmi rychle. V takovýchto případech je nutné buď upravit měřítka, která jsou použita v kontextu hodnot ze sloupců v hierarchii, nebo definici jednotlivých sloupců vytvořených za účelem zploštění Parent-Child hierarchie. Postup zobrazený v tomto příspěvku ale může být i v těchto situacích považován za základ, od kterého se můžeme následně posunout k řešení specifických situací, které mohou být jedinečné pouze pro konkrétní model.

Další praktické příklady můžete najít na stránce DAX příklady nebo na stránce Power BI. Základní teorii k jazyku DAX a popis vybraných DAX funkcí pak najdete na stránce Jazyk DAX. Video návody pak můžete najít na Youtubovém kanále pod tímto odkazem.

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

Komentáře