Při tvorbě datového modelu může nastat situace, kdy potřebujeme vyjádřit vztah mezi dvěma tabulkami, kdy v jedné tabulce jsou hodnoty pro relaci v jednom sloupci, a ve druhé tabulce jsou hodnoty určené k vytvoření relace ve více sloupcích. V tomto příspěvku si na příkladu popíšeme, jak tuto situaci můžeme vyřešit v Power BI pomocí jazyka DAX.
Uvažujme například tabulku zákazníků, kdy každý zákazník je z určitého státu (jeden sloupec) a současně z určitého regionu (druhý sloupec). Ve druhé tabulce máme zaměstnance zodpovědné za zákaznický servis. Každý zaměstnanec může mít na starosti zákazníky z jednoho celého regionu nebo zákazníky pouze z určitého státu v určitém regionu. Oblast, kterou má zaměstnanec na starosti, je v jednom sloupci, ať už se jedná o celý region nebo pouze o stát v regionu.
Výše popsaný vztah nemůžeme vyjádřit jednoduše pomocí relace. V následující části si proto ukážeme jednu z možností, jak vyjádřit výše popsaný vztah pomocí DAX kódu. V první části vytvoříme řešení v Power BI. Ve druhé části si pak ukážeme, jak stejný problém vyřešit ve starších verzích Tabulárního modelu, kde nemusí být dostupné všechny nejnovější DAX funkce.
Vztah mezi tabulkami na základě hodnot z jednoho nebo druhého sloupce
Celý příklad si můžeme vytvořit ve cvičném souboru Adventure Works DW 2020.pbix, který je volně dostupný ke stažení na internetu. Odkaz na stažení souboru s řešením pak můžete najít níže pod příspěvkem.
Začněme tabulkou 'Customer', ve které jsou záznamy o zákaznících, včetně jejich adres. Každý zákazník pak bydlí v určité zemi/regionu a v určitém státě/provincii.
Dva zvýrazněné sloupce na obrázku výše jsou sloupce, pomocí kterých budeme potřebovat propojit zákazníky se zaměstnanci. Tabulka, ve které jsou zaměstnanci a oblasti, za kterou tito zaměstnanci zodpovídají, pak vypadá následovně.
Prvních pět řádku ve výše zobrazené tabulce obsahuje ve sloupci 'Zákaznický servis'[Oblast] hodnoty, které bychom potřebovali porovnat se sloupcem 'Customer'[Country-Region]. Dalších pět řádků ve stejném sloupci pak obsahuje názvy států v Austrálii, to znamená že daní pracovníci nemají na starosti celou Austrálii, ale pouze určité státy. Těchto pět zbylých hodnot bychom potřebovali propojit se sloupcem 'Customer'[State-Province].
Stejnou tabulku si můžeme vytvořit jednoduše kliknutím na možnost "Nová tabulka" na kartě "Modelování". Do Editoru vzorců pak stačí vložit následující DAX výraz, který stejnou tabulku vygeneruje.
Počítaná tabulka:
Celou úlohy bychom mohli vyřešit více různými způsoby. V následující části si ukážeme relativně jednoduchý způsob, jak přiřadit zaměstnance z konfigurační tabulky ke konkrétním zákazníkům v tabulce 'Customer'.
Vytvoření filtru na základě hodnot z jednoho nebo z druhého sloupce v Power BI
Zaměstnance můžeme přiřadit ke konkrétnímu zákazníkovi za předpokladu, že v konfigurační tabulce najdeme pro zákazníka konkrétního zaměstnance, který má na starosti zákazníkovu zemi/region anebo zaměstnance, který má na starosti zákazníkův stát/provincii.
V tabulce zákazníků 'Customer' si vytvoříme nový počítaný sloupec, kde budeme chtít do každého řádku vložit jméno zaměstnance zodpovědného za vybraného zákazníka. V novém počítaném sloupci proto vytvoříme výpočet, který bude filtrovat tabulku 'Zákaznický servis' podle hodnot v aktuálním řádku tabulky 'Customer'. Hodnoty pro filtr budou buď ze sloupce 'Customer'[State-Province] nebo ze sloupce 'Customer'[Country-Region]. Výraz pro nový počítaný sloupec v tabulce 'Customer' může vypadat následovně.
Počítaný sloupec:
Výsledkem je nový počítaný sloupec v tabulce 'Customer', který obsahuje jména zaměstnanců, kteří mají na starosti dané zákazníky. Následně můžeme tento sloupec použít ve vizuálech Power BI, stejně jako jakýkoliv jiný sloupec. Můžeme se například podívat, kolik má každý zaměstnanec přiřazených zákazníků.
Na obrázku výše můžeme vidět, kolik má každý zaměstnanec přiřazených zákazníků. Poslední řádek s hodnotou "Více nebo žádný výsledek" patří záznamu v tabulce 'Customer', který nemá přiřazenou adresu. Jedná se o řádek, který zastupuje všechny zákazníky, kteří nakupují v kamenných prodejnách, a nejsou zaregistrování pro online nákupy, tudíž neznáme ani jejich adresu.
Jak můžeme vidět na obrázku výše, můžeme se podívat také na konkrétní zákazníky, které má daný zaměstnanec na starosti. Vizualizací bychom samozřejmě mohli vytvořit více podle požadavků uživatelů reportu.
V následující části je pak uveden postup, jak dosáhnout stejného výsledku ve starších verzích Tabulárního modelu, kde nemusí být dostupné všechny dříve použité funkce.
Přiřazení hodnot do tabulky bez použití proměnných
V některých starších verzích Tabulárního modelu nemůžeme definovat proměnné, a nemůžeme použít ani funkci SELECTEDVALUE(). Jedna z možností, jak dosáhnout stejného výsledku bez použití proměnných, může vypadat následovně.
Pozn.: V některých verzích Power Pivot v Excelu jsou argumenty funkcí odděleny středníky namísto čárek.
Počítaný sloupec:
Ve výše uvedeném výpočtu spoléháme na vlastnost, která v rámci vnořených iterací umožňuje přístup ke sloupcům ze všech tabulek, které jsou součástí výpočtu. V rámci iterace tabulky 'Zákaznický servis' ve funkci FILTER() tak můžeme přistupovat ke sloupcům z tabulky 'Customer', ve které je celý výpočet vyvolán v rámci definice nového počítaného sloupce, a která v tomto případě tvoří vnější iteraci.
Počítaný sloupec:
Ve výše uvedené verzi stejného výpočtu přistupujeme k hodnotám ze sloupců tabulky 'Customer' využitím funkce EARLIER(). Přístupů, jak dosáhnout stejného výsledku, může být samozřejmě více.
Shrnutí
Cílem příkladu bylo popsat pomocí jazyka DAX vztah, který je mezi dvěma tabulkami založen na hodnotách z jednoho sloupce v první tabulce, a na hodnotách ze dvou sloupců ve druhé tabulce. Tato situace může být možná trochu netypická při tvorbě modelu, nicméně pokud bychom se se stejnou situací setkali, řešením může být jazyk DAX a několik řádků kódu. Pokud bychom podobnou situaci řešili v Excelu, může být nahrání dat do modelu a vytvoření počítaného sloupce v cílové tabulce možná i jednodušší, než práce s vnořenými Excel funkcemi SVYHLEDAT a KDYŽ. Současně je v tomto případě uživatel alespoň částečně odstíněn od celého řešení, které je vytvořeno na pozadí souboru v Power Pivotu.
Dobrý den,
OdpovědětVymazatděkuji moc za super článek.
Řešil jste někdy případ toho, že vytvoření nového sloupce s klíčem nebylo vhodné z hlediska velkého objemu dat? Přemýšlím nad tím, jak bych to mohla obejít...
Hezký den.
Ester
Dobrý den.
OdpovědětVymazatV první řadě děkuji za komentář a připomenutí tohoto článku který je na čase revidovat :)
Pokud je ten sloupec v tabulce na straně Many, tzn. hodnoty v tomto sloupci se často opakují, bylo by určitě lepší vytvořit tento složený klíč přímo ve zdroji dat nebo alespoň v Power Query pokud je to možné - výrazně lepší komprese než u počítaného sloupce. Za určitých okolností může být nahrazena klasická relace relací virtuální, ale to má obvykle více negativ než pozitiv, zvláště při práci s velkými objemy dat. Začal bych tedy tím vytvořením klíčů přímo ve zdroji a porovnal bych velikost sloupců. Pokud by to nepomohlo tak bych asi přemýšlel o možnostech agregace faktové tabulky, to znamená vyměnit určitou ztrátu detailu za zmenšení tabulky. Samozřejmě to je všechno teorie, nevím jaké máte reálně možnosti…