DAX je programovací jazyk, jehož primárním účelem je rozšíření Tabulárního modelu (Power BI, Power Pivot v Excelu, SSAS atd.) o vlastní logiku, zejména prostřednictvím měřítek, počítaných sloupců nebo počítaných tabulek. V tomto příspěvku se podíváme na to, v čem je jazyk DAX unikátní oproti všem ostatním programovacím jazykům, což je velmi důležité pro pochopení, jakým způsobem o jazyku DAX přemýšlet a jak tento jazyk efektivně používat.
K tomuto tématu je k dispozici také video:
Jazyk DAX můžeme v současné chvíli používat k mnoha různým účelům a ve stále více nástrojích. Nejtypičtější použití jazyka DAX je při tvorbě měřítek, počítaných sloupců nebo počítaných tabulek. Stejný jazyk DAX a všechny jeho funkce ale můžeme použít také pro psaní DAX dotazů, pro vytvoření Calculation Groups (Kalkulačních skupin), pro vytvoření vlastních, uživatelsky definovaných funkcí, pro nastavení zabezpečení dat nebo pro tvorbu vizuálních výpočtů.
I přesto, že se jazyk DAX, stejně jako Power BI nebo obecně Tabulární model, neustále vyvíjí a neustále přibývají nové a nové funkcionality a přirozeně také nové DAX funkce, každý autor reportu nebo Tabulárního modelu téměř vždy dojde do situace, kdy ke splnění požadavků uživatelů musí vytvořit správně fungující měřítka, která často úzce souvisí s danou společností a s jejími specifickými procesy.
Vytvořením měřítek dáváme uživatelům možnost analyzovat data prostřednictvím logiky uložené právě v měřítku, a to jednoduchým drag-and-drop způsobem, bez nutnosti znát jakýkoliv programovací jazyk. A právě měřítka jsou to, co je na jazyku DAX tak unikátní a co nenajdeme v žádném jiném programovacím jazyku.
V čem spočívá unikátnost měřítek a proč mohou měřítka vyvolávat tolik frustrace při jejich vývoji si vysvětlíme na jednoduchém příkladu.
Uvažujme například následující výpočet v měřítku vracející procentuální marži.
Měřítko:
Pro úplnost, v měřítku [% Marže] se odkazujeme na měřítka [Prodeje] a [Náklady], která jsou definována následovně.
Měřítko:
Měřítko:
Pokud si všechna tři měřítka vložíme do vizuálu Matice, spolu s kategoriemi produktů v řádcích, výsledek bude vypadat následovně.
Všechna měřítka vrací hodnoty podle očekávání. Měřítko [Prodeje] vrací sumu za prodeje produktů prodaných v každé kategorii, respektive prodeje za všechny produkty v případě řádku souhrnů Celkem. Totéž měřítko [Náklady], které vrací náklady na pořízení stejných produktů v kategoriích a pro všechny kategorie v řádku Celkem. V měřítku [% Marže] pak jednoduše dělíme hrubý zisk hodnotou prodejů, a tím získáme hrubou marži, opět pro jednotlivé kategorie nebo pro všechny produkty v řádku Celkem. Nová měřítka vrací správné hodnoty a všechno vypadá velmi přirozeně.
Každé měřítko ovlivňují dva vstupy
V této chvíli je ale velmi důležité uvědomit si, že z pohledu programování není úplně přirozené, že použitá měřítka vrací prodeje, náklady a marži, které odpovídají jednotlivým kategoriím produktů.
Zaměřme se pro zjednodušení pouze na jedno měřítko, například na měřítko [% Marže].
Co je na definici měřítka [% Marže] nepřirozené? V měřítku [% Marže] nikde neříkáme, že chceme spočítat marži pro jednotlivé kategorie. V definici měřítka říkáme, že chceme prostřednictvím měřítka [Prodeje] načíst sumu za prodeje produktů a prostřednictvím měřítka [Náklady] sumu vynaloženou na pořízení produktů, ale ani v definici těchto měřítek nikde neuvádíme, pro jaké kategorie produktů chceme náklady a tržby sečíst. Přesto vidíme ve vizuálu matice marže zvlášť pro jednotlivé kategorie produktů.
Pokud vložíme do řádků vizuálu Matice roky namísto kategorií produktů, uvidíme marže v jednotlivých letech, opět bez jakékoliv zmínky o rocích v rámci definice DAX výpočtu.
Čtenář znalí jazyka DAX si v tuto chvíli může říct, že popisovaný stav má jasné vysvětlení, a že je přece každý DAX výpočet vyhodnocen v takzvaném kontextu vyhodnocení, který se skládá z kontextu filtru a z kontextu řádku.
Uvažujme například řádek s rokem 2017 na obrázku výše. V tomto řádku působí na výpočet filtr nastavený na rok 2017, a dále filtr nastavený na kategorii Bikes v Průřezu.
Pokud bychom chtěli vyjádřit vnější filtry působící na měřítko [% Marže] v prvním řádku vizuálu Matice pomocí jazyka DAX, mohli bychom použít následující zápis.
Z pohledu jazyka DAX je jedno zda filtr pochází z vizuálu Průřez (kategorie Bikes) nebo z řádku vizuálu Matice (rok 2017). Všechny vnější filtry působící na měřítko jsou nakonec sloučeny a vyhodnoceny v logickém AND vztahu a aplikovány na model, před vyhodnocením samotného měřítka, stejným způsobem jako vidíme na obrázku výše.
Proto, v měřítku [% Marže] v tomto konkrétním řádku, v prvním řádku vizuálu matice s rokem 2017, načítáme prodeje a náklady pouze za rok 2017 a kategorii Bikes, a na základě těchto hodnot jednoduše spočítáme hrubou marži.
Velmi podobně je měřítko [% Marže] vyhodnoceno v dalších řádcích vizuálu Matice, protože vnější filtry působí na měřítka v reportech automaticky.
Finální hodnotu měřítka tedy ovlivňují dva faktory. Zaprvé, DAX výpočet v měřítku, za který jsme jako autoři plně zodpovědní. Zadruhé, prostředí, ve kterém je měřítko vyhodnoceno. Prostředí, ve kterém je měřítko vyhodnoceno ale jako autoři DAX výpočtů můžeme ovlivnit pouze částečně, někdy dokonce vůbec. Pokud totiž tvoříme tzv. Enterprise model, uživatel může mít možnost tvořit vlastní reporty nebo vlastní kontingenční tabulky v Excelu. U modelu vytvořeného pouze pro jeden konkrétní Power BI soubor pak uživatelé běžně mohou ovlivňovat výsledky měřítek v reportech pomocí filtrů přes různé vizuály nebo prostřednictvím postranního panelu filtrů.
Pokud tedy tvoříme DAX výpočet, zejména v měřítku, musíme uvažovat jednak o definici samotného měřítka, ale musíme současně myslet na to, že dané měřítko je vyhodnoceno v určitém prostředí, které se může měnit, a které bude také ovlivňovat výsledné hodnoty.
To je to nejdůležitější na co nesmíme nikdy zapomínat. U jednoduchých výpočtů, jako je například výpočet v měřítku [% Marže], není kontext vyhodnocení obvykle problém a vše funguje velmi intuitivně. Měřítko [% Marže] bude vracet v použitém modelu správné a očekávané hodnoty v kontextu jakýchkoliv atributů. Problémy ale často nastávají v situacích, kdy tvoříme komplexní výpočty s použitím mnoha různých funkcí, a kdy jsme tak zabraní do samotné logiky výpočtu, že zapomínáme na vnější kontext, ve kterém bude měřítko vyhodnoceno. Až 90 % všech chyb v definici měřítek je způsobeno špatným pochopením interakce mezi vnějším kontextem vyhodnocení.
Důvody, proč u komplexních měřítek často narážíme na problémy, jsou opět dva.
Zaprvé, některé DAX funkce respektují kontext filtru, jiné ho mohou ignorovat, přepisovat nebo odstraňovat. Některé DAX funkce mohou interagovat s kontextem řádku, zejména prostřednictvím změny kontextu řádku na kontext filtru, a jiné funkce kontext řádku kompletně ignorují.
S tím souvisí také druhý, často opomíjený problém, který spočívá v tom, že v rámci jednoho měřítka, nebo obecněji v rámci jednoho DAX výpočtu, můžeme na různých místech výpočtu pracovat jiným způsobem s vnějším kontextem vyhodnocení, právě kvůli tomu, jak se jednotlivé funkce v daném kontextu vyhodnocení chovají a jak mohou jiné DAX funkce filtry přepisovat, odstraňovat nebo přidávat.
Práce s kontextem vyhodnocení v rámci definice výpočtu
Uvažujme například následující vizuál Matice, kde jsou v řádcích roky a státy, ze kterých pocházejí zákazníci.
Představme si nyní situaci, kdy bychom chtěli zjistit procentuální podíl počtu prodaných produktů v aktuálním státě vůči počtu prodaných produktů za všechny státy.
Pro dosažení požadovaných výsledků budeme potřebovat dvakrát sečíst počet prodaných kusů ze sloupce 'Sales'[Order Quantity]. První hodnota bude obsahovat počet prodaných kusů v aktuálním státě, to znamená v aktuálním kontextu vyhodnocení. Druhá hodnota bude obsahovat počet prodaných kusů za všechny státy.
Abychom získali počet prodaných produktů za všechny státy v každém řádku vizuálu, tzn. i v řádcích, ve kterých na výpočet působí filtr nastavený na konkrétní stát, musíme ve výpočtu nějakým způsobem manipulovat s vnějšími filtry.
Začneme ale jednodušším výpočtem, a to výpočtem, který bude vracet počet prodaných produktů v aktuálním kontextu vyhodnocením, v našem připraveném vizuálu počet prodaných kusů v aktuálním státě v řádcích se státy.
Měřítko:
Pokud nové měřítko vložíme do původního vizuálu, výsledek bude vypadat následovně.
Měřítko [Počet prodaných kusů] vrací hodnoty odpovídající vybrané kategorii produktů v Průřezu, a dále hodnoty odpovídající aktuálnímu roku a státu, díky filtrům působícím na měřítka z řádků vizuálu. Pokud bychom chtěli popsat jazykem DAX, jakým způsobem byla načtena hodnota 463 ve zvýrazněné buňce na obrázku výše, mohli bychom použít následující zápis.
Nyní, když máme představu o tom, jak dojde k načtení počtu prodaných kusů pro konkrétní stát, můžeme se přesunout k výpočtu počtu prodaných kusů za všechny státy. Abychom mohli vydělit počet prodaných kusů za konkrétní stát počtem prodaných kusů za všechny státy, do řádků se státy budeme potřebovat načíst hodnotu, která je nyní v řádcích s roky.
Následně budeme schopni vydělit, v případě státu Australia a roku 2017, hodnotu 463 hodnotou 7 327 a dostaneme správný výsledek.
Jak dostat stejné hodnoty, nyní zobrazené v řádcích souhrnů s roky, do všech příslušných řádků se státy? Musíme v řádcích se státy vytvořit stejný kontext filtru, který je v řádcích s roky.
Zaměříme-li se opět na jednu konkrétní buňku s roky, a to na řádek s rokem 2017, v tomto řádku můžeme prostřednictvím jazyka DAX popsat vnější kontext filtru následovně.
Pro připomenutí, řádek se státem Australia pro rok 2017 obsahuje následující kontext filtru.
Jediný rozdíl mezi kontextem filtru s řádcích souhrnů s roky a kontextem filtru v řádcích se státy je, že v řádcích se státy je navíc filtr nastavený na aktuální stát v aktuálním řádku vizuálu. Abychom tedy dostali v řádcích se státy hodnoty, které odpovídají hodnotám v řádcích souhrnů s roky, stačí odstranit vnější filtr nastavený na sloupec 'Customer'[Country-Region]. Odstranit filtr z konkrétních sloupců můžeme například pomocí funkce REMOVEFILTERS() následujícím způsobem.
Měřítko:
Pokud vložíme nové měřítko [Počet prodaných kusů (všechny státy)] do našeho původního vizuálu, výsledek bude následující.
Nyní, když víme, jak získat hodnoty jak pro dělitele, tak pro dělence, můžeme vytvořit finální výpočet v jednom kroku.
Měřítko:
Nové měřítko pak bude vracet požadované hodnoty.
Pro interpretaci výsledků se opět zaměříme na jednu konkrétní buňku vizuálu, což je vždy jediný možný postup, pokud se snažíme pochopit, jak je měřítko vyhodnoceno ve vnějším kontextu. Na červeně zvýrazněnou buňku na obrázku výše působí pořád tři filtry, jeden z Průřezu, jeden z řádku souhrnů s roky a jeden z aktuálního řádku vizuálu. Tyto tři filtry bychom pomocí jazyka DAX mohli popsat následujícím způsobem.
Pokud si ale rozbalíme výpočet v měřítku [% Podíl počtu prodaných kusů] a přidáme, jak se propisují vnější filtry k jednotlivým agregacím, tak zjistíme, že v různých částech výpočtu už působí na jednotlivé agregace jiné filtry.
Ačkoliv vnější kontext filtru je stále stejný a obsahuje tři filtry nastavené na tři sloupce, pomocí funkce REMOVEFILTERS() jsme před vyhodnocením výrazu SUM('Sales'[Order Quantity]) v proměnné PocetProdanychKusuVsechnyStaty odstranili jeden z těchto filtrů, konkrétně filtr nastavený na sloupec 'Customer'[Country-Region]. Proto působí na agregační funkci SUM() použitou na dvou místech výpočtu vždy jiné filtry, a díky tomu jsme dosáhli správného výsledku.
Interakce měřítka s kontextem vyhodnocení je něco, s čím musíme při tvorbě DAX výpočtu vždy počítat. Jazyk DAX totiž není pouze o syntaxi a o tom co daná funkce dělá, ale také o tom, jak se použité funkce chovají v kontextu filtru a v kontextu řádku.
Interakce DAX funkcí s kontextem vyhodnocení
Jako příklad můžeme použít dvě základní DAX funkce, funkci ALL() a funkci VALUES().
Funkce ALL(), pokud ji použijeme s argumentem ve formě sloupce, bude vracet všechny jedinečné hodnoty z použitého sloupce. To si můžeme demonstrovat na jednoduchém DAX dotazu.
Ve sloupci 'Product'[Color] je deset barev, a všechny tyto barvy jsou výsledkem DAX dotazu zobrazeného na obrázku výše.
Pokud nahradíme funkci ALL() funkcí VALUES(), výsledek bude následující.
Jak můžeme vidět na obrázku výše, také funkce VALUES() vrací všechny jedinečné hodnoty ze sloupce 'Product'[Color].
Funkce ALL() a funkce VALUES() ale nejsou stejné. Funkce ALL() vrací všechny jedinečné hodnoty. Funkce VALUES() pak vrací všechny jedinečné hodnoty dostupné v aktuálním kontextu filtru.
To je zásadní rozdíl. V rámci DAX dotazu je vnější kontext filtru prázdný, a obě funkce tak vrací stejné výsledky. Pokud ale obě funkce použijeme v měřítku, tak jejich chování již, v závislosti na aktuálním kontextu filtru, nemusí být stejné.
Následující měřítko bude vracet vždy počet všech barev ze sloupce 'Product'[Color].
Měřítko:
Pokud funkci ALL() nahradíme funkcí VALUES(), měřítko bude vracet počet barev dostupných v aktuálním kontextu filtru.
Měřítko:
Pokud si obě nová měřítka vložíme do vizuálu Matice, například s kategoriemi produktů v řádcích, můžeme na první pohled vidět rozdíl mezi chováním funkce ALL() a funkce VALUES() v kontextu filtru aktuální kategorie produktů.
Měřítko s použitím funkce ALL() vrací vždy číslo 10, což je počet všech barev, protože funkce ALL() ignoruje kontext filtru, který ve vizuálu tvoří kategorie produktů v řádcích. Měřítko s použitím funkce VALUES() pak vrací počet barev, ve kterých se prodávají produkty v aktuální kategorii, protože funkce VALUES() vrací pouze ty hodnoty, které jsou dostupné v aktuálním kontextu filtru.
Na základě těchto dvou měřítek samozřejmě nemůžeme říci, jestli je jedna funkce lepší nebo horší než druhá. Někdy zkrátka potřebujeme v rámci DAX výpočtu ignorovat vnější filtry, a jindy potřebujeme načíst hodnoty dostupné v aktuálním kontextu filtru, a podle toho si zvolíme příslušnou funkci.
Nyní, po zobrazení několika jednoduchých příkladů si můžeme odpovědět na otázku, proč je jazyk DAX tak těžký a jak se ho správně učit.
Proč je jazyk DAX těžký na pochopení a jak se ho správně učit
Jazyk DAX je těžký, protože je málo "vizuální". Pokud tvoříme měřítka a píšeme DAX kód, musíme přemýšlet o kontextu filtru, v rámci kterého bude měřítko vyhodnoceno. Tento vnější kontext filtru nevidíme v definici měřítka přímo napsaný, ale musíme si ho uvědomit a musíme vědět, jak budou jednotlivé funkce v aktuálním kontextu filtru vyhodnoceny a co budou vracet. Tady vždy pomáhá zaměřit se při vývoji na jednu konkrétní buňku ve vizuálu, a napsat si formou komentářů do jednotlivých částí DAX kódu, jaké filtry v dané buňce vizuálu na aktuální část výpočtu působí.
Z výše uvedeného také vyplývá, na co nesmíme nikdy zapomenout, pokud pracujeme s funkcí, která je pro nás nová. Vždy když narazíme na novou funkci, kterou ještě neznáme, tak se musíme naučit, jak se daná funkce chová v kontextu filtru a v kontextu řádku. Nestačí se naučit co funkce dělá, jakou má syntaxi, zda vrací tabulku nebo skalární hodnotu, ale to hlavní, na co se musíme ptát je, jak se daná funkce chová v kontextu řádku a jak se chová v kontextu filtru. Jakmile se jednotlivé funkce začneme učit tímto způsobem, brzy dojdeme do bodu, kdy pro nás jazyk DAX přestane být záhadou a začneme přirozeně používat kontext řádku a kontext filtru při řešení složitých výpočtů.
Tento způsob učení není jednoduchý. Často vyžaduje vlastní testování dané funkce, abychom vůbec zjistili, jak se chová v kontextu filtru a v kontextu řádku. Jakmile si ale přidáme ke každé funkci, kterou používáme, také tuto znalost, zjistíme, že jazyk DAX je sice těžký na naučení, ale principiálně je velmi jednoduchý. Dokonalé ovládnutí kontextu vyhodnocení je ta hranice, po jejíž překonání se všechno zjednoduší.
Shrnutí
Všechny příklady v tomto příspěvku byly záměrně velmi jednoduché, s cílem zdůraznit, že na každý výpočet v měřítku působí kontext filtru, a že různé funkce mohou s kontextem filtru interagovat jiným způsobem. V reálném modelu pracujeme obvykle se složitějšími výpočty a často také s vnořenými funkcemi nebo s iteračními funkcemi, které navíc generují kontext řádku. I u složitějších výpočtů je ale princip pořád stejný, vnější kontext filtru se propisuje do každé části výpočtu, pokud není některými funkcemi záměrně přepsán nebo odstraněn.
Zlaté pravidlo při nefunkčním výpočtu je následující. Vždycky, když máme komplexní výpočet, který nevrací požadované výsledky, rozdělíme si ho na dílčí kroky, například pomocí proměnných, a ujistíme se, v jakém kontextu filtru a v jakém kontextu řádku je dílčí mezivýpočet vyhodnocen. Kontext vyhodnocení je totiž to s čím musíme vždy počítat, i když ho nevidíme přímo napsaný v rámci DAX kódu. Samotným uvědoměním si, jaký je aktuální kontext vyhodnocení, často dojdeme k odhalení nedostatků v definici DAX výpočtů.
Komentáře
Okomentovat