Jednou z nejpoužívanějších funkcí v jazyku DAX je funkce CALCULATE(), díky které můžeme mimo jiné manipulovat s filtry v modelu. Tento článek obsahuje jednoduchý příklad s filtrováním jednoho sloupce ve funkci CALCULATE(). V ukázce budeme pracovat se cvičným souborem Adventure Works DW 2020.pbix, 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.
Jak filtrovat jeden sloupec v CALCULATE
V prvním kroku si vytvoříme v Power BI souboru měřítko, které bude počítat sumu prodejů z tabulky 'Sales' v aktuálním kontextu vyhodnocení. Měřítko prodejů tedy nebude obsahovat žádné explicitně definované filtry, a budeme ho používat pouze pro porovnání s měřítky, ve kterých budeme s filtry manipulovat. Definice tohoto měřítka bude vypadat následovně.
Měřítko:
Měřítko [Prodeje] bude vždy vyhodnoceno v kontextu filtrů nastavených v reportu uživatelem nebo autorem reportu. Pokud bychom vedle tohoto měřítka chtěli vytvořit jiný výpočet, který bude zobrazovat vždy pouze prodeje z jedné konkrétní kategorie produktů, například produktů z kategorie jízdních kol, musíme použít funkci, která dokáže přepsat vnější filtry, ve kterých je měřítko vyhodnoceno. Takovou funkcí je funkce CALCULATE(). Prvním argumentem funkce bude samotný výpočet. Druhým argumentem funkce bude filtr, který bude spolu se všemi vnějšími filtry ovlivňovat výsledek výpočtu v prvním argumentu funkce CALCULATE(). Měřítko, ve kterém budeme explicitně filtrovat jeden sloupec, může vypadat následovně.
Měřítko:
Prvním argumentem funkce CALCULATE() je výpočet sumy prodejů ze sloupce 'Sales'[Sales Amount], který obsahuje částky za prodeje produktů. Druhým argumentem je filtr, který ovlivňuje výsledek prvního argumentu, spolu se všemi vnějšími filtry v reportu, pokud nějaké v době vyhodnocení měřítka existují. Filtr je nastaven na sloupec obsahující kategorie produktů, a filtruje tyto produkty pouze na kategorii Bikes – jízdní kola. Nyní můžeme obě měřítka vložit vedle sebe do tabulky v reportu a podívat se na výsledek v situaci, kdy v reportu nejsou použity žádné filtry.
Měřítko [Prodeje] vrací sumu za všechny prodané produkty. Měřítko [Prodeje kategorie Bikes] vrací sumu prodejů produktů pouze z kategorie Bikes, na kterou je nastaven filtr uvnitř měřítka. Nyní přidáme do řádků tabulky barvy produktů. Obě měřítka tak budou nově vyhodnocena v kontextu filtru každé barvy, která se nachází v aktuálním řádku vizuálu.
Pokud se podíváme na výsledek měřítka [Prodeje] v prvním řádku tabulky, tak výsledná hodnota odpovídá sumě prodejů všech produktů, které mají černou barvu. V druhém řádku vrací měřítko [Prodeje] sumu prodejů za všechny produkty modré barvy, a tak dále. Měřítko [Prodeje kategorie Bikes] vrací v prvním řádku také sumu prodejů černých produktů, ale pouze černých produktů z kategorie jízdních kol. V druhém řádku tabulky vrací měřítko [Prodeje kategorie Bikes] sumu prodejů za produkty v modré barvě a znovu pouze z kategorie jízdních kol, a tak dále. V řádcích, ve kterých měřítko [Prodeje kategorie Bikes] nevrací žádnou hodnotu, nejsou k dispozici žádné prodané produkty v dané barvě a v kategorii jízdních kol.
Nyní vložíme do řádku tabulky hodnoty ze sloupce kategorie produktů, tedy ze sloupce, který jsme použili v měřítku [Prodeje kategorie Bikes] pro nastavení filtru.
Výsledek měřítka [Prodeje] odpovídá v každém řádku tabulky sumě prodejů produktů pro danou kategorii. Měřítko [Prodeje kategorie Bikes] vrací v každém řádku tabulky stejnou hodnotu, která představuje sumu prodejů produktů v kategorii jízdních kol. Pokud v měřítku definujeme filtr ve formě logického výrazu, dojde před nastavením filtru nejdříve k odstranění všech předchozích filtrů, které byly dříve nastaveny na použitý sloupec. Filtr nastavený na kategorii Bikes uvnitř měřítka [Prodeje kategorie Bikes] tedy přepíše všechny vnější filtry, které byly aplikovány před vyhodnocením měřítka na sloupec filtrovaný uvnitř funkce CALCULATE() pomocí logického výrazu. Toto chování může být ještě více zřetelné, pokud do reportu přidáme průřez, ve kterém použijeme pro filtrování stejný sloupec, tedy sloupec s kategoriemi produktů, ve kterém vybereme produkty z kategorie Clothing.
Tabulka nyní obsahuje pouze jeden řádek, protože filtr nastavený v průřezu filtruje také hodnoty v řádcích tabulky, tedy hodnoty ze sloupce s kategoriemi produktů. Měřítko [Prodeje] vrací sumu prodejů za produkty z kategorie Clothing. Měřítko [Prodeje kategorie Bikes] vrací prodeje produktů z kategorie jízdních kol, bez ohledu na filtr nastavený v průřezu a bez ohledu na stejný filtr, který působí na měřítko z řádků tabulky. Všechny vnější filtry nastavené na sloupec s kategoriemi produktů jsou před vyhodnocením měřítka [Prodeje kategorie Bikes] odstraněny, a nahrazeny filtrem obsahujícím pouze produkty z kategorie Bikes.
Průnik filtrů namísto jejich přepsání
Měřítko:
Nové měřítko nyní pro srovnání použijeme ve stejném reportu.
Měřítko [Prodeje] vrací očekávané hodnoty, odpovídající kontextu filtru v každém řádku tabulky, ve kterém je vyhodnoceno. Měřítko [Prodeje Bikes KEEPFILTERS] již vrací zajímavější výsledky. V měřítku [Prodeje Bikes KEEPFILTERS] je nyní filtr nastavený ve funkci CALCULATE() vyhodnocen v logickém AND vztahu s vnějšími filtry. V prvním řádku tabulky působí na měřítko [Prodeje Bikes KEEPFILTERS] při jeho vyhodnocení filtr nastavený na sloupec s kategoriemi na hodnotu Accessories. Uvnitř měřítka [Prodeje Bikes KEEPFILTERS] je nastavený filtr na stejný sloupec na hodnotu Bikes. Protože se oba filtry vylučují, výsledkem měřítka v prvním řádku tabulky je prázdná hodnota BLANK. V datech se totiž nevyskytují žádné produkty, které by byly současně v kategorii Accessories a v kategorii Bikes. Ve druhém řádku tabulky odpovídá hodnota měřítka [Prodeje Bikes KEEPFILTERS] prodejům produktů z kategorie Bikes. Filtr působící na měřítko v druhém řádku tabulky je stejný, jako je filtr nastavený uvnitř funkce CALCULATE(). Protože se oba filtry nyní nevylučují, ale shodují, měřítko vrací hodnotu odpovídající prodejům všech produktů z kategorie Bikes. Ve třetím a čtvrtém řádku se filtry opět vylučují, a měřítko vrací prázdnou hodnotu. Pokud bychom v průřezu zafiltrovali produkty pouze z kategorie Clothing, výsledek bude vypadat následovně.
Měřítko [Prodeje Bikes KEEPFILTERS] vrací prázdnou hodnotu, protože vnější filtry se opět vylučují s filtrem nastaveným uvnitř měřítka.
Shrnutí
Pokud použijeme logický filtr ve funkci CALCULATE() pro ovlivnění výsledku prvního argumentu, je tento logický filtr vyhodnocen v logickém AND vztahu se všemi aktivními filtry, které jsou aplikovány na jiné sloupce, než který je použitý v logickém filtru uvnitř funkce CALCULATE(). Pokud jsou v době vyhodnocení měřítka aktivní vnější filtry nastavené na stejný sloupec, který je použitý pro vytvoření logického filtru ve funkci CALCULATE(), dojde před vyhodnocením prvního výrazu ve funkci CALCULATE() k přepsání těchto filtrů, nastavených na stejný sloupec. Jednou z možností, jak toto chování změnit, je vložení celého filtru do funkce KEEPFILTERS(). Filtr vložený do funkce KEEPFILTERS() je potom vyhodnocen v logickém AND vztahu s aktivními vnějšími filtry nastavenými na stejný sloupec. Nedochází proto k přepsání vnějších filtrů nastavených na stejný sloupec, ale k jejich průniku.
Komentáře
Okomentovat