Tento příspěvek obsahuje příklady použití různých druhů filtrů, které lze používat pro ovlivnění výsledku výrazu ve funkci CALCULATE(). Podrobnější informace o jednotlivých typech filtrů můžete následně najít v samostatných příspěvcích na stránce DAX - Průvodce.
Všechny příklady uvedené v tomto článku mohou být aplikovány také na funkci CALCULATETABLE() s tím rozdílem, že funkce CALCULATE() vrací skalární hodnotu (jedno číslo, jeden textový řetězec, jedno datum atd.), a funkce CALCULATETABLE() vrací tabulku.
Všechny ukázky z tohoto článku jsou vytvořeny v cvičném Power BI souboru Adventure Works DW 2020, který je volně dostupný ke stažení na stránkách Microsoftu. Odkaz na stažení souboru s řešením můžete najít níže pod tímto příspěvkem.
Filtry ve funkci CALCULATE
Funkce CALCULATE() umožňuje mimo jiné programově upravit kontext filtru. CALCULATE() má pouze jeden povinný argument, a to výraz, který se má vyhodnotit. Další, nepovinné argumenty, mohou být filtry ve formě logických výrazů, tabulek nebo ve formě speciálních funkcí pro úpravu filtrů, které se nazývají modifikátory filtrů.
Syntaxe funkce CALCULATE() vypadá následovně.
Syntaxe funkce CALCULATE:
Prvním argumentem funkce CALCULATE() je výraz, který se má vyhodnotit a který bude výsledek této funkce. Pokud použijeme funkci CALCULATE() bez filtrů, dojde k vyhodnocení výrazu uvnitř funkce v kontextu všech vnějších filtrů, v tzv. kontextu vyhodnocení. Dále, pokud v době vyhodnocení funkce CALCULATE() existuje aktivní kontext řádku, dojde ke změně kontextu řádku na jeho ekvivalent ve formě kontextu filtru. Využitím dalších argumentů funkce CALCULATE() pak můžeme odstranit, upravit nebo přidat další filtry.
Funkce CALCULATE() před vyhodnocením výrazu načte všechny aktivní vnější filtry, ve kterých je funkce vyhodnocena. K těmto filtrům pak CALCULATE() přidá filtry explicitně definované uvnitř funkce. Výrazy uvedené ve druhém a dalších argumentech funkce CALCULATE() mohou ale vnější filtry také přepsat, odebrat nebo upravit. Dalším filtrem vstupujícím do souboru filtrů pro vyhodnocení výrazu může být filtr, který vznikne změnou kontextu řádku na kontext filtru. Všechny tyto filtry jsou sloučeny do jednoho nového souboru filtrů, pod kterým je vyhodnocen výraz v prvním argumentu funkce.
Filtry ve funkci CALCULATE jsou vyhodnoceny v originálním kontextu
Při psaní filtrů v CALCULATE() musíme vždy brát v úvahu kontext, ve kterém je funkce vyhodnocena. Tento kontext ovlivňuje nejen samotný výsledek prvního výrazu, tzn. výrazu, který funkce vrací jako svůj výsledek. Kontext vyhodnocení ovlivňuje také samotné filtry, které jsou vyhodnoceny v aktuálním kontextu, ve kterém je funkce použita. To můžeme demonstrovat pomocí následujícího měřítka.
Měřítko:
Ve výše uvedeném měřítku chceme vrátit pomocí funkce VALUES() hodnotu obsahující datum, pro které se hodnota ze sloupce 'Date'[Date] rovná maximální hodnotě ze stejného sloupce. Jaká je ale maximální hodnota pak záleží na kontextu, ve kterém je funkce CALCULATE() vyhodnocena.
Poznámka: Stejného výsledku jako v předchozím měřítku bychom dosáhli také použitím výrazu MAX('Date'[Date]) v prvním argumentu funkce CALCULATE(), a to bez jakýchkoliv filtrů. Výše uvedený výpočet je pouze ilustrativní s cílem znázornit že filtry ve funkci CALCULATE() jsou vyhodnoceny v originálním kontextu, ve kterém je měřítko použito.
Kontext vyhodnocení je často složitější, a může obsahovat různé filtry přicházející z průřezů, sloupců a řádků v matici, z filtrů stránek atd.
Při psaní explicitních filtrů ve funkci CALCULATE() proto vždy uvažujeme, v jakém kontextu bude měřítko použito a jak tento kontext ovlivní vyhodnocení filtrů, ať už jedná o filtry logické, filtry ve formě tabulek nebo o modifikátory filtrů.
Logické filtry ve funkci CALCULATE
Filtry ve funkci CALCULATE() můžeme psát mimo jiné jako logické výrazy. Logické výrazy hodnotu TRUE nebo FALSE. Logické filtry můžeme v jazyce DAX tvořit pomocí aritmetických operátorů, jako jsou rovná se, je větší než, je menší než, nerovná se atd. Při tvorbě logických filtrů můžeme také použít logické operátory. Dvojitý ampersand (&&) znamená logické AND, dvojitý symbol kanálu (||) představuje logické OR. Díky operátoru IN pak můžeme porovnat hodnoty ve sloupci nebo více sloupcích s více hodnotami v tabulce.
Následující měřítko obsahuje filtr ve funkce CALCULATE() ve formě logického výrazu. Logický výraz porovnává hodnoty ve sloupci 'Product'[Color] a vrací hodnotu TRUE, pokud se hodnota ve sloupci rovná hodnotě "Blue".
Měřítko:
Pokud takto definované měřítko vložíme do Matice s kategoriemi produktů v řádcích, a dalším měřítkem [Prodeje] v hodnotách, které neobsahuje žádné explicitně definované filtry, výsledek může vypadat následovně.
Měřítko [Prodeje] vrací v každém řádku tabulky sumu prodejů pro danou kategorii, díky filtru který působí na výpočet z řádků vizuálu matice. Měřítko [Prodeje modré produkty] vrací hodnotu, která v každém řádku odpovídá prodejům pouze modrých produktů v dané kategorii.
Pokud nahradíme kategorie produktů v řádcích vizuálu hodnotami ze stejného sloupce, který jsme použili pro vytvoření logického filtru uvnitř funkce CALCULATE(), tedy ze sloupce s barvami produktů, výsledek může být překvapivý.
Hodnota měřítka [Prodeje modré produkty] je v každém řádku vizuálu stejná, a představuje sumu za prodeje všech modrých produktů. Ve druhém řádku tabulky je možné vidět, že hodnota měřítka [Prodeje] je stejná jako hodnota měřítka [Prodeje modré produkty].
Pokud bychom chtěli zjistit, proč je hodnota měřítka [Prodeje modré produkty] v každém řádku vizuálu stejná, musíme jít o krok hlouběji a vysvětlit si, co se odehrává na pozadí pokud vytvoříme ve funkci CALCULATE() logický filtr.
Vždy, když definujeme ve funkci CALCULATE() logický filtr, dojde před aplikací samotného filtru nejprve k odstranění všech filtrů, které byly dříve použity na sloupec použitý pro vytvoření logického filtru. Měřítko [Prodeje modré produkty] je při vyhodnocení ve skutečnosti na pozadí interpretováno následovně.
Měřítko:
Vždy když ve funkci CALCULATE() vytvoříme logický filtr, tento filtr je na pozadí převeden na tabulku. Logické filtry ve funkci CALCULATE() jsou pouze syntaktické zkratky pro zjednodušení zápisu. Pokud si rozepíšeme logický filtr tak, jak je ve skutečnosti vyhodnocen, zjistíme důvod proč dojde při použití logického filtru k přepsání všech vnějších filtrů které jsou aplikovány na stejný sloupec, jaký je použitý právě v logickém filtru. Funkce ALL () v prvním argumentu funkce FILTER() načte všechny hodnoty ze sloupce s barvami produktů, bez ohledu na vnější filtry, protože ty funkce ALL() ignoruje. Následně dojde k zafiltrování tabulky se všemi barvami produktů pouze na barvu modrou, a tato tabulka s jedním řádkem a jedním sloupcem je následně použita jako filtr, společně s případnými vnějšími filtry, při vyhodnocení měřítka [Prodeje] v prvním argumentu funkce CALCULATE().
Pokud tedy vložíme novou verzi měřítka do stejného vizuálu, výsledky obou měřítek budou totožné.
Na obrázku výše je vidět, že obě dvě měřítka, tedy měřítko s logickým filtrem a také měřítko s filtrem ve formě tabulky, vracejí stejné hodnoty, které představují v každém řádku tabulky prodej modrých produktů za fiskální rok 2019.
Logické filtry ve funkci CALCULATE() jsou vždy na pozadí přepsané do podoby tabulky. To může být vodítko pro pochopení některých výpočtů, které vracejí jiné hodnoty, než autor očekává. Filtr definovaný ve funkci CALCULATE() je nakonec vždy tabulka.
Filtry ve funkci CALCULATE ve formě tabulek
Ovlivnit výsledek výrazu ve funkci CALCULATE() můžeme také použitím filtru ve formě tabulky. Tabulka může obsahovat jeden sloupec, některé vybrané sloupce z tabulky, všechny sloupce z tabulky nebo také vybrané sloupce z více různých tabulek.
Tabulka jako filtr ve funkci CALCULATE
Jednoduchým příkladem použití tabulky jako filtru může být následující měřítko, které vrací počet produktů z tabulky 'Product', pro které existuje záznam v tabulce 'Sales'. Pro porovnání si můžeme vytvořit také druhé měřítko, které spočítá počet řádků v tabulce 'Product' v aktuálním kontextu, bez explicitních filtrů uvedených uvnitř CALCULATE().
Měřítka:
Nová měřítka si můžeme vložit každé samostatně do vizuálů karta, a zobrazit si výsledky.
Jak je vidět na obrázku výše, v tabulce 'Product' je 397 produktů, ale pouze 350 produktů má záznam o prodejích v tabulce 'Sales'. Pokud v průřezu vybereme pouze produkty s černou barvou, výsledek obou měřítek se změní. Opět tedy musíme počítat s kontextem, ve kterém je měřítko vyhodnoceno.
Používat celé tabulky ve filtru funkce CALCULATE(), tak jak tomu bylo u měřítka [Počet produktů s prodeji], nemusí být vždy úplně dobrý nápad. Obecně se dá říct, že filtry ve funkci CALCULATE() by měly obsahovat vždy pouze ty sloupce, které jsou nezbytně nutné pro dosažení požadovaného výsledku.
Stejného výsledku jako u měřítka [Počet produktů s prodeji] můžeme dosáhnout například následujícím způsobem.
Měřítko:
Jak v měřítku [Počet produktů s prodeji], tak v měřítku [Počet produktů s prodeji (2)], využíváme pro dosažení požadovaného výsledku princip rozšířených tabulek, kterému je věnován samostatný příspěvek pod tímto odkazem.
Funkce vracející tabulku jako filtr ve funkci CALCULATE
Jako filtr mohou být použity také funkce vracející tabulky. Stejně jako u všech ostatních filtrů ve funkci CALCULATE() můžeme kombinovat více filtrů současně. Níže uvedené měřítko vrací prodeje kol (první filtr) za fiskální rok 2019 (druhý filtr), opět s ohledem na další filtry vyplývající z kontextu vyhodnocení.
Měřítko:
Pokud měřítko vložíme do vizuálu matice s fiskálními roky v řádcích, výsledek může vypadat následovně.
Jak je vidět na obrázku výše, výsledek měřítka [Prodeje kol ve FY 2018] vrací v tabulce hodnotu pouze v řádku s fiskálním rokem 2018. Tímto způsobem definovaný filtr tedy nepřepsal existující filtr aplikovaný na sloupec 'Date'[Fiscal Year], jako tomu bylo u logických filtrů, ale mezi jednotlivými filtry nastavenými na stejný sloupec existuje logický AND vztah.
Ve druhém řádku tabulky je výraz v měřítku [Prodeje kol ve FY 2018] vyhodnocen v kontextu filtru kategorie "Bikes" (filtr definovaný uvnitř CALCULATE()), fiskálního roku 2018(filtr definovaný uvnitř CALCULATE()) a fiskálního roku 2019 (filtr definovaný v druhém řádku tabulky). Jelikož neexistují žádné současné prodeje pro fiskální rok 2018 a fiskální rok 2019, výsledek měřítka je prázdná hodnota BLANK.
Takto vytvořený filtr již je poměrně komplexní. Při psaní komplexních filtrů je třeba dobře znát chování každé použité funkce. Především je důležité vědět, jaká je návratová hodnota každé použité funkce v aktuálním kontextu filtru, které funkce generují kontext řádku a dále je třeba brát v úvahu všechny vnější filtry, které mohou do vyhodnocení filtrů vstupovat.
Modifikátory filtrů ve funkci CALCULATE
Speciální kategorií filtrů ve funkci CALCULATE() jsou tzn. modifikátory filtrů, tedy funkce pro úpravu filtrů. Mezi běžně používané modifikátory pro úpravu filtrů v CALCULATE() patří funkce REMOVEFILTERS(), ALLSELECTED(), ALLEXCEPT(), USERRELATIONSHIP() a další. Jednou z vlastností všech modifikátorů je, že jsou vyhodnoceny přednostně před všemi ostatními filtry explicitně definovanými uvnitř funkce CALCULATE().
Níže jsou uvedeny ukázky pouze vybraných běžně používaných modifikátorů. Fungovaní některých těchto funkcí je poměrně složité a vyžadovalo by detailnější popis, který by však byl nad rámec tohoto článku. Některé modifikátory lze použít také jako funkce vracející tabulky. V tomto příspěvku ovšem o všech uvedených funkcích uvažujme pouze jako o modifikátorech CALCULATE().
Funkce REMOVEFILTERS() a funkce ALLSELECTED()
Funkce REMOVEFILTERS() odstraní všechny aktivní filtry z tabulky nebo sloupců, uvedených v argumentu této funkce. Pokud je v argumentu funkce zadaná tabulka, dojde k odstranění filtrů také z tabulky v její rozšířené verzi. V případě použití REMOVEFILTERS() bez argumentu dojde k odstranění všech filtrů z aktuálního kontextu vyhodnocení. Dříve se k odstranění filtrů používala funkce ALL(), která má, coby modifikátor funkce CALCULATE(), stejné chování jako funkce REMOVEFILTERS().
Funkce ALLSELECTED() načte kontext filtru, ve kterém je vyhodnocena poslední iterační funkce, v rámci které je ALLSELECTED() použita, pokud není tento kontext ignorován, například při použití funkce ALL () jako funkce vracející tabulku. ALLSELECTED() může být použita s argumentem ve formě sloupce, tabulky, nebo bez argumentu. ALLSELECTED() se nedoporučuje používat v iteračních funkcích, pokud neznáme přesně její fungování. Více informací o funkci ALLSELECTED() můžete najít v samostatném příspěvku.
Pro porovnání chování funkcí REMOVEFILTERS() a ALLSELECTED() můžeme vytvořit dvě podobná měřítka. V prvním měřítku použijeme jako modifikátor funkci REMOVEFILTERS(), a ve druhém funkci ALLSELECTED(), pokaždé s argumentem ve formě sloupce s barvami produktů.
Měřítka:
Obě měřítka vložíme do Power BI vizuálu Matice. V řádcích vizuálu jsou hodnoty ze sloupce 'Product'[Color] a hodnoty ze stejného sloupce jsou také v průřezu.
Jak je vidět na obrázku výše, funkce REMOVEFILTERS() odstraní všechny filtry, které jsou použity na sloupec uvedený v argumentu funkce. Funkce ALLSELECTED() odebere pouze filtry, které jsou vygenerovány na pozadí dotazu v rámci iterační funkce, ve které je měřítko vyhodnoceno. V tomto případě se jedná o dotaz vygenerovaný na pozadí vizuálu matice a dojde proto k odstranění filtrů z řádků matice. ALLSELECTED() následně přebere vnější filtry, ve kterých je dotaz vyhodnocen, v tomto případě filtr z průřezu.
Měřítko [Prodeje REMOVEFILTERS Color] vrací sumu prodejů za všechny barvy, bez ohledu na všechny filtry aplikované na sloupec 'Product'[Color]. Měřítko [Prodeje ALLSELECTED Color] vrací v každém řádku tabulky sumu prodaných produktů v černé, modré a šedé barvě - tedy v barvách vybraných v průřezu, který představuje vnější filtr pro iterační funkci použitou pro vygenerování hodnot na pozadí vizuálu.
Výše uvedené funkce jsou často používány pro výpočet dělitele při výpočtu procentuálních podílů. V těchto typech výpočtů chceme, aby dělenec respektoval kontext filtru přicházejících z řádků a filtrů, ale dělitel by měl obsahovat sumu bez ohledu na filtry z řádků vizuálu.
Jako ukázku můžeme vytvořit dvě měřítka, kde použijeme jako dělenec měřítko [Prodeje]. Dělitel bude v prvním případě ignorovat všechny filtry aplikované na sloupec 'Product'[Color]. V druhém případě bude dělitel ignorovat filtry přicházející z řádků vizuálu, ve kterém je výpočet vyhodnocen, ale vnější filtry bude respektovat. Vnějším filtrem rozumíme i v tomto případě opět filtr z průřezu.
Měřítka:
Pokud použijeme měřítka ve stejném reportu, můžeme vidět rozdíl mezi použitím funkce REMOVEFILTERS() a funkce ALLSELECTED().
Měřítko [% podíl REMOVEFILTERS] počítá procentuální podíl prodejů produktů pro konkrétní barvu v daném řádku na celkových prodejích bez ohledu na barvu produktu. Měřítko [% podíl ALLSELECTED] počítá procentuální podíl produktů pro konkrétní barvu v daném řádku na celku, který představuje pouze prodeje produktů v barvách vybraných v průřezu.
Funkci ALLSELECTED() bychom neměli používat v iteračních funkcích, pokud přesně nevíme jak funkce funguje, protože jedna iterační funkce se již většinou nachází na pozadí vizuálů, které v reportech používáme. Dále bychom neměli vkládat měřítka obsahující funkci ALLSELECTED() do jiných měřítek. V případě dodržení těchto pravidel je použití funkce ALLSELECTED() jednoduché a intuitivní a její chování odpovídá příkladu uvedenému v ukázce výše. Více informací o funkci ALLSELECTED() můžete najít v samostatném příspěvku.
Funkce ALLEXCEPT()
Dalším často používaným modifikátorem filtrů je funkce ALLEXCEPT(). ALLEXCEPT() odstraní všechny aktivní filtry z tabulky a její rozšířené verze, uvedené v prvním argumentu funkce, a ponechá aktivní filtry na sloupcích nebo tabulkách, uvedených v dalších argumentech funkce.
Tuto funkci můžeme použít například v situaci, kdy chceme vypočítat podíl prodejů v kategorii na celkových prodejích v nadřazené kategorii.
Ve výše uvedeném vizuálu jsou v řádcích v hierarchii kategorie a subkategorie. Pokud tedy chceme vypočítat podíl prodejů dané subkategorie na celkových prodejích v příslušné kategorii, můžeme definovat následující měřítko.
Měřítko:
Nové měřítko pak můžeme vložit do stejného vizuálu a zobrazit si výsledek.
Výsledkem je procentuální podíl prodejů v dané subkategorii na celkových prodejích v nadřazené kategorii. Funkce ALLEXCEPT() odstraní všechny filtry z tabulky uvedené v prvním argumentu funkce, a ponechá filtry na sloupcích nebo tabulkách uvedených v druhém a dalších argumentech funkce. Pokud tedy ve vizuálu nahradíme subkategorie produktů konkrétními produkty, měřítko [% Podíl v kategorii] bude nyní vracet procentuální podíl prodejů aktuálního produktu vůči celkovým prodejům v kategorii.
Stejným způsobem bychom mohli použít měřítko [% Podíl v kategorii] v kontextu jakéhokoliv atributu z tabulky 'Product', vždy za předpokladu že vizuál obsahuje v nejvyšší úrovni hierarchie kategorie produktů.
Funkce KEEPFILTERS()
Další užitečnou funkcí je funkce KEEPFILTERS(). Logické filtry nebo tabulky uvedené v argumentu této funkce se při vyhodnocení porovnávají se všemi vnějšími filtry v logickém AND vztahu. Nedochází tedy k přepsání filtrů, ale k jejich průniku.
Můžeme například uvažovat následující dvě měřítka, kde v každém měřítku filtrujeme výsledek prodejů pouze na kategorii produktů jízdní kola.
Měřítka:
Rozdíl mezi těmito měřítky můžeme vidět na následujícím obrázku.
Měřítko [Prodeje Bikes] vrací v každém řádku vizuálu hodnotu prodejů za produkty z kategorie Bikes. Filtr použitý jako argument ve funkce CALCULATE() před vyhodnocením prvního argumentu přepíše všechny vnější aktivní filtry aplikované na sloupec 'Product'[Category], a následně tento sloupec zafiltruje na hodnotu "Bikes". Dojde tedy k přepsání filtru.
Na druhou stranu, při použití funkce KEEPFILTERS() nedojde k přepsání filtru, ale aktivní filtr aplikovaný na sloupec 'Product'[Category], přicházející z řádků vizuálu je vyhodnocen v logickém AND vztahu s filtrem uvedeným ve funkci KEEPFILTERS().
V prvním řádku tabulky tedy působní na měřítko [Prodeje Bikes KEEPFILTERS] filtr obsahující kategorii "Accessories". Filtr definovaný uvnitř měřítka, který filtruje stejný sloupec na hodnotu "Bikes", je pak vyhodnocen v logickém AND s tímto vnějším filtrem. Protože každý produkt patří pouze do jedné kategorie, tak nemůže podmínka "Bikes" a současně "Accessories" nikdy platit, proto je v prvním řádku výsledek měřítka [Prodeje Bikes KEEPFILTERS] prázdná hodnota BLANK. Ve druhém řádku pak platí, že filtr na kategorii "Bikes" přicházející z řádku tabulky odpovídá explicitně definovanému filtru ve funkci CALCULATE(), proto v tomto řádku výsledek měřítka odpovídá prodejům v kategorii "Bikes".
Ve funkci CALCULATE(), stejně jako ve funkci CALCULATETABLE(), můžeme definovat explicitní filtry, které ovlivní výsledek výrazu uvedeného jako první argument funkce. Filtry definované uvnitř funkce CALCULATE() mohou být ve formě logických výrazů, ve formě tabulek a ve formě speciálních funkcí, které se nazývají modifikátory pro úpravu filtrů. Všechny tyto filtry můžeme libovolně kombinovat. Při psaní explicitních filtrů musíme také uvažovat o kontextu, ve kterých je měřítko vyhodnoceno, protože kontext vyhodnocení ovlivňuje nejen samotný výpočet, ale může ovlivňovat také vyhodnocení filtrů. Při porozumění všech aspektů ovlivňujících výpočet výrazu ve funkci CALCULATE() můžeme tvořit komplexní výpočty, které mohou obsahovat vnější filtry, filtry vznikající změnou kontextu řádku na jeho ekvivalent ve formě kontextu filtru a filtry explicitně definované uvnitř funkce CALCULATE(). Podrobnější informace o jednotlivých druzích filtrů můžete najít na stránce DAX - Průvodce. Některé praktické příklady s použitím funkce CALCULATE() jsou pak k dispozici na stránce DAX - Příklady.
Komentáře
Okomentovat