Proměnné v jazyku DAX umožňují tvořit přehlednější výpočty, přináší možnost rozdělením kódu na jednotlivé dílčí části a za určitých okolností může být výpočet s použitím proměnných vyhodnocen rychleji než alternativní výpočet bez použití proměnných. V tomto příspěvku si popíšeme, co jsou to proměnné v jazyku DAX a na příkladech si ukážeme jakým způsobem se používají.
(Článek byl aktualizován 7. 10. 2024)
K tomuto tématu je k dispozici také video:
Proměnné v jazyku DAX mají trochu jiný význam než v jiných programovacích jazycích. Proměnná v jazyku DAX slouží pouze k uložení výrazu, který již nemůže být nikdy přepsán. Jedná se tedy spíše o konstanty než o proměnné.
Velmi důležitou vlastností proměnných v jazyku DAX je způsob jejich vyhodnocení. Proměnné jsou vyhodnoceny pouze jednou, bez ohledu na to, kolikrát je v kódu použijeme, a jsou vyhodnoceny v kontextu, kde jsou definovány, ne kde jsou použity. Současně, pokud definujeme proměnou, která následně nebude nikde v další části kódu použita, nebude výpočet v této proměnné nikdy vyhodnocen.
Práce s proměnnými v jazyku DAX
Proměnné můžeme definovat v rámci jakéhokoliv DAX výrazu. Do proměnných můžeme ukládat jak skalární hodnoty, tak tabulky. Každá proměnná je uvedena klíčovým slovem VAR, za kterým následuje název proměnné a za znaménkem rovná se definice proměnné. Proměnných můžeme vytvořit v rámci jednoho výrazu libovolné množství. Pro ukončení výrazu pak slouží klíčové slovo RETURN, za kterým následuje výsledek výrazu. Jednotlivé VAR - RETURN výrazy můžeme také vnořovat. To znamená, že uvnitř definice jedné proměnné můžeme definovat další proměnné, a výsledek vnitřní definice opět vypsat pomocí klíčového slova RETURN.
Názvy proměnných nesmí začínat číslovkou, nesmí obsahovat žádné speciální znaky kromě podtržítka, nesmí obsahovat znaky s diakritikou a konečně názvy proměnných se nesmí shodovat s názvy tabulek v modelu nebo s názvy DAX funkcí.
Příklady použití proměnných v Power BI
Příklady v tomto příspěvku jsou vytvořeny ve cvičném Power BI souboru Adventure Works DW 2020.pbix. V tomto souboru je navíc vytvořené měřítko [Prodeje], které vrací sumu za prodané produkty v aktuálním kontextu vyhodnocení.
Měřítko:
Pokud měřítko [Prodeje] vložíme do vizuálu Matice spolu s kategoriemi produktů z tabulky 'Product' v řádcích, výchozí vizuál, se kterým budeme pracovat může vypadat například následovně.
Uvažujme nyní situaci, kdy bychom chtěli vytvořit měřítko které bude vracet průměrnou cenu prodávaných produktů v každé kategorii. Ve výpočtu tedy budeme chtít dělit hodnotu prodejů v dané kategorii počtem prodaných kusů. Hodnotu prodejů získáme pomocí měřítka [Prodeje]. Počty prodaných kusů jsou pak uloženy ve sloupci 'Sales'[Order Quantity]. Výpočet průměrné ceny s použitím proměnných pak může vypadat například následovně.
Měřítko:
Definice proměnných začíná vždy klíčovým slovem VAR, za kterým následuje název proměnné a za znaménkem rovná se definice proměnné. Proměnných můžeme v rámci jednoho výrazu vytvořit libovolné množství. Každý výraz s použitím proměnných je pak ukončen klíčovým slovem RETURN, za kterým následuje výsledek výrazu. Za klíčovým slovem RETURN můžeme vypsat jakýkoliv výraz. To znamená že za klíčovým slovem RETURN můžeme vypsat jakoukoliv dříve definovanou proměnnou, anebo další výpočet s použitím proměnných nebo také jakýkoliv výpočet bez použití dříve definovaných proměnných. To se může hodit zejména při debugování složitějších výpočtů.
V měřítku [Průměrná cena] vidíme celkem tři proměnné. V první proměnné je uložena hodnota prodejů v aktuálním kontextu vyhodnocení. Ve druhé proměnné je uložena hodnota, která vrací počet prodaných kusů v aktuálním kontextu vyhodnocení. V proměnné PrumernaCenaProduktu pak dělíme ve funkci DIVIDE() hodnotu prodejů počtem prodaných kusů, a výsledek tohoto výpočtu vypisujeme za klíčovým slovem RETURN.
Měřítko [Průměrná cena] by samozřejmě šlo vytvořit i bez použití proměnných, nicméně zvyknout si používat proměnné i v jednoduchých výpočtech má několik výhod. Zaprvé, při tvorbě DAX výpočtů se relativně často stává, že z prvotní myšlenky, která předpokládá velmi jednoduchý výpočet se při vývoji stane složitější výpočet, a tím že si automaticky ukládáme jednotlivé mezivýpočty do proměnných, můžeme následně celý výpočet snadněji rozvíjet bez složitého vnořování dalších a dalších funkcí do sebe. Zadruhé, používáním proměnných a jejich vhodným pojmenováním tvoříme současně dokumentaci výpočtu, jak si ukážeme na následujícím příkladu.
Uvažujme například následující DAX výpočet, který bude vracet procentuální podíl prodejů v jednotlivých kategoriích vůči prodejům za všechny kategorie.
Měřítko:
Podívat se rovnou můžeme také na alternativní výpočet procentuálních prodejů v kategorii, tentokrát s použitím proměnných.
Měřítko:
Pokud si porovnáme definice měřítek [% Podíl kategorie] a [% Podíl kategorie (v2)], tak zápis s použitím proměnných v měřítku [% Podíl kategorie (v2)] je sice delší, nicméně zřejmě kdokoliv, kdo se na tuto definici měřítka podívá dokáže popsat, co vlastně chceme spočítat a jaký význam má každá část výpočtu. Jak již bylo řečeno, vhodnými názvy jednotlivých proměnných totiž současně tvoříme určitou dokumentaci výpočtu, a díky tomu pracujeme s přehlednějším DAX kódem který je také daleko lépe udržovatelný.
Další výhodou měřítka [% Podíl kategorie (v2)] je snadná možnost ověřovat si jednotlivé mezivýpočty. Tím že máme každou část uloženou v samostatné proměnné, můžeme následně kteroukoliv proměnnou použít jako výsledek měřítka za klíčovým slovem RETURN.
Obě verze výpočtu pak samozřejmě vrací stejné výsledky.
Při používání proměnných je velmi důležité vědět, že proměnné jsou vyhodnoceny tam kde jsou definovány, a ne tam kde jsou použity. Díky tomu že v jazyku DAX můžeme pomocí některých funkcí měnit kontext vyhodnocení, stává se celkem běžně že tento kontext je v rámci jednoho výpočtu na různých místech jiný. Proto, pokud existuje v rámci jednoho výpočtů více kontextů, je důležité, ve které části zamýšlenou proměnnou definujeme.
Pro ilustraci tohoto problému se můžeme vrátit ke kompaktnější verzi výpočtu procentuálního podílu prodejů v kategorii.
Měřítko:
Při pohledu na definici měřítka [% Podíl kategorie] si můžeme všimnout, že v celém výpočtu je dvakrát použito měřítko [Prodeje], jednou v prvním argumentu funkce DIVIDE(), a podruhé ve druhém argumentu funkce DIVIDE(), uvnitř funkce CALCULATE().
Jednou z typických optimalizačních technik je pak každý výraz, který se v rámci jednoho výpočtu opakuje, uložit do proměnné a následně v různých částech výpočtu používat tuto proměnnou. Díky tomu dáváme DAX Enginu informaci, že stejný výraz použitý na různých místech může být vyhodnocený pouze jednou, a to na místě, kde definujeme proměnnou. Zde si ale musíme dát pozor právě na to, v jakém kontextu je proměnná definována, protože to je místo, kde bude také vyhodnocena.
Bez této znalosti bychom mohli v dobrém úmyslu vytvořit následující verzi výpočtu procentuálního podílu prodejů kategorie.
Měřítko:
V nové verzi měřítka si nejdříve ukládáme hodnotu měřítka [Prodeje] do proměnné AktualniProdeje, kterou následně použijeme v prvním a ve druhém argumentu funkce DIVIDE(). Pokud ale nové měřítko vložíme do původního vizuálu s kategoriemi produktů v řádcích, výsledkem bude vždy 100 %.
Problém s měřítkem [% Podíl kategorie (špatně)] spočívá v tom, že v rámci funkce DIVIDE() potřebujeme vyhodnotit měřítko [Prodeje] pokaždé v jiném kontextu. Poprvé v originálním kontextu, ve kterém je měřítko vyhodnoceno, kdy tento kontext obsahuje filtr z řádků vizuálu nastavený na aktuální kategorii produktů. Podruhé uvnitř funkce CALCULATE(), kde ale chceme kontext filtru změnit, a to tak že odstraňujeme filtry právě ze sloupce s kategoriemi produktů pomocí funkce REMOVEFILTERS(). Cílem výpočtu je totiž vydělit aktuální prodeje v kategorii prodeji za všechny kategorie, a získat tak procentuální podíl.
Pokud ale uložíme hodnotu měřítka [Prodeje] do proměnné, je toto měřítko vyhodnoceno pouze jednou, v našem výpočtu v originálním kontextu, tedy v kontextu filtru aktuální kategorie. Následně je tato hodnota, která je uložená v proměnné AktualniProdeje, použita jednou v prvním argumentu funkce DIVIDE(), a podruhé ve druhém argumentu funkce DIVIDE() uvnitř funkce CALCULATE(). To že ve druhém argumentu funkce DIVIDE() odstraníme filtry ze sloupce s kategoriemi produktů ale nemá žádný vliv na to, v jakém kontextu je proměnná AktualniProdeje vyhodnocena. Ta už totiž byla vyhodnocena dříve v místě, kde je definována, a následně nemůže být změněna. Hodnota 100 % je proto výsledkem dělení prodejů v aktuální kategorii prodeji v aktuální kategorii, což je vždy číslo 1, a tedy 100 %.
Výše uvedený příklad sloužil ke znázornění toho, proč si musíme dávat pozor v jakém kontextu definujeme jednotlivé proměnné. Ačkoliv se nyní může zdát, že způsob vyhodnocení proměnných omezuje jejich používání, ve skutečnosti jde o velkou výhodu, díky které se výrazně zjednodušuje psaní komplexních DAX výpočtů. To si můžeme ukázat na dalším příkladu.
Začneme jednoduchými měřítky [Prodeje], [Průměrné prodeje produktů] a [Počet produktů].
Měřítka:
Pokud všechna tři měřítka vložíme do vizuálu spolu s kategoriemi produktů v řádcích, výsledek může vypadat například následovně.
Použitá měřítka vrací přesně ty hodnoty, které názvy těchto měřítek popisují. Představme si nyní situaci, kdy bychom chtěli vědět, kolik produktů se v každé kategorii prodává nadprůměrně v porovnání s ostatními produkty. To znamená že potřebujeme zjistit počet produktů, jejichž prodeje jsou vyšší než průměrné prodeje všech produktů v dané kategorii (kontext filtru aktuální kategorie z řádků vizuálu). K tomuto účelu si můžeme vytvořit v rámci DAX výpočtu virtuální tabulku s názvy produktů, kterou zafiltrujeme pouze na ty produkty, pro které vrací měřítko [Prodeje] vyšší hodnotu než měřítko [Průměrné prodeje produktů]. Následně v takto zafiltrované tabulce spočítáme počet řádků a měli bychom dostat správný výsledek.
Měřítko:
Nové měřítko ale bude vracet v každém řádku vizuálu prázdnou hodnotu BLANK.
Problémem měřítka [Počet nadprůměrných produktů (špatně)] je logická podmínka ve funkci FILTER(). V této podmínce se snažíme domněle zjistit, jestli jsou prodeje aktuálního produktu větší než průměrné prodeje produktů.
Ve skutečnosti ale ověřujeme, jestli jsou prodeje aktuálního produktu větší než prodeje aktuálního produktu. Pokud vyhodnocujeme měřítko [Prodeje] v kontextu řádku každého produktu, který je v tabulce v prvním argumentu funkce FILTER(), dostaneme opravdu prodeje aktuálního produktu. Pokud ale vyhodnocujeme měřítko [Průměrné prodeje produktů] také v kontextu řádku aktuálního produktu, nedostaneme hodnotu průměrných prodejů všech produktů v kategorii, ale průměrnou hodnotu prodejů pouze aktuálního produktu. Průměrná hodnota prodejů jednoho produktu pak bude vždy stejná jako hodnota měřítka [Prodeje]. Logická podmínka ve funkci FILTER() proto nemůže nikdy platit.
Vyřešit celý problém můžeme více různými způsoby. Pokud ale nechceme duplikovat již existující kód v měřítku [Průměrné prodeje produktů] a nebo složitě manipulovat s filtry pomocí funkce CALCULATE(), stačí si uložit hodnotu měřítka [Průměrné prodeje produktů] do proměnné v originálním kontextu, tedy mimo funkci FILTER(), a následně použít v logické podmínce funkce FILTER() tuto proměnnou.
Měřítko:
Ve výše uvedeném výpočtu je již způsob vyhodnocení proměnných velkou výhodnou. Hodnotu měřítka [Průměrné prodeje produktů] si ukládáme do stejně nazvané proměnné, mimo funkci FILTER(). Díky tomu je měřítko [Průměrné prodeje produktů] vyhodnoceno v originálním kontextu filtru, tedy v kontextu jednotlivých kategorií, a vrací průměrné prodeje produktů v dané kategorii. Tato hodnota je pak použita v logické podmínce funkce FILTER(), kde ověřujeme, jestli jsou prodeje aktuálního produktu (měřítko [Prodeje]) větší než průměrné prodeje všech produktů v dané kategorii. Funkce FILTER() pak bude vracet pouze ty produkty, pro které je tato podmínka splněna.
Druhá důležitá informace, se kterou se v rámci tohoto příspěvku setkáváme poprvé, je ta že do proměnných můžeme ukládat také virtuální tabulky, které mohou být následně použity pro další výpočty. V proměnné NadprumerneProdukty je funkce FITLER(), která vrací tabulku, v našem případě s jedním sloupcem a názvy produktů které splňují námi definovanou podmínku. Následně v proměnné PocetNadprumernychProduktu zjišťujeme pomocí funkce COUNTROWS() počet řádků v této tabulce, a tuto hodnotu vypisujeme za klíčovým slovem RETURN.
Z edukativního důvodu si můžeme ukázat ještě jednu variantu měřítka, které bude vracet počet nadprůměrných produktů.
Měřítko:
Zaměřme se nyní na proměnou NadprumerneProdukty, která bude zase obsahovat tabulku s produkty zafiltrovanými pouze na ty produkty, pro které platí že prodeje těchto produktů jsou vyšší než průměr. V rámci proměnné NadprumerneProdukty ale nyní narozdíl od původního výpočtu nejdříve definujeme novou proměnnou ProduktyVAktuanimKontextu, ve které si pomocí funkce VALUES() načítáme názvy produktů, které jsou dostupné v aktuálním kontextu. Za klíčovým slovem RETURN, kterým ukončujeme výraz s proměnnou ProduktyVAktualnimKontextu, následuje funkce FILTER(), ve které v prvním argumentu používáme tabulku s produkty uloženou v předchozí proměnné. Logická podmínka ve funkci FILTER() pak nově začíná definicí proměnných, a to proměnných ProdejeAktualnihoProduktu a Podminka. Výsledkem výrazu ve druhém argumentu funkce FILTER() je pak proměnná Podminka, která vrací hodnotu TRUE nebo FALSE a funguje jako filtr pro jednotlivé produkty.
Tato verze výpočtu již může působit relativně složitě oproti předchozí variantě stejného výpočtu. Důležité na tomto příkladu ale je uvědomit si, že proměnné v jazyku DAX můžeme definovat kdekoliv, kde je očekávaný výraz který vrací skalární hodnotu a nebo tabulku. Tento postup je vhodný zejména u složitějších výpočtů, což samozřejmě není případ měřítka [Počet nadprůměrných produktů (v2)]. Pokud by ale byla například podmínka ve druhém argumentu funkce FILTER() složena z několika na sebe navazujících mezivýpočtů, je velmi výhodné uložit si tyto mezivýpočty do samostatných proměnných a následně za klíčovým slovem RETURN vypsat pouze výslednou podmínku, která vrací hodnotu TRUE nebo FALSE, podobně jako v měřítku [Počet nadprůměrných produktů (v2)]. Úplně stejně pak tabulka v prvním argumentu funkce FILTER() může obsahovat hodnoty které mohou být výsledkem složité manipulace, a i v tomto případě si tabulku pro první argument funkce FILTER() můžeme uložit do proměnné s vhodným názvem. Nová varianta výpočtu počtu nadprůměrných produktů pak bude opět vracet stejné výsledky jako původní verze.
Za pozornost ještě stojí hodnota měřítka [Počet nadprůměrných produktů] v řádku souhrnů. Číslo 69 totiž na první pohled neodpovídá součtu jednotlivých hodnot z ostatních řádků vizuálu. Důvodem je že měřítko [Počet nadprůměrných produktů] je ze své podstaty neaditivní. Více informací o práci s neaditivními výpočty můžete najít v samostatném příspěvku pod tímto odkazem.
Shrnutí
Používání proměnných výrazně zjednodušuje psaní DAX kódu. V některých případech sice zápis výpočtu s použitím proměnných zabere více řádků, nicméně benefitem je lepší čitelnost, možnost odladit si každý krok ve výpočtu samostatně a logické rozdělení složitějších výpočtů na dílčí jednodušší mezivýpočty. Další výhodou proměnných je způsob jejich vyhodnocení. Proměnné jsou vyhodnoceny tam, kde jsou definovány, a ne kde jsou použity. Můžeme si tak jednoduše uložit jeden výpočet v určitém kontextu vyhodnocení, a použít výsledek výpočtu uložený v proměnné v jiném kontextu vyhodnocení, například uvnitř iteračních funkcí. Zvyknout si používat proměnné může zabrat určitý čas, nicméně výhody jejich používání by měly převažovat.
Perfektní vysvětlení tématu - ostatně jako obvykle. Děkuji a zdravím.
OdpovědětVymazatDěkuji moc :)
VymazatSuper článek a ještě lepší video, moc díky!
OdpovědětVymazat