Relace na základě hodnot z více sloupců

V Power BI, nebo obecně v Tabulárním modelu, můžeme vytvořit relaci mezi tabulkami na základě hodnot z jednoho sloupce v každé tabulce. Občas se ale můžeme setkat se situací, kdy je vztah mezi tabulkami založený na hodnotách z více sloupců. Tento příspěvek popisuje jeden z přístupů, jak můžeme vytvořit relaci mezi tabulkami na základě hodnot z více sloupců pomocí vytvoření počítaných sloupců.

Příklad vytvoření relace na základě hodnot z více sloupců

Příklad vytvoření relace na základě hodnot z více sloupců si můžeme nasimulovat ve cvičném Power BI souboru Adventure Works DW 2020.pbix. Tento soubor obsahuje datový model fiktivní společnosti zabývající se prodejem jízdních kol, sportovního oblečení a dalšího příslušenství. Pro zjednodušení se zaměříme pouze tabulku 'Sales' a tabulku 'Product'. Záznamy o prodejích jednotlivých produktů jsou v tabulce 'Sales'. Tabulka 'Product'  obsahuje jednotlivé produkty, včetně jejich zařazení do kategorií, podkategorií atd. Tabulka 'Product' je s tabulkou 'Sales' propojena relací na základě jedinečného identifikátoru každého produktu. 

Tabulka 'Product' obsahuje jedinečné záznamy popisující jednotlivé produkty. To znamená, že každý produkt je v tabulce 'Product' pouze jednou. Naopak v tabulce 'Sales' se každý produkt může vyskytovat vícekrát, podle toho kolikrát došlo k prodeji daného produktu. 

Stávající model si rozšíříme o tabulku, která bude obsahovat záznamy o slevových akcích. Slevové akce se budou vztahovat k určitému dni v roce a budou platit pouze pro produkty z určitých kategorií. Takovou tabulku si můžeme vytvořit pomocí DAX kódu. V Power BI souboru vybereme možnost "Nová tabulka" na kartě "Modelování". 

Do okna editoru vzorců vložíme následující DAX kód, který vytvoří tabulku s osmi řádky a čtyřmi sloupci.

Počítaná tabulka:

Slevové akce =

SELECTCOLUMNS

(

    {

("Bikes" , DATE(2019, 9, 29), 0.1, "Podzimní výprodej kol"),

("Bikes" , DATE(2019, 9, 30), 0.2, "Podzimní výprodej kol"),

("Bikes" , DATE(2019, 10, 30), 0.4, "Podzimní výprodej kol"),

("Bikes" , DATE(2019, 10, 31), 0.4, "Podzimní výprodej kol"),

("Clothing", DATE(2019, 7, 1), 0.2, "Výprodej letního oblečení"),

("Clothing", DATE(2019, 7, 2), 0.2, "Výprodej letního oblečení"),

("Clothing", DATE(2019, 7, 3), 0.4, "Výprodej letního oblečení"),

("Clothing", DATE(2019, 7, 4), 0.4, "Výprodej letního oblečení")

    },

    "Kategorie" , [Value1],

    "Datum akce", [Value2],

    "Sleva %" , [Value3],

"Název akce", [Value4]

)

Nová tabulka obsahuje názvy kategorií, datum slevové akce, název akce a slevu, která je v daný den uplatněna na prodané produkty z konkrétní kategorie.

Pokud bychom chtěli vytvořit relaci mezi tabulkou 'Sales' a novou tabulkou 'Slevové akce', musíme vytvořit vztah na základě hodnot ze dvou sloupců. Pokud bychom vytvořili relaci pouze na základě kategorie produktů, uplatňovali bychom slevu na všechny produkty z dané kategorie bez ohledu na datum slevové akce. Pokud bychom vytvořili relaci pouze na základě data, ve kterém dochází ke slevové akci, uplatňovali bychom slevu na všechny produkty prodané v dané dny, bez ohledu na kategorii produktů. Relace proto musí být vytvořena na základě kombinace hodnot ze dvou sloupců, a to ze sloupce 'Slevové akce'[Kategorie] a sloupce 'Slevové akce'[Datum akce].

Relace mezi tabulkami na základě hodnot z více sloupců

Jednou z možností, jak vytvořit relaci mezi tabulkami na základě hodnot z více sloupců, je tyto hodnoty sloučit do jednoho sloupce a vytvořit tak složený klíč. Složený klíč v tabulce 'Slevové akce' můžeme vytvořit pomocí následujícího DAX výrazu.

Počítaný sloupec v tabulce 'Slevove akce':

SlevovaAkceKey = 'Slevové akce'[Datum akce] & "-" & 'Slevové akce'[Kategorie

Nový sloupec je nyní součástí tabulky, a můžeme ho použít k vytvoření relace, stejně jako kterýkoliv jiný sloupec.

Stejný klíč ale budeme potřebovat také v tabulce 'Sales'. Tabulka 'Sales' neobsahuje názvy kategorií jednotlivých produktů. Tyto názvy najdeme v tabulce 'Product'. Tabulka 'Product' je s tabulkou 'Sales' ve vztahu MANY-TO-ONE na straně ONE. Pokud tedy vytváříme počítaný sloupec v tabulce 'Sales', můžeme přistupovat k hodnotám v tabulce 'Product' pomocí funkce RELATED(). Počítaný sloupec se složeným klíčem v tabulce 'Sales' tak může vypadat následovně.

Počítaný sloupec v tabulce 'Sales':

SlevovaAkceKey = Sales[Order Date] & "-" & RELATED('Product'[Category])

Následně můžeme mezi tabulkou 'Sales'  a tabulkou 'Slevové akce' vytvořit relaci na základě nových počítaných sloupců.

Nyní můžeme novou tabulku obsahující informace o slevových akcích používat stejně jako kteroukoliv jinou dimenzní tabulku. Můžeme si například vytvořit několik měřítek, a podívat se na výsledky slevových akcí.

Měřítka:

Počet produktů = SUM(Sales[Order Quantity])

Prodeje = SUM(Sales[Sales Amount])

Prodeje započítaná sleva =

SUMX

(

Sales,

Sales[Sales Amount] * (1-RELATED('Slevové akce'[Sleva %]))

)

Sleva = [Prodeje] - [Prodeje započítaná sleva]

Měřítka vložíme do vizuálu Matrix, spolu s názvy slevových akcí a dny akcí v řádcích vizuálu.

V tabulce je možné vidět, kolik produktů bylo prodáno v každé slevové akci, včetně poskytnuté slevy. Výše uvedená tabulka je zafiltrovaná pouze na prodeje patřící do jednotlivých slevových akcí. Bez tohoto filtru by vizuál vypadal následovně.

Bez použitého filtru tabulka obsahuje jeden řádek navíc. Tento řádek zahrnuje všechny ostatní prodeje, které proběhly mimo slevové akce. Prázdný řádek v řádcích vizuálu obvykle znamená, že v dimenzní tabulce nemáme hodnoty odpovídající hodnotám ve faktové tabulce. V tomto příkladu je to logické, protože slevová akce probíhá pouze v určité dny a pro vybrané produkty. 

Shrnutí

Pokud potřebujeme vytvořit vazbu mezi tabulkami na základě hodnot z více sloupců, můžeme vytvořit sloupce se složenými klíči, a na základě těchto sloupců následně vytvořit relaci mezi tabulkami. Jak už to v jazyku DAX obvykle bývá, jedná se pouze o jednu z více možností, které máme k dispozici a která má své výhody i nevýhody. Další možností by mohla být denormalizace požadovaných hodnot z dimenzní tabulky, například pomocí funkce LOOKUPVALUE().  Tento postup je popsán v samostatném příspěvku.

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