Jak přemýšlet o filtrech ve funkci CALCULATE

Úvodní obrázek

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:

Prodeje = SUM(Sales[Sales Amount])

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 přemýšlet o filtrech ve funkci CALCULATE

Na následujících několika příkladech si popíšeme jak jsou na pozadí vyhodnoceny logické filtry ve funkci CALCULATE() a obecně jakým způsobem bychom o filtrech ve  funkci CALCULATE() měli přemýšlet.

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ě.

Jak přemýšlet o filtrech ve funkci CALCULATE 2

Představme si nyní situaci, kdy bychom chtěli vytvořit měřítko, které bude vracet sumu za prodeje produktů v jedné konkrétní barvě, řekněme třeba v modré barvě, a to bez ohledu na to jaká barva je právě vybraná v průřezu. Takovýto výpočet můžeme vytvořit jednoduše pomocí funkce CALCULATE() následujícím způsobem.

Měřítko:

Prodeje (modrá barva) =
CALCULATE
(
    [Prodeje],
    'Product'[Color] = "Blue"
)

Pokud nové měřítko vložíme do původního vizuálu, výsledek bude vypadat následovně.

Jak přemýšlet o filtrech ve funkci CALCULATE 3

Ačkoliv je v průřezu vybraná černá barva, měřítko [Prodeje (modrá barva)] tento filtr ignoruje, a vrací prodeje za produkty v modré barvě, a to díky logickému filtru vytvořenému ve funkci CALCULATE(). První důležitý poznatek tedy je, že logické filtry ve funkci CALCULATE() přepisují vnější filtry, které jsou nastavené na stejný sloupec, jaký je použitý pro sestavení logického filtru.

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:

Prodeje (modrá barva) 2 =
CALCULATE
(
    [Prodeje],
    FILTER
    (
        ALL('Product'[Color]),
        'Product'[Color] = "Blue"
    )
)

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ě.

Jak přemýšlet o filtrech ve funkci CALCULATE 4

Měřítko [Prodeje (modrá barva)] a měřítko [Prodeje (modrá barva) 2] vrací stejné hodnoty a vracet budou stejné hodnoty v jakémkoliv kontextu, ve kterém bychom tato měřítka použili. Důvodem je že měřítko [Prodeje (modrá barva)] je na pozadí vyhodnoceno způsobem, kterým je definováno měřítko [Prodeje (modrá barva) 2].

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:

Prodeje (velké nákupy) =
CALCULATE
(
    [Prodeje],
    Sales[Unit Price] * Sales[Order Quantity] > 5000
)

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:

Prodeje (velké nákupy) 2 =
CALCULATE
(
    [Prodeje],
    FILTER
    (
        ALL(Sales[Unit Price], Sales[Order Quantity]),
        Sales[Unit Price] * Sales[Order Quantity] > 5000
    )
)

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].

Jak přemýšlet o filtrech ve funkci CALCULATE 5

O filtrech ve funkci CALCULATE() bychom měli vždy přemýšlet jako o tabulkách. Tyto tabulky pak mohou obsahovat jakékoliv hodnoty z jakýchkoliv sloupců v modelu. Jakmile si uvědomíme že filtry ve funkci CALCULATE() jsou prosté tabulky, můžeme začít tvořit komplexní filtry pomocí velkého množství DAX funkcí vracejících tabulky. S těmito tabulkami můžeme pomocí DAX funkcí různým způsobem manipulovat, ukládat si mezivýpočty do proměnných a finální tabulku sestavenou podle našich požadavků následně použít jako filtr ve funkci CALCULATE().

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.

Jak přemýšlet o filtrech ve funkci CALCULATE 6

Na obrázku výše je možné vidět že v Průřezu jsou vybrány určité barvy produktů, a měřítko [Prodeje] tedy vrací sumu za prodeje produktů v těchto vybraných barvách. To je standardní chování Průřezu a není na tom nic překvapivého. Představme si ale situaci, kdy bychom chtěli vidět výsledky prodejů za produkty ve všech barvách, které nejsou vybrány v průřezu. Výběr barvy v průřezu by tedy nefiltroval měřítko, ale produkty ve vybraných barvách v průřezu by naopak byly vyloučeny z výpočtu.

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:

Prodeje (ostatní barvy) =
VAR VybraneBarvy = VALUES('Product'[Color])
VAR VsechnyBarvy = ALL('Product'[Color])
VAR BarvyDoFiltru = EXCEPT(VsechnyBarvy, VybraneBarvy)
VAR Vypocet =
    CALCULATE
    (
        [Prodeje],
        BarvyDoFiltru
    )
RETURN
    Vypocet

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.

Jak přemýšlet o filtrech ve funkci CALCULATE 7

Měřítko [Prodeje] nyní vrací sumu za prodeje produktů v barvách "White" a "Yellow", které jsou vybrané v Průřezu. Nové měřítko [Prodeje (ostatní barvy)] pak vrací sumu za prodeje produktů ve všech barvách, kromě barev vybraných v průřezu. Díky tomu si můžeme například porovnat, jaký podíl tvoří prodeje produktů ve vybraných barvách, oproti prodejům ve všech ostatních barvách.

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.

Stáhnout soubor s řešením.
č. 88

Komentáře