Parametrické tabulky v Power BI a DAX

Parametrické tabulky, někdy nazývané také jako Odpojené tabulky jsou tabulky, které nemají relace s ostatními tabulkami v modelu a jsou vytvořeny za účelem ovlivňování výsledků výpočtů na základě aktuálně hodnoty Parametru. Výhodou Parametrických tabulek je jejich jednoduchost použití a především možnost vytvořit jeden výpočet, který bude vracet různé výsledky podle toho, který Parametr v Parametrické tabulce uživatel zvolí.

V tomto příspěvku si ukážeme dva příklady s použitím Parametrických tabulek. V prvním příkladu si ukážeme jak použít Parametrickou tabulku pro převod zobrazovaných jednotek v reportu. V druhém příkladu si ukážeme použití Parametrické tabulky pro rozdělení prodávaných produktů do speciálních kategorií, kdy každý produkt může patřit do jedné nebo více kategorií.

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

Všechny příklady v tomto příspěvku jsou vytvořeny ve cvičném Power BI souboru Adventure Works DW 2020.pbix, který je volně dostupný ke stažení na internetu. Soubor s řešením je dostupný ke stažení níže pod tímto příspěvkem.

V použitém Power BI souboru je dále vytvořeno měřítko [Prodeje], které má následující definici.

Měřítko:

Prodeje = SUM(Sales[Sales Amount])

Měřítko [Prodeje] bude vracet sumu za prodané produkty v aktuálním kontextu vyhodnocení. Pokud toto měřítko vložíme do vizuálu Tabulka s kategoriemi produktů v řádcích, tak výchozí report pro naše příklady může vypadat následovně.

Nyní máme vše připravené a můžeme se přesunout k prvnímu příkladu.

Příklady použití Parametrických tabulek v Power BI

Z technického hlediska jsou Parametrické tabulky stejné tabulky jako kterékoliv jiné tabulky nahrané v modelu. Každý záznam v Parametrické tabulce má však svůj speciální význam. Struktura Parametrických tabulek je obvykle taková, že v jednom sloupci jsou uloženy názvy Parametrů, a v dalším jednom nebo více sloupcích jsou uloženy hodnoty Parametrů. Uživatel reportu pak typicky zvolí konkrétní Parametr (řádek v Parametrické tabulce) výběrem názvu Parametru v Průřezu, a ve výpočtech budeme jako autoři reportů používat hodnoty tohoto vybraného Parametru. Díky tomu můžeme vytvořit jeden výpočet, který bude vracet různé výsledky, v závislosti na volbě uživatele.

Parametrickou tabulku můžeme do modelu nahrávat přímo ze zdroje dat, například z SQL Serveru, z Excelu nebo z jakéhokoliv jiného zdroje, odkud pocházejí data. Parametrickou tabulku si můžeme vytvořit také přímo v Power BI jako počítanou tabulku. V tomto příspěvku jsou všechny Parametrické tabulky vytvořeny právě jako počítané tabulky přímo v Power BI, nicméně samotný způsob vytvoření Parametrické tabulky nemá žádný vliv na její následné používání ve výpočtech.

Změna zobrazených jednotek v Power BI reportu

Cílem tohoto příkladu je umožnit uživateli vybrat si, v jakých jednotkách chce zobrazit hodnoty prodejů v použitém vizuálu. Na výběr bude mít zobrazení hodnot bez měřítka, zobrazení v tisících Kč, v milionech Kč nebo v miliardách Kč.

Pozn.: Částky v použitém Power BI souboru jsou v uvedeny USD. V této části příspěvku však pro zjednodušení budeme o částkách mluvit jako o Kč.

Prvním krokem je vytvoření samotné Parametrické tabulky. Tato tabulka může být nahrána přímo ze zdrojové databáze, může být vytvořena v Power Query nebo přímo v Power BI. V tomto příkladu si můžeme tabulku vytvořit pomocí DAX výrazu. Tabulka bude mít dva sloupce a čtyři řádky. V prvním sloupci bude název Parametru, který budeme používat ve vizuálu Průřez. Ve druhém sloupci budou hodnoty, které budou následně použity ve výpočtu v měřítku. 

Počítaná tabulka:

Jednotky =

SELECTCOLUMNS

(

    {

        ("Kč", 1),

        ("Tis. Kč", 1000),

        ("Mil. Kč", 1000000),

        ("Mld. Kč", 1000000000)

    },

    "Název", [Value1],

    "Hodnota", [Value2]

)

Výsledkem výše uvedeného DAX kódu je nová tabulka, která je nyní součástí modelu jako kterákoliv jiná tabulka. Tato nová tabulka není propojená s žádnou jinou tabulkou pomocí relací.

Jakmile máme tabulku nahranou v modelu, můžeme hodnoty ze sloupce 'Jednotky'[Název] vložit do průřezu.

V této chvíli vybraná hodnota v Průřezu nijak neovlivňuje zobrazované hodnoty v měřítku [Prodeje]. Výpočet, který bude převádět hodnotu měřítka [Prodeje] podle zvolené jednotky v Průřezu může vypadat následovně.

Měřítko:

Prodeje (Jednotky) =

VAR HodnotaParametru =

    SELECTEDVALUE('Jednotky'[Hodnota], 1)

VAR Vypocet =

    DIVIDE([Prodeje], HodnotaParametru)

RETURN

    Vypocet

Pokud nové měřítko vložíme do původního vizuálu a v Průřezu vybereme například "Tis. Kč", měřítko [Prodeje (Jednotky)] bude vracet hodnotu měřítka [Prodeje] převedenou na tisíce.

V případě že zvolíme v průřezu možnost "Mil. Kč", hodnoty budou zobrazeny v milionech Kč.

Pokud uživatel nevybere žádnou hodnotu v Průřezu nebo pokud by vybral více hodnot, výsledkem bude hodnota měřítka [Prodeje] v originálních jednotkách, protože ve funkci SELECTEDVALUE() bude použita v tomto případě alternativní hodnota 1 ze druhého argumentu této funkce.

V dalším příkladu si ukážeme použití Parametrické tabulky pro rozdělení produktů do speciálních kategorií, kde jeden produkt může patřit do jedné, více anebo do všech kategorií.

Rozdělení produktů do kategorií podle katalogové ceny produktu

V použitém Power BI souboru je v tabulce 'Product' celkem 397 produktů. Každý produkt má v tabulce 'Product' uvedenou mimo jiné také katalogovou cenu, která je ve sloupci 'Product'[List Price].

Rozpětí katalogových cen jednotlivých produktů je velmi široké. Na obrázku výše můžeme vidět ve druhém řádku cenu 2,29. Nejdražší produkt zobrazený na obrázku výše má cenu 3 578,27. Shodou okolností jde o nejmenší a největší částku, které se vyskytují ve sloupci 'Product'[List Price].

Představme si situaci, kdy bychom v jednom vizuálu chtěli zobrazit prodeje za produkty v různých cenových kategoriích. Jednotlivé kategorie však budou určeny pouze pomocí horní hranice. Budeme tak chtít zobrazit prodeje za produkty například s cenou do 50, prodeje za produkty s cenou do 250, prodeje za produkty s cenou do 1000, vše v jednom vizuálu. Díky tomuto pohledu si budeme moci porovnat podíl tržeb za jednotlivé produkty v různých cenových kategoriích. Uživatel si dále bude moci zvolit, kterou jednu nebo více kategorií chce ve vizuálu zobrazit.

Úskalí takto popsaného reportu je v tom, že jeden produkt může patřit do více nebo dokonce do všech kategorií. Uvažujme například produkt "Patch Kit/8 Patches" s cenou 2,29. Tento produkt bude jistě patřit do kategorie produktů s cenou do 50. Stejný produkt bude ale také v kategorii produktů s cenou do 250, dále v kategorii produktů s cenou do 1000, a ve všech dalších. Z tohoto důvodu nemůžeme produkty rozdělit do kategorií v počítaném sloupci nebo pomocí relace, protože nemůžeme v jednom sloupci přiřadit jednomu produktu více kategorií. Elegantním řešením může být právě Parametrická tabulka.

Tato tabulka bude obsahovat opět dva sloupce, název a hodnotu. Hodnota bude představovat horní hranici pro katalogovou cenu produktů v dané kategorii a bude následně použita pro vytvoření logického filtru v měřítku.

Počítaná tabulka:

Kategorie =

SELECTCOLUMNS

(

    {

        ("Do 50", 50),

        ("Do 250", 250),

        ("Do 1000", 1000),

        ("Do 2500", 2500),

        ("Všechny", MAX('Product'[List Price]) + 1 )

    },

    "Název", [Value1],

    "Hodnota", [Value2]

)

Jednotlivé kategorie obsahují pevně danou horní hranici pro cenu produktů. V posledním řádku v kategorii "Všechny" je použita funkce MAX() pro načtení nejvyšší hodnoty ze sloupce 'Product'[List Price] abychom měli jistotu, že v této kategorii budou dostupné opravdu všechny produkty.

Nyní je nová tabulka opět součástí modelu a můžeme hodnoty z této tabulky používat ve vizuálech a ve výpočtech. Měřítko, které bude vracet prodeje za produkty v dané cenové kategorii může vypadat následovně.

Měřítko:

Prodeje (Kategorie) =

VAR HodnotaParametru =

    SELECTEDVALUE(Kategorie[Hodnota])

VAR Vypocet =

    CALCULATE

    (

        [Prodeje],

        'Product'[List Price] < HodnotaParametru

    )

RETURN

    Vypocet

Měřítko [Prodeje (Kategorie)] bude v proměnné HodnotaParametru načítat aktuálně vybranou hodnotu ze sloupce 'Kategorie'[Hodnota], která bude následně použita pro sestavení filtru ve funkci CALCULATE(). Výsledkem měřítka pak budou prodeje pouze za produkty s katalogovou cenou menší než je aktuální hodnota Parametru.

Nové měřítko si můžeme opět vložit do vizuálu s kategoriemi produktů v řádcích. Do sloupců vizuálu Matice přidáme sloupec 'Kategorie'[Název] a tento sloupec použijeme také v Průřezu. Následně budeme schopni porovnávat prodeje za produkty v různých kategoriích.

Na obrázku výše tak můžeme například vidět, že v kategorii "Bikes" nejsou žádné prodané produkty s cenou do 250. Naopak v kategorii "Clothing" jsou všechny prodané produkty v kategorii do 250. Pokud v Průřezu vybereme například kategorii "Do 1000" a "Všechny", výsledek bude vypadat následovně.

Na obrázku výše nyní můžeme vidět, že v kategorii "Accessories" a v kategorii "Clothing" jsou všechny prodané produkty v kategorii do 1000. Naopak u kategorií "Bikes" a "Components" tvoří prodeje produktů s cenou do 1000 pouze část celkových tržeb.

Shrnutí

Parametrické tabulky mají široké spektrum využití. Příklady uvedené v tomto příspěvku ani zdaleka nepokrývají možnosti, které se autorům Power BI reportů otevírají při práci s Parametrickými tabulkami. Princip použití Parametrických tabulek je ale vždy velmi podobný. Cílem je načítat hodnoty Parametrů, a tyto hodnoty používat ve výpočtech v měřítku. Takto vytvořená měřítka pak vrací různé hodnoty podle toho, který Parametr je aktuálně použitý ve výpočtu. Díky Parametrickým tabulkám můžeme také tvořit reporty a výpočty, které obsahují dodatečnou logiku, která nemůže být vytvořena pomocí stávajících relací mezi tabulkami.

Další příklady můžete najít na stránce DAX - Příklady nebo na stránce Power BI.

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