Funkce CALCULATE() v sobě skrývá mnoho drobných detailů, které mohou být na první pohled skryté, přesto ale velmi důležité. V tomto příspěvku se zaměříme na jeden z těchto detailů, a to na způsob vyhodnocení filtrů ve funkci CALCULATE(), které jsou definované jako logické výrazy.
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 Power BI souboru Adventure Works DW 2020.pbix. Soubor s řešením je k dispozici ke stažení níže pod tímto příspěvkem.
V tomto souboru je navíc vytvořené měřítko [Prodeje], které má následující definici.
Měřítko:
Výchozí report, se kterým budeme v příkladech pracovat, bude obsahovat vizuál Průřez s barvami produktů a vizuál Matice s roky a měsíci v řádcích, a s měřítkem [Prodeje] v hodnotách.
Jak jsou vyhodnoceny logické filtry ve funkci CALCULATE
Ve dříve vytvořeném reportu můžeme v průřezu vybrat jednu nebo více barev, ve kterých se prodávají produkty, a ovlivnit tak výsledek měřítka [Prodeje] ve vizuálu Matice. Pokud například vybereme pouze černou barvu, měřítko [Prodeje] bude vracet sumu za prodeje produktů v aktuálním měsíci a roce, nicméně pouze za produkty v černé barvě.
Měřítko:
Pokud nové měřítko vložíme do původního vizuálu, výsledek bude vypadat následovně.
Co ale může být méně intuitivní je, že výsledkem logického filtru definovaného v měřítku [Prodeje (modrá barva)] je tabulka. Tato tabulka v tomto konkrétním měřítku [Prodeje (modrá barva)] obsahuje jeden sloupec a jeden řádek s hodnotou "Blue". Každý logický filtr ve funkci CALCULATE() je pouze syntaktická zkratka. Měřítko [Prodeje (modrá barva)] je ve skutečnosti na pozadí vyhodnoceno následujícím způsobem.
Měřítko:
Funkce FILTER() použitá ve filtru funkce CALCULATE() je iterační funkce vracející tabulku. Prvním argumentem funkce FILTER() je funkce ALL(), která vrací v tomto konkrétním měřítku všechny barvy ze sloupce 'Product'[Color]. Tato tabulka vytvořená funkcí ALL() s jedním sloupcem a se všemi barvami produktů je následně zafiltrována pouze na jednu konkrétní barvu, barvu "Blue". Výsledkem druhého argumentu funkce CALCULATE() v měřítku [Prodeje (modrá barva) 2] je tedy tabulka s jedním sloupcem a jedním řádkem, s hodnotou "Blue". Pokud nové měřítko [Prodeje (modrá barva) 2] vložíme do původního vizuálu, výsledek bude vypadat následovně.
Logické filtry ve funkci CALCULATE() jsou vždy na pozadí převedeny na tabulky. Tato tabulka pak může obsahovat jeden nebo více sloupců, jeden nebo více řádků, a to podle toho, jakým způsobem je logický filtr vytvořen.
Podívejme se na další příklad, ve kterém budeme chtít získat sumu za prodeje produktů, u kterých byla celková částka utracená zákazníkem vyšší, než řekněme 5 000. Celková částka se pak bude skládat z jednotkové ceny produktu vynásobené počtem zakoupených kusů. Měřítko [Prodeje (velké nákupy)] můžeme pomocí logického filtru ve funkci CALCULATE() vytvořit následujícím způsobem.
Měřítko:
Měřítko [Prodeje (velké nákupy)] opět obsahuje logický filtr, tentokrát se dvěma sloupci. Tento logický filtr je na pozadí zase převeden na tabulku, a to následujícím způsobem.
Měřítko:
Výsledná tabulka ve filtru funkce CALCULATE() nyní obsahuje dva sloupce a více řádků. Tabulka v prvním argumentu funkce FILTER() vytvořená opět pomocí funkce ALL() nejdříve načte všechny platné kombinace hodnot ze sloupců 'Sales'[Unit Price] a 'Sales'[Order Quantity]. Následně je tato tabulka se všemi hodnotami z těchto dvou sloupců zafiltrována pouze na ty řádky, ve kterých platí že jednotková cena produktu vynásobená odebraným množstvím je větší než 5 000. Výsledný filtr pak obsahuje tabulku se dvěma sloupci a s hodnotami z těchto sloupců, pro které platí definovaná podmínka. Tato tabulka je následně použita jako filtr před vyhodnocením měřítka [Prodeje] v prvním argumentu funkce CALULATE(). Obě nová měřítka vrací stejné hodnoty, protože měřítko [Prodeje (velké nákupy)] je opět na vyhodnoceno způsobem, kterým je definováno měřítko [Prodeje (velké nákupy) 2].
Od teorie zpět k příkladům. V následujícím příkladu si ukážeme, jak pomocí tabulky ve filtru funkce CALCULATE() můžeme naprosto změnit způsob jakým mezi sebou interagují vizuály v reportu.
Filtry ve funkci CALCULATE jsou vždy tabulky
Na následujícím obrázku je opět zachycen stejný report s vizuálem Průřez a vizuálem Matice a se stejnými atributy a s měřítkem [Prodeje], stejně jako v předchozích příkladech.
Protože už víme že filtry ve funkci CALCULATE() jsou tabulky, a tyto tabulky mohou obsahovat jakékoliv hodnoty z jakýchkoliv sloupců v modelu, můžeme pomocí vlastním způsobem sestavené tabulky změnit chování vizuálů.
Celý algoritmus bude vypadat následovně. V prvním kroku si načteme všechny barvy, které jsou vybrané v průřezu. Následně si do další proměnné uložíme úplně všechny barvy ze sloupce 'Product'[Color], bez ohledu na výběr v průřezu. Finální tabulka pro filtr ve funkci CALCULATE() pak bude obsahovat pouze ty barvy, které jsou v tabulce se všemi barvami, kromě těch barev, které jsou v tabulce s barvami vybranými v průřezu. Takto popsaný výpočet může vypadat například následovně.
Měřítko:
Funkce EXCEPT() v proměnné BarvyDoFiltru vrací všechny hodnoty z tabulky v prvním argumentu, kromě hodnot v tabulce ve druhém argumentu. Výsledná tabulka pro filtr proto obsahuje všechny barvy kromě těch, které jsou aktuálně vybrané v průřezu.
Shrnutí
Funkce CALCULATE() v sobě skrývá mnoho drobných detailů, které je dobré mít na paměti, zejména pokud tvoříme složitější DAX výpočty. Jedním z těchto detailů je také fakt, že filtry ve funkci CALCULATE() jsou vždy tabulky. I když ve funkci CALCULATE() používáme logické výrazy, výsledkem těchto logických výrazů je nakonec také tabulka. Tabulka je dobře uchopitelná a představitelná a je proto dobré takto o filtrech ve funkci CALCULATE() přemýšlet. Tabulka ve filtru funkce CALCULATE() může obsahovat jakékoliv sloupce z modelu, a jakékoliv hodnoty z těchto sloupců. Díky tomu můžeme ve funkci CALCULATE() tvořit komplexní filtry, ve kterých můžeme používat jakékoliv DAX funkce, které vracejí tabulky.
Více informací o funkci CALCULATE() můžete najít v samostatných příspěvcích na stránce Jazyk DAX. Praktické příklady vytvořené v Power BI pomocí DAX výpočtů můžete najít na stránce Power BI nebo na stránce DAX příklady. Pod tímto odkazem pak můžete najít video návody publikované na mém Youtube kanále.
Komentáře
Okomentovat