Jazyk DAX je funkcionální jazyk, ve kterém při tvorbě výpočtů běžně vnořujeme jednotlivé funkce do sebe. Kdykoliv analyzujeme DAX kód, který obsahuje více vnořených funkcí, můžeme začít od nejvnitřnější funkce a postupovat směrem od středu k dalším vnějším funkcím. Jedinou výjimkou jsou funkce CALCULATE() a CALCULATETABLE(). V tomto příspěvku si proto vysvětlíme, jaký je rozdíl při vyhodnocení jednotlivých argumentů ve funkci CALCULATE() a CALCULATETABLE() oproti ostatním DAX funkcím.
(Aktualizace 7. 12. 2024 - nové příklady a doplnění videa)
K tomuto tématu je k dispozici také video:
V příkladech v tomto příspěvku budeme pro zjednodušení pracovat pouze s funkcí CALCULATE(). Popsané principy ale platí také pro funkci CALCULATETABLE() s tím rozdílem, že funkce CALCULATE() vrací skalární hodnotu, a funkce CALCULATETABLE() vrací tabulku.
Než se dostaneme ke specifikům funkce CALCULATE(), vytvoříme si měřítko ve kterém nebudeme používat funkci CALCULATE(), abychom si připomněli způsob vyhodnocení vnořených DAX funkcí.
Nové měřítko bude vracet prodeje za produkty prodané po jednom kuse. Počet prodaných kusů máme v tabulce 'Sales' ve sloupci 'Sales'[Order Quantity]. Ve výpočtu nejdříve zafiltrujeme tabulku 'Sales' pouze na ty řádky, pro které platí, že odebrané množství kusů se rovná číslu 1. Následně v takto zafiltrované tabulce sečteme částky za prodeje produktů, které jsou uložené ve sloupci 'Sales'[Sales Amount].
Měřítko:
Pokud bychom chtěli významově interpretovat výše uvedený výpočet, tak začneme od nejvnitřnější funkce, tedy od funkce FILTER(). Vnější funkce SUMX() totiž nemůže být vyhodnocena dříve, než bude mít k dispozici tabulku, kterou vrací právě funkce FILTER(). Ve funkci FILTER() dojde nejdříve k vyhodnocení prvního argumentu, v našem příkladu k načtení všech řádků z tabulky 'Sales', které jsou dostupné v aktuálním kontextu vyhodnocení. Následně funkce FILTER() vyhodnotí podmínku pro každý řádek této tabulky v prvním argumentu, a vrátí pouze ty řádky z tabulky 'Sales', pro které je podmínka splněna.
Jakmile máme k dispozici tabulku kterou vrací funkce FILTER(), dojde k vyhodnocení vnější funkce SUMX(). Funkce SUMX() nejdříve načte tabulku zadanou v prvním argumentu, což je zafiltrovaná tabulka kterou vrací funkce FILTER(), a následně sečte všechny hodnoty ze sloupce 'Sales'[Sales Amount], tedy ze sloupce který je uveden ve druhém argumentu funkce SUMX().
Poznámka: Výše uvedená interpretace je sémantickou interpretací výpočtu. DAX Engine může výpočet na pozadí vyhodnotit jiným, efektivnějším způsobem.
Měřítko [Prodeje (Q 1 bez Calculate)] bude vracet sumu za prodeje produktů, které se prodaly po jednom kuse.
Měřítko [Prodeje (Q 1 bez calculate)] vrací nižší hodnoty než měřítko [Prodeje], protože v měřítku [Prodeje] sčítáme částky za prodeje produktů bez ohledu na počet prodaných kusů.
Pro úplnost si můžeme vytvořit také měřítko, které bude vracet prodeje za produkty prodané ve více než jednom kuse. Logika výpočtu bude velmi podobná, pouze změníme podmínku ve druhém argumentu funkce FILTER().
Měřítko:
Pokud vložíme nové měřítko do původního vizuálu, výsledek bude vypadat následovně.
V použitém cvičném souboru se produkty prodávali buď po jednom kuse, nebo po více než jednom kuse. V tabulce 'Sales' nejsou například záznamy o vráceném zboží nebo záznamy bez uvedeného počtu produktů, a proto součet měřítek [Prodeje (Q 1 bez calculate)] a [Prodeje (Q > 1 bez calculate)] bude odpovídat hodnotě měřítka [Prodeje].
Nyní, po relativně dlouhé přípravě se můžeme přesunout k funkci CALCULATE(). Pokud bychom chtěli získat prodeje za všechny produkty prodané po jednom kuse a použít při tom funkci CALCULATE(), což by měl být preferovaný postup, celý výpočet bude výrazně jednodušší.
Měřítko:
Výsledky obou verzí měřítek vracejících prodeje za produkty prodané po jednom kuse, tedy měřítka kde jsme nepoužívali funkci CALCULATE() a měřítka s použitím funkce CALCULATE(), můžeme vidět na následujícím obrázku.
Používání funkce CALCULATE() je ve většině případů velmi intuitivní, nicméně aby tomu tak mohlo být, skrývá v sobě tato funkce, v porovnání s ostatními DAX funkcemi, několik specifik. Jedním ze specifik je pořadí vyhodnocení argumentů.
Funkce CALCULATE() totiž nefunguje jako většina ostatních DAX funkcí, ve kterých je nejdříve vyhodnocen první argument, následně druhý, a tak dále. Funkce CALCULATE() nejdříve vyhodnotí druhý a případně další zadané argumenty, které jsou aplikovány jako filtry na model, a až následně vyhodnotí první argument, a to v nově vytvořeném kontextu filtru.
Vždy když tedy vidíme v DAX kódu funkci CALCULATE(), musíme se nejdříve zaměřit na druhý a případné další argumenty, a až následně přemýšlet o tom, jak bude vyhodnocen první argument této funkce po aplikování filtrů.
Způsob vyhodnocení jednotlivých argumentů ve funkci CALCULATE() je dobré znát, protože tato funkce se často používá ve výpočtech v měřítkách, a současně dříve vytvořená měřítka se běžně používají při definici dalších měřítek.
Uvažujme například následující výpočet.
Měřítko:
Ve výše uvedeném výpočtu je v prvním argumentu funkce CALCULATE() dříve vytvořené měřítko [Prodeje (Q 1)], které jak víme, vrací prodeje za produkty prodané po jednom kuse. Ve druhém argumentu je filtr nastavený na sloupec s odebraným množstvím produktů, který filtrujeme pouze na hodnoty větší než číslo 1. V samotném měřítku [Prodeje (Q 1)] je ale filtr nastavený na stejný sloupec, tentokrát ale na hodnoty, které se rovnají číslu 1.
Za jaké množství prodaných produktů bude vracet toto nové měřítko hodnoty, za produkty prodané po jednom kuse nebo za produkty prodané po více kusech?
Pokud si nejsme jistí odpovědí na předchozí otázku, můžeme se podívat na rozepsanou verzi výše uvedeného výpočtu, ve kterém je nahrazeno měřítko [Prodeje (Q 1)] jeho definicí.
Měřítko:
První varianta, která se nabízí je, že výsledkem výše uvedeného výpočtu bude prázdná hodnota BLANK, protože dva filtry nastavené na stejný sloupec se navzájem vylučují. Tato odpověď by napadla zřejmě každého, kdo má zkušenosti s jazykem SQL a zdá se být nejlogičtější. Dále se nabízí odpověď, že měřítko bude vracet prodeje za produkty prodané po jednom kuse, nebo prodeje za produkty prodané ve více kusech.
Před zobrazením výsledku si ještě můžeme připomenout, že každá ze dvou funkcí CALCULATE() použitých ve výše uvedeném měřítku nejdříve vyhodnotí druhý argument, to znamená filtr, a až následně první argument.
Odpověď na naši otázku získáme pokud si zobrazíme měřítko [Prodeje (Q 1)] vedle měřítek [Prodeje (Q 1 bez calculate)] a [Prodeje (Q > 1 bez calculate)].
Měřítko [Prodeje (Q ?)] vrací prodeje za produkty prodané po jednom kuse. Abychom si popsali proč tomu tak je, ukážeme si další verzi stejného výpočtu, tentokrát s rozepsanými logickými filtry tak, jak jsou na pozadí vyhodnoceny, protože každý filtr ve funkci CALCULATE() je ve skutečnosti tabulka.
Měřítko:
Nyní, když vidíme rozepsanou verzi měřítka [Prodeje (Q 1)] tak, jak je ve skutečnosti toto měřítko na pozadí vyhodnoceno, můžeme si vysvětlit proč jsou výsledkem prodeje za produkty prodané po jednom kuse.
Jak už jsme si řekli, funkce CALCULATE() nejdříve vyhodnotí filtry ve druhém a dalších argumentech, a až následně výpočet v prvním argumentu.
V našem měřítku je proto jako první vyhodnocen filtr ve vnější funkci CALCULATE(). Tento filtr bude vracet všechny hodnoty ze sloupce 'Sales'[Order Quantity], které jsou větší než 1. Následně dojde k vyhodnocení prvního argumentu vnější funkce CALCULATE().
Prvním argumentem vnější funkce CALCULATE() je vnitřní funkce CALCULATE(). Vnitřní funkce CALCULATE() opět nejdříve vyhodnotí filtr, který bude vracet tabulku s jedním sloupcem 'Sales'[Order Quantity], a tato tabulka s jedním sloupcem bude obsahovat jeden řádek s číslem 1. Vnitřní filtr přepíše vnější filtry nastavené na stejný sloupec, a to díky funkci ALL() v prvním argumentu funkce FILTER(). Výsledkem našeho měřítka [Prodeje (Q 1)] jsou proto prodeje za produkty prodané po jednom kuse.
Pokud bychom chtěli vyhodnotit filtry nastavené na stejné sloupce v logickém AND vztahu, museli bychom použít funkci KEEPFILTERS(). V opačném případě budou filtry ve funkci CALCULATE() vždy přepisovat vnější filtry nastavené na stejné sloupce.
Shrnutí
Pochopení pořadí vyhodnocení argumentů ve funkci CALCULATE() je velmi důležité a může výrazně usnadnit práci se složitějšími výrazy, které obsahují několik vnořených funkcí a mezi kterými mohou být i funkce CALCULATE() nebo CALCULATETABLE(). Funkce CALCULATE() může v některých situacích vracet neočekávané výsledky. Tomu se můžeme vyhnout, pokud se seznámíme se všemi vlastnostmi této funkce.
Komentáře
Okomentovat