Jazyk DAX obvykle používáme k rozšíření modelu o vlastní výpočty přes měřítka, vizuální výpočty, počítané sloupce nebo počítané tabulky. Velkou výhodou jazyka DAX je jeho univerzálnost. Jakmile se naučíme používat jazyk DAX jako programovací jazyk, měli bychom být relativně snadno schopni používat tento jazyk také pro psaní DAX dotazů. Při psaní DAX dotazů můžeme používat stejné funkce jako při psaní měřítek nebo počítaných sloupců, pouze s tím rozdílem, že výsledkem dotazu musí být vždy tabulka, která je vyhodnocena za klíčovým slovem EVALUATE.
K tomuto tématu je k dispozici také video:
DAX dotazy můžeme používat k načtení dat z Tabulárního modelu ve stále více nástrojích, včetně Excelu nebo Power BI. Existují také specializované nástroje pro psaní DAX dotazů, jako je například DAX Studio. DAX Studio je zdarma a obsahuje mnoho dodatečných funkcí, které jsou velmi užitečné také při optimalizaci DAX výpočtů nebo při čtení metadat z Tabulárního modelu.
V tomto příspěvku ale budeme pracovat přímo v Power BI Desktop a pro psaní DAX dotazů použijeme nástroj "Zobrazení dotazů DAX", což je asi nejjednodušší způsob, jak začít s psaním vlastních DAX dotazů. V následující části příspěvku si nedříve popíšeme syntaxi DAX dotazů a následně přejdeme k jednoduchým příkladům.
Dotazy v jazyku DAX a příkaz EVALUATE
Každý DAX dotaz musí obsahovat klíčový příkaz EVALUATE. EVALUATE má následující syntaxi s jediným povinným argumentem, kterým je tabulka nebo funkce vracející tabulku.
Syntaxe EVALUATE:
V rámci DAX dotazu můžeme definovat objekty na jeho začátku, a také ovlivňovat výsledek dotazu pomocí klauzulí ORDER BY nebo START AT. Definice objektů a příkazy pro ovlivnění výsledku dotazů jsou ale volitelné. Nejjednodušší DAX dotaz proto může vypadat například následovně.
DAX dotaz:
Výsledkem je tabulka 'Product' se všemi sloupci a všemi řádky tak, jak je nahraná v použitém modelu.
DAX dotaz který vrací všechny sloupce a všechny řádky z tabulky nahrané v modelu není příliš užitečný. Při psaní vlastních DAX dotazů se obvykle snažíme replikovat samotné vizuály které se zobrazují uživatelům. Důvodem může být například samotný vývoj konkrétních měřítek nebo ladění výkonu. Každé měřítko ale vrací skalární hodnotu (jednu hodnotu v každé buňce nebo v každém bodě vizuálu), zatímco výsledkem DAX dotazu musí být tabulka.
Výsledkem DAX dotazu musí být tabulka
Nejjednodušší možností, jak vypsat výsledek měřítka samostatně v rámci DAX dotazu, je použít takzvaný konstruktor tabulky. Konstruktor tabulky se tvoří pomocí složených závorek. Následující dotaz vrací výsledek měřítka [Prodeje] v tabulce s jedním řádkem a jedním sloupcem.
DAX dotaz:
Výsledkem je tabulka s jedním řádkem a jedním sloupcem, který je automaticky pojmenovaný názvem Value.
Předchozí dotaz vracel sumu za všechny prodané produkty bez jakéhokoliv filtru, který by na měřítko působil. Pokud bychom chtěli zobrazit výsledek měřítka [Prodeje] v kontextu například jednotlivých barev, ve kterých se produkty prodávaly, můžeme použít následující DAX dotaz.
DAX dotaz:
Ve výše uvedeném dotazu nejdříve pomocí funkce VALUES() načteme všechny barvy produktů ze sloupce 'Product'[Color]. Funkce VALUES() vytvoří tabulku s jedním sloupcem, a k této tabulce přidáváme nový sloupec pomocí funkce ADDCOLUMNS(), a to sloupec s hodnotami měřítka [Prodeje], které je vyhodnoceno pro každou barvu samostatně. Výsledná tabulka je pak seřazena sestupně podle hodnoty měřítka [Prodeje].
Jak bylo možné vidět na předcházejícím příkladu, pomocí DAX dotazu si můžeme relativně jednoduše nasimulovat jak bude měřítko vyhodnoceno v určitém kontextu, a to pomocí jednoduchých funkcí jako jsou funkce VALUES() nebo ADDCOLUMNS().
Definice objektů v rámci DAX dotazu
V DAX dotazech můžeme v části DEFINE vytvořit měřítka, počítané sloupce, tabulky nebo proměnné. Všechny objekty definované v rámci DAX dotazu jsou dostupné pouze v době jeho vyhodnocení. V následujícím DAX dotazu je například v části DEFINE vytvořené měřítko [Náklady] a proměnná vProdejeVnejsi.
Měřítko definované v části DEFINE musí mít před názvem samotného měřítka, který se uvádí v hranatých závorkách, uveden název některé z existujících tabulek v modelu. Měřítko [Náklady] se po jeho definici bude v rámci DAX dotazu chovat jako kterékoliv jiné měřítko.
Proměnná vProdejeVnejsi pak obsahuje hodnotu měřítka [Prodeje], které je součástí modelu. Každá proměnná definovaná v části DEFINE může být použita kdekoliv v dotazu, bez nutnosti použít klíčové slovo RETURN.
DAX dotaz:
Na obrázku níže je zobrazen výsledek předcházejícího dotazu. Měřítko [Náklady] definované v rámci DAX dotazu se chová jako kterékoliv jiné měřítko vytvořené v modelu. To můžeme využít buď při vývoji měřítek nebo při jejich optimalizaci, aniž bychom museli měnit definici měřítka přímo v modelu.
Zaujmout může také rozdíl mezi vyhodnocením měřítka [Prodeje] ve sloupci [mProdeje] a mezi hodnotou měřítka [Prodeje], která je uložena v proměnné vProdejeVnejsi a v tabulce na obrázku níže zobrazena ve sloupci [vProdejeVnejsi]. Zatímco měřítko je vyhodnoceno v kontextu, ve kterém je použito, proměnná je vyhodnocena v kontextu, ve kterém je definována. Ve sloupci [vProdejeVnejsi] proto vidíme hodnotu měřítka [Prodeje] za všechny barvy, protože v době definice proměnné vProdejeVnejsi nepůsobí na měřítko [Prodeje] žádné filtry.
Pokud definujeme v části DEFINE měřítko se stejným názvem, jako je již dříve vytvořené měřítko v modelu, v rámci DAX dotazu má přednost měřítko vytvořené v části DEFINE před měřítkem se stejným názvem vytvořeným v modelu. V následujícím dotazu tak bude originální měřítko [Prodeje], které je součástí modelu, přepsáno měřítkem vytvořeným v rámci dotazu.
DAX dotaz:
Výsledkem měřítka [Prodeje] je nyní v každém řádku tabulky hodnota 700, protože měřítko definované v rámci DAX dotazu má přednost před měřítkem se stejným názvem vytvořeným v modelu.
Nahrazovat hodnotu měřítka konstantou není příliš užitečné. Tento příklad slouží pouze pro zdůraznění možnosti přepisovat definici měřítka vytvořeného v modelu v rámci DAX dotazu. Tato vlastnost je totiž velmi užitečná zejména při optimalizaci měřítek, kdy si v rámci DAX dotazu můžeme vytvořit různé varianty výpočtů, a až po jejich odladění nahradit novou verzi výpočtu přímo v definici měřítka v modelu.
Jak je možné vidět na předchozím příkladu, měřítko definované v rámci DAX dotazu má přednost před měřítkem se stejným názvem, které je součástí modelu. Pokud by ovšem bylo měřítko [Prodeje] použito uvnitř jiných měřítek, v rámci těchto měřítek by byla použita originální definice měřítka [Prodeje], tak jak je definována v rámci modelu. Jinak řečeno, definice měřítka v rámci DAX dotazu přepíše v době tohoto dotazu originální měřítko pouze pokud je toto měřítko voláno přímo v dotazu, a ne nepřímo v rámci jiných měřítek.
S DAX dotazy je úzce spojena funkce SUMMARIZECOLUMNS(), která je často použita pro generování DAX dotazů samotnými Power BI vizuály. Funkce SUMMARIZECOLUMNS() je velmi komplexní a současně specifická funkce, v rámci které můžeme používat další funkce, například funkce pro zobrazení výsledků různých mezisoučtů nebo souhrnů. V následující části příspěvku si proto ukážeme pouze základní použití funkce SUMARIZECOLUMNS() při psaní vlastních DAX dotazů.
DAX dotazy a funkce SUMMARIZECOLUMNS
Funkce SUMMARIZECOLUMNS() je funkce vytvořená primárně pro účely DAX dotazů. V době psaní tohoto příspěvku je používání funkce SUMMARIZECOLUMNS() v definici měřítka omezené, nicméně je veřejně známo že v blízké budoucnosti budou tato omezení odstraněna a funkce SUMMARIZECOLUMNS() bude mít daleko širší využití. V příspěvku věnovanému DAX dotazům ale má funkce SUMMARIZECOLUMNS() své místo, a to proto že téměř každý vizuál používá při generování DAX dotazů právě tuto funkci.
Začít můžeme opět jednoduchým příkladem s barvami produktů a prodeji za produkty v těchto barvách.
DAX dotaz:
Funkce SUMMARIZECOLUMNS() ve výše uvedeném DAX dotazu načte hodnoty ze sloupce 'Product'[Color] a následně vyhodnotí v kontextu filtru každé barvy měřítko [Prodeje], jehož hodnoty budou součástí výsledné tabulky.
Na výše uvedeném obrázku vidíme pouze 9 barev, protože pro barvu "Gray", která je také ve sloupci 'Product'[Color], nevrací měřítko [Prodeje] žádnou hodnotu, respektive vrací prázdnou hodnotu BLANK. Funkce SUMMARIZECOLUMNS() totiž v základním nastavení nevrací ty řádky, pro které vrací všechny výpočty prázdnou hodnotu BLANK. Protože funkce SUMMARIZECOLUMNS() je použita na pozadí téměř každého vizuálu, tento efekt je dobře známý autorům Power BI reportů. Automatický filtr řádků pro které vrací všechny výpočty prázdné hodnoty BLANK můžeme v našem příkladu zrušit vložením měřítka [Prodeje] do funkce IGNORE(). Funkce IGNORE() je funkce kterou použije také Power BI při generování DAX dotazu v případě, pokud vybereme u některého ze sloupců vložených ve vizuálu možnost "Zobrazit položky, které neobsahují data".
Ve funkci SUMMARIZECOLUMNS() můžeme v prvních argumentech použít sloupce z různých tabulek. Za těmito sloupci pak mohou následovat filtry ve formě tabulek (filtry ve funkci SUMMARIZECOLUMNS() nemůžou být predikáty) a dále libovolný počet výpočtů. Následující DAX dotaz pak bude vracet roky, měsíce a kategorie produktů a hodnoty měřítka [Prodeje], vše pouze pro rok 2019.
DAX dotaz:
Část výsledku výše uvedeného dotazu můžeme vidět na následujícím obrázku.
Jak už bylo uvedeno výše, funkce SUMMARIZECOLUMNS() je komplexní funkce, v rámci které můžeme používat další funkce. Uvažujme například následující jednoduchý report, kde jsou v řádcích vizuálu matice roky a měsíce, ve sloupcích kategorie produktů, a nad vizuálem matice je průřez s vybraným rokem 2019. Červeně zvýrazněné buňky obsahují hodnoty měřítka [Prodeje], které jsou shodou okolností výsledkem předchozího DAX dotazu.
Power BI musí oproti našemu zjednodušenému DAX dotazu navíc získat hodnoty pro jednotlivé souhrny. K tomuto účelu jsou pro funkci SUMMARIZECOLUMNS() k dispozici speciální funkce, jejichž použití můžeme vidět v následujícím DAX dotazu.
DAX dotaz:
Výše uvedený DAX dotaz je zjednodušená verze DAX dotazu, který je vygenerovaný Power BI vizuálem matice zobrazeným na předchozím obrázku. V proměnné __DS0FilterTable je uložen filtr působící na vizuál matice z průřezu. Tento filtr je následně použitý přímo ve funkci SUMMARIZCECOLUMNS(). Pro získání hodnot pro řádky "Celkem" ve vizuálu matice jsou použity funkce ROLLUPADDISSUBTOTAL() a ROLLUPGROUP(). Sloupec 'Date'[Měsíc číslo] je sloupec který nevidíme nikde ve vizuálu matice, ale tento sloupec je součástí DAX dotazu, protože sloupec 'Date'[Měsíc] je řazený právě podle sloupce 'Date'[Měsíc číslo].
DAX dotazy které generují vizuály v Power BI většinou obsahují i funkce které běžně při psaní DAX výpočtů nepoužíváme a rozhodně není potřeba je nějak detailně studovat. I přesto je ale dobré vědět, jakým způsobem jsou data pro Power BI vizuály načítány z Tabulárního modelu. Tyto DAX dotazy jsou sice relativně komplexní, nicméně základem je většinou právě funkce SUMMARIZECOLUMNS(), v rámci které jsou vyhodnocena měřítka použitá ve vizuálech. Tím že dokážeme ve vygenerovaném DAX dotazu lokalizovat místo kde jsou vyhodnocena měřítka, můžeme si DAX dotaz zjednodušit a omezit ho pouze na funkci SUMMARIZECOLUMNS(), což je důležitá znalost zejména při vývoji složitějších výpočtů nebo při optimalizaci reportů.
Shrnutí
Psaní DAX dotazů vyžaduje určitou praxi a schopnost rozlišit, které funkce vracejí tabulky a které skalární hodnoty. Při psaní DAX dotazů ale nejsme omezeni používáním některých specifických funkcí. Používat můžeme všechny dostupné DAX funkce, stejně jako v počítaném sloupci, v měřítku nebo v počítané tabulce. Pouze musíme vždy myslet na to, že výsledkem dotazu musí být tabulka. Proto, pokud chceme získat pomocí dotazu skalární hodnotu, například výsledek měřítka, musíme použít funkci vracející tabulku nebo takzvaný konstruktor tabulky.
Komentáře
Okomentovat