Vztahy mezi tabulkami na základě rozsahu v jazyku DAX a Power BI

Typickým příkladem relace mezi dvěma tabulkami na základě rozsahu je zařazení hodnot z jedné tabulky do kategorií, kdy kategorie jsou uložené v jiné tabulce, a daná kategorie je definována spodní a horní hranicí, do které musí cílová hodnota patřit. V tomto přípěvku si ukážeme dva možné přístupy jak pomocí jazyka DAX vyjádřit vztah mezi dvěma tabulkami na základě rozsahu v případě, kdy v tabulkách nemáme k dispozici potřebné klíče pro vytvoření standardní relace.

(Tento článek je v revizi.)

V Tabulárním modelu můžeme vytvořit relace s různou kardinalitou (1:M, M:N, 1:1) a s různým nastavením směru propagace filtrů (jednosměrné, obousměrné). Existují ale situace, kdy vytvořit relaci mezi tabulkami jednoduše není možné, protože zkrátka nemáme k dispozici potřebné klíče (sloupce určené pro vytvoření relací).

Uvažujme například situaci, kdy máme model který zachycuje mimo jiné prodeje produktů přes různé obchodní kanály. Jedná se o nadnárodní firmu, která má celou řadu možností jak prodávat své produkty. Prodeje se tak uskutečňují v různých zemích přes kamenné prodejny, přes obchodníky, přes katalogové prodeje nebo přes internet. Informace o obchodech jsou uloženy ve standardní dimenzní tabulce, kdy každý obchod má v této tabuce jeden záznam. 

Po několika letech existence daného modelu vznikne požadavek ohodnotit obchody podle jejich výkonnosti. Do modelu tak bude přidána nová tabulka, která bude obsahovat kategorie obchodů. Kategorie jsou čtyři (výborná, dobrá, špatná, velmi špatná) a daný obchod bude do vybrané kategorie zařazen podle objemu prodejů, který bude přepočítán na počet zaměstnanců. Problémem je že mezi tabulkou s obchody a tabulku s kategoriemi neexistuje relace, a nemáme k dispozici ani potřebné klíče pro vytvoření odpovídající relace. V tabulce s obchody totiž není kategorie pro každý objem prodejů, ale jsou zde kategorie pro určitý rozsah prodejů. Každá kategorie má spodní a horní hranici, a aby byl obchod zařazen do kategorie, musí být přepočítaný objem prodejů na jednoho zaměstnance v rozsahu daným minimální a maximální hodnotou pro danou kategorii.

V následující části si ukážeme dva možné přístupy pro vyřešení výše popsaného problému. V prvním přistupu nebudeme tvořit relaci, ale v tabulce s obchody si vytvoříme nový počítaný sloupec, do kterého si pomocí DAX výrazu uložíme název kategorie, do které příslušný obchod spadá. Toto řešení je ideální pokud nám stačí do tabulky s obchody přidat jeden sloupec. V případě, kdy by tabulka s kategoriemi obsahovala celou řadu sloupců, které jsou užitečné pro tvorbu reportů, je obvykle lepší vytvořit mezi tabulkymi relaci. Proto si ukážeme i druhý přístup, ve kterém si pomocí jazyka DAX vytvoříme potřebné klíče pro vytvoření standardní relace.

Poznámka pro autora: Odstranit klíče z tabulky hodnocneí, a v druhém příkladu je vytvořit pomocí ROWNUMBER().

Vztahy mezi tabulkami na základě rozsahu a jazyk DAX

Celý příklad je vytvořen ve cvičném Power BI souboru Contoso Sales Sample for Power BI Desktop.pbix. Soubor s řešením je dostupný ke stažení níže pod tímto příspěvkem. 

V použitém modelu je mimo jiné dimenzní tabulka s názvem 'Stores', která obsahuje jeden záznam pro každý obchod. Přes tyto obchody se pak prodávají produkty fiktivní společnosti "Contoso". Záznamy o prodaných produktech jsou v tabulce s názvem 'Sales'.

V tabulce 'Stores' jsou čtyři typy obchodů – Catalog, Online, Reseller a Store. V příkladech uvedených v tomto příspěvku budeme chtít vyhodnotit, zda obchody v dané kategorii dosahovali určité výkonnosti v konkrétním roce. Výkonnost bude vyhodnocena na základě objemu prodejů uskutečněných přes každý obchod. Objem prodejů přes konkrétní obchod ještě přepočítáme na počet zaměstnanců v daném obchodě. Dále budeme chtít při zařazení obchodu do výkonnostní kategorie rozlišovat, o jaký typ obchodu se jedná, protože nemá smysl porovnávat výkonnost přepočtenou na zaměstnance například Online obchodů s kamennými prodejnami. Obchody budeme řadit do čtyř kategorií podle výkonnosti (Velmi špatná, Špatná, Dobrá, Výborná). Tabulka, kterou budeme používat pro hodnocení výkonnosti obchodů, bude obsahovat čtyři řádky pro každý typ obchodu a sloupce s ID řádku, typem obchodu, kategorií výkonnosti, minimální hranicí pro zařazení do dané kategorie a maximální hranici. 

Tabulku s hodnocením si můžeme v Power BI vytvořit jednoduše kliknutím na tlačítko "Nová tabulka", které najdeme na kartě "Modelování". Do okna editoru vzorců pak stačí vložit následující DAX výraz, který vygeneruje výše uvedenou tabulku.

Počítaná tabulka:

Hodnocení =

DATATABLE

(

    "ID", INTEGER,

    "TypObchodu", STRING,

    "Výkonnost na zaměstnance", STRING,

    "MIN Prodeje na zaměstnance", CURRENCY,

    "MAX Prodeje na zaměstnance", CURRENCY,

    {

        {1, "Store", "Velmi špatná", 0, 50000},

        {2, "Store", "Špatná", 50000, 100000},

        {3, "Store", "Dobrá", 100000, 200000},

        {4, "Store", "Výborná", 200000, 999999999},

        {5, "Online", "Velmi špatná", 0, 7000000},

        {6, "Online", "Špatná", 7000000, 14000000},

        {7, "Online", "Dobrá", 14000000, 24000000},

        {8, "Online", "Výborná", 24000000, 999999999},

        {9, "Reseller", "Velmi špatná", 0, 5000000},

        {10, "Reseller", "Špatná", 5000000, 7000000},

        {11, "Reseller", "Dobrá", 7000000, 9000000},

        {12, "Reseller", "Výborná", 9000000, 999999999},

        {13, "Catalog", "Velmi špatná", 0, 800000},

        {14, "Catalog", "Špatná", 800000, 1600000},

        {15, "Catalog", "Dobrá", 1600000, 2200000},

        {16, "Catalog", "Výborná", 2200000, 999999999}

   

    }

)

Nová tabulka se po vytvoření stane součástí modelu, nicméně mezi tabulkou 'Hodnocení' a tabulkou 'Stores' nyní nemůžeme vytvořit relaci. V tabulce 'Stores' neexistuje klíč, který bychom mohli použít pro vytvoření relace s tabulkou 'Hodnocení'. Abychom mohli přiřadit hodnocení ke konkrétnímu obchodu, musíme nejdříve:

• Vypočítat prodeje přes daný obchod v konkrétním roce.
• Přepočítat prodeje přes daný obchod na jednoho zaměstnance.
• Zjistit typ daného obchodu (Catalog, Online, Store, Reseller).
• Na základě prodejů na zaměstnance a typu obchodu přiřadit výkonnost obchodu z tabulky 'Hodnocení'.

Jakmile dokážeme přiřadit hodnocení ke každému obchodu v tabulce 'Stores', čeká nás ještě jedno rozhodnutí. V první variantě můžeme přiřadit hodnocení obchodu přímo do tabulky 'Stores' pomocí počítaného sloupce (denormalizace). Druhou možností je vytvořit  v tabulce 'Stores' počítaný sloupec s ID hodnotou odpovídajícího řádku z tabulky 'Hodnocení', na základě kterého vytvoříme mezi tabulkami 'Stores' a 'Hodnocení' relaci (normalizace). Logika obou výpočtů bude podobná, nicméně v případě, kdy budeme chtít vytvořit mezi tabulkami relaci, musíme pro výpočet počítaného sloupce použít jiné DAX funkce, než které použijeme v první variantě výpočtu.

Vytvoření nového sloupce na základě vyhodnocení více podmínek

Nový počítaný sloupec si vytvoříme v jednom kroku, kdy si jednotlivé mezivýpočty uložíme do proměnných. Nejdříve určíme rok, ve kterém chceme prodeje vyhodnotit. Jako rozhodující rok pro vyhodnocení výkonnosti můžeme použít rok, který bude předcházet poslednímu roku, ve kterém došlo k alespoň nějakým prodejům, abychom měli jistotu, že budeme pracovat s uzavřeným obdobím. Následně tento rok přidáme do filtru při vytvoření součtu prodejů pro jednotlivé obchody. Počet zaměstnanců v daném obchodě načteme do proměnné jednoduše ze sloupce 'Stores'[EmployeeCount]. Následně vydělíme sumu prodejů ve vybraném roce počtem zaměstnanců v daném obchodu. Poslední hodnotu, kterou potřebujeme zjistit, je typ obchodu, který je uložený ve sloupci 'Stores'[StoreType]. Jakmile máme v proměnných k dispozici všechny hodnoty potřebné pro sestavení filtrovacích podmínek  v tabulce 'Hodnocení', můžeme načíst tuto tabulku zafiltrovanou pro aktuální obchod. Tato tabulka by měla v každém řádku tabulky 'Stores' obsahovat pouze jeden řádek, což si před vyhodnocením výpočtu ještě ověříme jednoduchým logickým výrazem. Celý výpočet může vypadat následovně.

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

Výkonnost =

VAR PredchoziRok = YEAR(MAX(Sales[DateKey])) - 1

VAR ProdejePredchoziRok =

CALCULATE

(

    SUM(Sales[SalesAmount]),

    'Calendar'[Year] = PredchoziRok

)

VAR PocetZamestnancu = Stores[EmployeeCount]

VAR ProdejeNaZamestnance = DIVIDE(ProdejePredchoziRok, PocetZamestnancu)

VAR TypObchodu = Stores[StoreType]

VAR TabulkaDoFitru =

FILTER

(

    'Hodnocení',

    'Hodnocení'[TypObchodu] = TypObchodu &&

    'Hodnocení'[MIN Prodeje na zaměstnance] <= ProdejeNaZamestnance &&

    'Hodnocení'[MAX Prodeje na zaměstnance] > ProdejeNaZamestnance

)

VAR FiltrObsahujeJedenRadek =

    COUNTROWS(TabulkaDoFitru) = 1

VAR Hodnoceni =

     CALCULATE

    (

        VALUES('Hodnocení'[Výkonnost na zaměstnance]),

        TabulkaDoFitru

    )

VAR Vysledek =

IF

(

    FiltrObsahujeJedenRadek,

    Hodnoceni,

    "Nezjištěno"

)

RETURN

    Vysledek

Výsledkem je nový počítaný sloupec v tabulce 'Stores'. Tento sloupec pak můžeme použít v Power BI vizuálech a podívat se tak například, kolik obchodů patří do každé kategorie.

Měřítko:

Počet obchodů = COUNTROWS(Stores)

Na obrázku níže můžeme vidět, že nejvíce obchodů má hodnocení výkonnosti "Dobrá", naopak hodnocení výkonnosti "Špatná" má pouze 17 obchodů z celkového množství 306.

Stejně tak se můžeme podívat v každé výkonnostní kategorii na konkrétní obchody.

Pokud bychom chtěli propojit tabulku 'Hodnocení' s tabulkou 'Stores' pomocí relace, musíme pro vytvoření počítaného sloupce v tabulce 'Stores' s klíčem určeným pro relaci použít trochu jiný postup.

Vytvoření relace na základě rozsahu

Pokud bychom chtěli vytvořit relaci mezi tabulkou 'Hodnocení' a tabulkou 'Stores', musíme namísto slovního hodnocení ve sloupci 'Hodnocení'[Výkonnost na zaměstnance] načítat do nového počítaného sloupce jedinečné hodnoty ze sloupce 'Hodnocení'[ID]. Dále pak musíme v kódu nového počítaného sloupce určeného pro vytvoření relace nahradit ty funkce, které pracují s hodnotami z tabulky 'Hodnocení' a mohly by potencionálně vracet prázdné hodnoty BLANK. Takovou funkcí je například funkce VALUES(), kterou v kódu nahradíme funkcí DISTINCT(), která prázdné hodnoty BLANK ignoruje. Logika výpočtu pak zůstává stejná, změny oproti původnímu kódu jsou popsány také v komentářích.

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

ID Hodnocení =

VAR PredchoziRok = YEAR(MAX(Sales[DateKey])) - 1

VAR ProdejePredchoziRok =

CALCULATE

(

    SUM(Sales[SalesAmount]),

    'Calendar'[Year] = PredchoziRok

)

VAR PocetZamestnancu = Stores[EmployeeCount]

VAR ProdejeNaZamestnance = DIVIDE(ProdejePredchoziRok, PocetZamestnancu)

VAR TypObchodu = Stores[StoreType]

VAR TabulkaDoFitru =

FILTER

(

    'Hodnocení',

    'Hodnocení'[TypObchodu] = TypObchodu &&

    'Hodnocení'[Prodeje na zaměstnance MIN] <= ProdejeNaZamestnance &&

    'Hodnocení'[Prodeje na zaměstnance MAX] > ProdejeNaZamestnance

)

VAR FiltrObsahujeJedenRadek =

    COUNTROWS(TabulkaDoFitru) = 1

VAR Hodnoceni =

     CALCULATE

    (

        DISTINCT('Hodnocení'[ID]),

        //sloupec 'Hodnocení'[ID]

        //namísto sloupce 'Hodnocení'[Výkonnost na zaměstnance]

        //funkce DISTINCT místo původní VALUES

        TabulkaDoFitru

    )

VAR Vysledek =

IF

(

    FiltrObsahujeJedenRadek,

    Hodnoceni

    //odebrán text s alternativním výsledkem

    //v počítaném sloupci s datovým typem Whole number

    //nemůže být text

)

RETURN

    Vysledek

Na základě nového počítaného sloupce již můžeme vytvořit relaci mezi tabulkami 'Hodnocení' a 'Stores'.

V takto vytvořeném modelu můžeme s tabulkou 'Hodnocení' pracovat jako s kteroukoliv jinou  dimenzní tabulkou.

Shrnutí

V Tabulárním modelu se můžeme setkat se situací, kdy mezi dvěma tabulkami existuje vztah, který je vyjádřen určitým rozsahem. V takovém případě můžeme hodnoty ze zdrojové tabulky denormalizovat, to znamená pomocí počítaných sloupců je přidat do cílové tabulky. Druhou možností je vytvořit relaci mezi tabulkami. Pokud chceme tabulky se vztahem na základě rozsahu propojit pomocí relace, nesmíme v počítaném sloupci, který je určený pro vytvoření relace mezi tabulkami, používat funkce, které mohou vracet hodnoty BLANK z tabulky, se kterou chceme relaci vytvořit.

Stáhnou soubor s řešením.
č. 44