Funkce FILTER() je iterační funkce, která vrací tabulku uvedenou v prvním argumentu funkce se všemi řádky z této tabulky, pro které platí logická podmínka uvedená ve druhém argumentu funkce. Prvním argumentem funkce FILTER() může být tabulka nebo také funkce vracející tabulku. Druhým argumentem funkce je logický výraz, který je vyhodnocen v kontextu každého řádku tabulky uvedené v prvním argumentu funkce. Syntaxe funkce FILTER() vypadá následovně.
Syntaxe funkce FILTER:
Přestože funkce FILTER() vrací tabulku, můžeme tuto funkci používat v různých typech DAX výpočtů. Funkce FILTER() může být použita samostatně nebo jako součást více funkcí při vytvoření nové počítané tabulky. Pro funkci FILTER() můžeme najít uplatnění také při vytváření nových počítaných sloupců, v definici měřítek nebo při psaní DAX dotazů. Níže jsou uvedeny některé možné příklady využití funkce FILTER(). Příklady jsou v uvedeny postupně od nejjednodušších až po složitější, včetně příkladů s použitím funkce FILTER() jako filtru ve funkci CALCULATE() při vytváření měřítek.
Příklady funkce FILTER
Jednoduchý příklad použití funkce FILTER
V prvním příkladu si ukážeme nejjednodušší způsob použití funkce FILTER(). Funkci FILTER() můžeme použít pro vytvoření nové počítané tabulky, kde při vyhodnocení nepůsobí na funkci žádné vnější filtry, jak je tomu obvykle v reportech při vyhodnocení měřítek. Následující výraz vrací tabulku se všemi sloupci z tabulky 'Product', ale pouze s řádky, které obsahují záznamy o produktech v bílé barvě.
Počítaná tabulka:
Prvním argumentem je v modelu již existující tabulka 'Product'. Druhým argumentem funkce FILTER() je logický výraz, který je vyhodnocen pro každý řádek tabulky uvedené v prvním argumentu. Pokud je logický výraz v konkrétním řádku vyhodnocen jako TRUE, bude tento řádek součástí výsledné tabulky. Řádky, pro které je logický výraz ve druhém argumentu funkce vyhodnocen jako FALSE, nebudou součástí výsledku. V původní tabulce 'Product' jsou pouze čtyři produkty v bílé barvě. Výsledná tabulka má proto pouze tyto čtyři řádky.
Pokud bychom chtěli ve výsledné tabulce pouze vybrané sloupce, například sloupce 'Product'[ProductKey] a 'Product'[Product], můžeme vložit funkci FILTER() do funkce SELECTCOLUMS() následujícím způsobem.
Počítaná tabulka:
Funkce SELECTCOLUMNS() z tabulky uvedené v prvním argumentu funkce vrátí pouze ty sloupce, které uvedeme ve druhém a dalších argumentech této funkce. Výsledkem jsou čtyři stejné řádky jako v prvním příkladu, ale pouze se dvěma sloupci, které jsme vybrali ve vnější funkci SELECTCOLUMNS().
Při pohledu na předchozí příklad by nás mohlo napadnout, proč načítáme ve vnitřní funkci FILTER() všechny sloupce z tabulky 'Product', když ve výsledku potřebujeme pouze dva sloupce z původní tabulky. V prvním argumentu funkce FILTER() se nemusíme odkazovat na celou tabulku, ale můžeme použít také funkci vracející tabulku. Můžeme si tak vytvořit tabulku se dvěma sloupci, a pro tuto tabulku následně vyhodnotit podmínku ve druhém argumentu funkce FILTER(). Myšlenka by to mohla být dobrá, ale nefunkční.
Počítaná tabulka:
Výše uvedený výraz nebude fungovat. Narážíme tím na důležitou vlastnost všech iteračních funkcí, se kterou musíme při psaní DAX výrazů počítat. V iteračních funkcích se můžeme odkazovat pouze na sloupce, které jsou součástí iterované tabulky a její rozšířené verze. Ve funkci FILTER() nefiltrujeme model, jako je tomu u funkcí CALCULATE() nebo CALCULATETABLE(), ale filtrujeme pouze tabulku uvedenou v prvním argumentu této funkce. Pokud tedy do tabulky v prvním argumentu této funkce přidáme i sloupec s barvami produktů, můžeme již tento sloupec použít v logickém výrazu ve druhém argumentu a výsledkem bude opět tabulka se čtyřmi řádky, ale tentokrát se třemi sloupci.
Počítaná tabulka:
Výsledek můžeme vidět na následujícím obrázku.
Ve výše uvedených jednoduchých příkladech jsme pracovali s jednou podmínkou. Pokud chceme pomocí ve funkci FILTER() vyhodnotit v každém řádku tabulky více podmínek, máme opět několik možností, jak dosáhnout stejného výsledku.
Více logických filtrů ve funkci FILTER
Ve funkci FILTER() můžeme použít ve druhém argumentu pouze jeden výraz, který vrací pro každý řádek tabulky hodnotu TRUE nebo FALSE. Pokud chceme vyhodnotit v každém řádku tabulky více podmínek, můžeme tyto podmínky vložit do jedné nebo více funkcí AND(). Následující výraz vrátí tabulku produktů, které mají bílou barvu a katalogovou cenu menší než 9.
Počítaná tabulka:
Stejný výsledek dostaneme zřetězením podmínek pomocí AND operátoru, kterým je v jazyku DAX dvojitý ampersand (&&).
Počítaná tabulka:
Další možností, nicméně zřejmě méně čitelnou a asi i méně používanou, může být vložení dvou funkcí FILTER() do sebe, kdy v každé funkci vyhodnotíme jinou podmínku.
Počítaná tabulka:
Všechny tři předchozí výrazy vrací stejný výsledek, a to bílé produkty s katalogovou cenou menší než 9.
Jednotlivé logické výrazy můžeme vyhodnotit také v logickém OR vztahu. Pomocí funkce OR() můžeme například vytvořit tabulku produktů, ve které budou produkty s bílou barvou nebo produkty s katalogovou cenou menší než 7.
Počítaná tabulka:
Stejného výsledku dosáhneme použitím operátoru OR (||).
Počítaná tabulka:
Výsledkem obou předchozích výrazů bude stejná tabulka, obsahující produkty v bílé barvě nebo s produkty s cenou nižší než 7.
V logickém výrazu ve druhém argumentu funkce FILTER() můžeme použít i komplexní výpočty nebo měřítka. Před psaním podobných výrazů ale doporučuji seznámit se s principem kontextu řádku, s principem kontextu filtru a s funkcionalitou změny kontextu řádku na kontext filtru, ke které dochází při vyhodnocení funkcí CALCULATE() a CALCULATETABLE() v kontextu řádku. Dále bychom měli pamatovat, že každé měřítko je vždy na pozadí obaleno do pro autora skryté funkce CALCULATE(), a proto dochází ke změně kontextu řádku na kontext filtru také při vyhodnocení jakéhokoliv měřítka v kontextu řádku.
Logické filtry ve funkci FILTER na základě výsledku měřítka
Logickou podmínku ve funkci FILTER() můžeme vytvořit také na základě výsledku výpočtu nebo měřítka. To je jednou z velkých výhod funkce FILTER(). Pro tento příklad si můžeme vytvořit měřítko, které bude vracet sumu za prodané produkty v aktuálním kontextu vyhodnocení.
Měřítko:
Měřítko [Prodeje] pak můžeme použít jako součást logické podmínky ve druhém argumentu funkce FILTER(). Následující výraz vrací tabulku s produkty, jejichž prodeje jsou vyšší než 2 000 000 Kč.
Počítaná tabulka:
Výsledkem je tabulka se třemi produkty, pro které platí, že suma prodejů přesáhla 2 mil Kč.
Do takto vytvořené tabulky si pak můžeme jednoduše přidat také sloupec se sumou celkových prodejů, abychom viděli hodnotu pro podmínku zhmotněnou přímo v tabulce.
Počítaná tabulka:
Výsledkem je podobná tabulka jako v předchozím případě, navíc je v tabulce pouze sloupec se sumou celkových prodejů pro aktuální produkt.
Stejného výsledku můžeme dosáhnout i bez použití měřítka. Výpočet sumy prodejů pouze musíme vložit do funkce CALCULATE(), abychom vyvolali změnu kontextu řádku na kontext filtru.
Počítaná tabulka:
Funkce FILTER() může být užitečná také při použití jako filtru ve funkci CALCULATE(), jak můžete vidět v následující části příspěvku.
Funkce FILTER jako filtr ve funkci CALCULATE
Pokud ve funkci CALCULATE() používáme komplexní filtry, měli bychom vědět, jak jsou jednotlivé argumenty použitých funkcí vyhodnoceny v aktuálním kontextu. Filtry ve funkci CALCULATE() jsou vyhodnoceny v originálním kontextu vyhodnocení, ve kterém je měřítko použito. Pokud tedy budeme chtít použít funkci FILTER() jako argument ve funkci CALCULATE(), měli bychom vědět, že:
- Tabulka uvedená v prvním argumentu funkce FILTER() je vyhodnocena v originálním kontextu vyhodnocení.
- Výraz uvedený v druhém argumentu funkce FILTER() je vyhodnocen v originálním kontextu vyhodnocení a současně v nově vytvořeném kontextu řádku.
Měřítko:
Z předchozího příkladu, ve kterém jsme si vytvořili novou počítanou tabulku právě s produkty s prodeji většími než 2 mil Kč již víme, že v použitém modelu jsou takové produkty tři. Pokud vložíme nové měřítko ve vizuálu tabulky bez jakýchkoliv aktivních vnějších filtrů, výsledek bude podle očekávání číslo tři.
Jedná se o stejné tři produkty, které jsme mohli vidět při vytvoření nové počítané tabulky s hodnotami z tabulky 'Product' filtrované pouze na produkty se sumou prodejů větší než 2 mil Kč. To si můžeme ověřit, pokud přidáme do řádků použitého vizuálu názvy produktů z tabulky 'Product'.
V tabulce vidíme pouze tři produkty, protože pro všechny ostatní produkty je výsledkem měřítka [Počet produktů s prodeji > 2 mil Kč] hodnota BLANK. Co se ale stane, pokud v řádcích tabulky použijeme například roky z tabulky 'Date'?
Měřítko [Počet produktů s prodeji > 2 mil Kč] je nyní vyhodnoceno v každém řádku tabulky v kontextu filtru daného roku. Pokud se zaměříme na argumenty funkce FILTER(), tak na sedmém řádku v kódu na obrázku výše je odkaz na tabulku 'Product'. Tuto tabulku neovlivňuje filtr nastavený na aktuální rok, protože tabulka 'Date' s roky filtruje pouze tabulku 'Sales' s prodeji, díky vazbám nastaveným v použitém modelu.
Nyní se ještě jednou můžeme podívat na definici popisovaného měřítka.
Na osmém řádku kódu v měřítku [Počet produktů s prodeji > 2 mil Kč] vyhodnocujeme, zda je měřítko [Prodeje] v aktuálním kontextu větší než 2 000 000 Kč. Jaký je ale aktuální kontext, ve kterém je měřítko [Prodeje] vyhodnoceno? Na měřítko [Prodeje] působí vnější filtr s aktuálním rokem v každém řádku vizuálu tabulky. Současně na měřítko [Prodeje] působí filtr vytvořený změnou kontextu řádku na kontext filtru v každém řádku tabulky 'Produkt', v prvním argumentu funkce FILTER(). Jelikož suma prodejů žádného jednotlivého produktu v kterémkoliv roku nepřesáhla částku 2 000 000 Kč, výsledkem měřítka [Počet produktů s prodeji > 2 mil Kč] je pro každý rok hodnota BLANK. V řádku souhrnů již na výpočet měřítka nepůsobí filtr s roky, a měřítko [Prodeje] je ve druhém argumentu funkce FILTER() vyhodnoceno pouze v kontextu řádku každého produktu, a vrací tedy v každém řádku tabulky 'Product' sumu prodejů aktuálně iterovaného produktu za všechny roky. Jak již víme, v souhrnu za všechny roky přesáhla suma prodejů částku 2 mil Kč u tří produktů, proto je výsledek měřítka v řádku souhrnů číslo 3.
Vytvořme si nyní obdobné měřítko, u kterého pouze změníme částku, která tvoří hranici pro započítání konkrétního produktu do výpočtu. Hranici si můžeme změnit z 2 000 000 Kč na 1 000 000 Kč, abychom ve vizuálu viděli více produktů. Nové měřítko tedy bude vypadat následovně.
Měřítko:
Měřítko si opět vložíme do vizuálu tabulky s roky v řádcích. Na obrázku níže můžeme vidět, že pro rok 2019 jsou v použitém modelu čtyři produkty, které v měli v daném roce sumu prodejů větší než 1 000 000 Kč. V roce 2020 šlo o tři produkty, které splňovali definovanou podmínku. V řádku souhrnů můžeme vidět číslo 41. Nyní již víme, že v řádku souhrnů nepůsobí na měřítko žádný filtr s roky, a výsledek proto můžeme interpretovat tak, že v použitém modelu je 41 produktů s prodeji vyššími než 1 000 000 Kč, nicméně s prodeji souhrnně za všechny roky.
Nyní s větším počtem produktů splňující podmínku filtru můžeme lépe vidět složitost takto definovaného výpočtu. Na první pohled je zřejmé, že suma v řádku souhrnů je vyšší, než součet jednotlivých hodnot v řádcích tabulky. Nejde ovšem o problém, který by spočíval ve špatném vyhodnocení měřítka. Měřítko [Počet produktů s prodeji > 1 mil Kč] vrací přesně takové hodnoty, jaké jsme definicí měřítka požadovali. Nicméně pokud měřítko použijeme s roky v řádcích vizuálu, nechceme pravděpodobně vědět počet prodejů s celkovou částkou vyšší než 1 000 000 Kč za všechny roky, ale počet produktů se sumou prodejů vyšší než 1 000 000 Kč v každém jednotlivém roce. Proč měřítko vrací jinou hodnotu v řádku souhrnů a jinou hodnotu v jednotlivých řádcích tabulky už jsme si řekli při popisu vyhodnocení měřítka s hranicí prodejů 2 000 000 Kč. Zaměřme se nyní na to, jak dostat v takto definovaném výpočtu správný výsledek v řádku souhrnů. V řádku souhrnů potřebujeme celý výpočet vyhodnotit v každém jednotlivém roce samostatně, a výsledky výpočtu za každý jednotlivý rok následně sečíst. Takto popsanou definici výpočtu splňuje následující měřítko.
Měřítko:
Nové měřítko si můžeme zobrazit spolu s původním výpočtem ve stejném vizuálu.
Nyní již suma v řádku souhrnů odpovídá součtu jednotlivých hodnot v řádcích s roky. Zaměřme se nejdříve na vyhodnocení výpočtu v řádku souhrnů. V řádku souhrnů je výsledkem prvního argumentu funkce SUMX() (v kódu na řádku 4) tabulka s jedním sloupcem, která obsahuje všechny roky viditelné v aktuálním kontextu vyhodnocení. Funkce SUMX() je iterační funkce, která vytvoří pro každý řádek tabulky s roky (ve 4 řádku kódu) nový kontext řádku. Tento kontext řádku je díky funkci CALCULATE() přeměněn na kontext filtru. Pro každý jeden rok proto dojde k vyhodnocení výpočtu na řádku 5 až 13 v kontextu filtru aktuálně iterovaného roku. Výpočet na řádku 5 až 13 je stejný výpočet, který jsme dnes již několikrát použili pro získání počtu produktů splňujících podmínku definovanou ve druhém argumentu funkce FILTER(). Jakmile je tento výpočet vyhodnocen v kontextu nově vzniklého filtru pro každý rok, díky změně kontextu řádku na kontext filtru ve funkci CALCULATE(), dojde k sečtení všech jednotlivých výsledků za všechny roky. Výsledná hodnota v řádku souhrnů je tedy součtem hodnoty 4 pro rok 2019 a hodnoty 3 pro rok 2020. V ostatních letech neměl žádný produkt tržby větší než 1 000 000 Kč.
Na výše uvedeném jednoduchém příkladu jsem se pokusil popsat, jak jsou vyhodnoceny jednotlivé argumenty ve funkci FILTER() v případě, že tuto funkci použijeme jako filtr ve funkci CALCULATE(). Nejednalo se o žádný smysluplný výpočet, který bychom mohli používat v reportech, ale o teoretický příklad s cílem vysvětlit vlastnosti funkce FILTER(). I přesto ale platí, že informace uvedené v této části příspěvku jsou velmi důležité, pokud obecně pracujeme s komplexními filtry při definici měřítek.
Shrnutí
Příklady uvedené v tomto příspěvku jsou pouze ilustrativní s cílem popsat konkrétní funkcionalitu. Nicméně čím více příkladů a způsobů použití jednotlivých funkcí dokážeme vstřebat, tím kreativnější a efektivnější můžeme být při řešení komplexních příkladů. Nejdůležitější částí tohoto příspěvku je popis vyhodnocení funkce FILTER() při použití jako filtru ve funkci CALCULATE() při vytváření měřítek. Pokud totiž použijeme funkci FILTER() v měřítku, musíme vždy myslet na to, že argumenty ve funkci CALCULATE() jsou vyhodnoceny v originálním kontextu, ve kterém je vyhodnoceno celé měřítko. Současně pokud používáme funkci FILTER() jako argument ve funkci CALCULATE(), měli bychom v prvním argumentu funkce FILTER() používat pouze sloupce nezbytně nutné k vytvoření filtru, a ne celou tabulku, pokud to není nezbytně nutné.
Komentáře
Okomentovat