Funkce FILTER() a funkce CALCULATETABLE() vrací obě tabulky a v určitých typech výpočtů mohou být zaměnitelné. I přesto je ale způsob vyhodnocení obou funkcí jiný a každá funkce byla vytvořena za jiným účelem. Na příkladech v tomto příspěvku si vysvětlíme jak obě funkce fungují a jaké jsou hlavní rozdíly mezi funkcí FILTER() a funkcí CALCULATETABLE().
K tomuto tématu je k dispozici také video:
Ačkoliv funkce FILTER() i funkce CALCULATETABLE() vrací obě tabulky, používají se tyto funkce běžně také jako součást výpočtů v rámci definice měřítek nebo počítaných sloupců, jejichž výsledkem je vždy skalární hodnota. V tomto příspěvku ale budeme pro lepší názornost pracovat pouze s DAX dotazy, protože výsledkem každého DAX dotazu je právě tabulka.
Začít můžeme velmi jednoduchým příkladem, ve kterém budou obě funkce vracet stejné výsledky. Cílem následujícího DAX dotazu bude vypsat pouze ty řádky z tabulky 'Product', pro které platí že katalogová cena aktuálního produktu je vyšší než 3 500.
Pomocí funkce CALUCLATETABLE() můžeme tyto produkty získat následujícím způsobem.
DAX dotaz:
Tabulka s produkty obsahuje v použitém modelu 397 řádků. Výsledkem výše uvedeného dotazu ale bude pouze podmnožina produktů, protože pouze pět produktů má katalogovou cenu vyšší než 3 500.
Stejného výsledku můžeme dosáhnout také s použitím funkce FILTER().
DAX dotaz:
I druhý DAX dotaz vrací stejné řádky z tabulky 'Product' jako v případě použití funkce CALCULATETABLE().
V tomto konkrétním příkladu vrací obě funkce stejné výsledky, nicméně způsob, jakým fungují je odlišný.
Funkce FILTER() je iterační funkce, která nejdříve vyhodnotí tabulku zadanou v prvním argumentu, a to v aktuálním kontextu vyhodnocení (aktuální kontext řádku a aktuální kontext filtru). Následně je pro každý řádek tabulky vyhodnocen druhý argument funkce FILTER(), a to v nově vytvořeném kontextu řádku tabulky zadané v prvním argumentu a dále ve vnějším kontextu vyhodnocení, ve kterém je celá funkce použita. V našem příkladu je v rámci DAX dotazu vnější kontext filtru a vnější kontext řádku prázdný, to ale obvykle neplatí při použití funkce FILTER() v měřítku kde s vnějším kontextem vyhodnocení musíme počítat.
Výsledkem funkce FILTER() jsou pak pouze ty řádky z tabulky zadané v prvním argumentu, pro které vrací druhý argument hodnotu TRUE. Pokud je pro aktuální řádek výsledek podmínky hodnota FALSE, takovýto řádek bude z výsledku vyloučen.
Funkce CALCULATETABLE() funguje na zcela odlišném principu. Funkce CALCULATETABLE() se chová přesně jako funkce CALCULATE() pouze s tím rozdílem, že funkce CALCULATETABLE() vrací tabulku, a funkce CALCULATE() vrací skalární hodnotu. V porovnání s funkcí FILTER() tak ve funkci CALCULATETABLE() dojde nejdříve k vyhodnocení všech filtrů ve druhém a dalších argumentech, které jsou následně aplikované na model, a až poté je vyhodnocena tabulka v prvním argumentu, a to v novém kontextu filtru vytvořeným funkcí CALCULATETABLE().
Nejdůležitější rozdíl je v tom že funkce FILTER() filtruje tabulku zadanou v prvním argumentu, zatímco funkce CALCULATETABLE() filtruje model před vyhodnocením tabulky v prvním argumentu. Všechny další rozdíly se odvíjí od výše uvedeného.
Ve funkci FILTER() můžeme používat jakoukoliv tabulku, včetně virtuální tabulky vytvořené pouze uvnitř DAX výpočtu, a každou tabulku můžeme filtrovat pomocí jakéhokoliv DAX výrazu, který vrací pro aktuální řádek této tabulky hodnotu TRUE nebo FALSE.
Naproti tomu ve funkci CALCULATETABLE() můžeme použít ve filtrech pouze sloupce nebo tabulky které jsou v modelu. Ve funkci CALCULATETABLE() můžeme dále používat jakékoliv modifikátory filtrů, stejně jako v případě funkce CALCULATE(), mezi které patří například funkce REMOVEFILTERS(), ALLEXCEPT(), CROSSFILTER(), USERELATIONSHIP() a tak dále. Filtry ve funkci CALCULATETABLE() jsou pak vždy tabulky které filtrují model, stejně jako je tomu u funkce CALCULATE().
Všechny výše popsané rozdíly mezi funkcí FILER() a funkcí CALCULATETABLE() si postupně ukážeme na příkladech.
Uvažujme například situaci, kdy bychom chtěli vypsat názvy produktů, to znamená hodnoty z jednoho konkrétního sloupce z tabulky 'Product', a to pouze produktů v určité kategorii, například v kategorii "Bikes". Toho můžeme dosáhnout jednoduše pomocí funkce CALCULATETABLE() následujícím způsobem.
DAX dotaz:
Ve výše uvedeném DAX dotazu dojde nejdříve ve funkci CALCULATETABLE() k vyhodnocení filtru, který je následně aplikovaný na model. Až poté je vyhodnocena funkce VALUES() v prvním argumentu, a to právě v kontextu filtru kategorie "Bikes". Výsledkem jsou proto pouze produkty z této kategorie.
V tomto konkrétním příkladu nemůžeme jednoduše nahradit funkci CALCULATETABLE() funkcí FILTER(), protože ve funkci FILTER() se nemůžeme při sestavování logické podmínky odkazovat na všechny sloupce z modelu, ale pouze na ty sloupce které jsou v tabulce v prvním argumentu.
Alternativní výpočet s použitím funkce FILTER() by tam mohl vypadat například následovně.
DAX dotaz:
Abychom mohli zafiltrovat produkty pouze na konkrétní produkty z určité kategorie pomocí funkce FILTER(), musí být kategorie produktů součástí tabulky zadané v prvním argumentu této funkce.
V dalším příkladu si můžeme ukázat opačnou situaci, tedy situaci kdy bude funkce FILTER() ideální funkcí pro dosažení požadovaného výsledku. Ve funkci CALCULATETABLE() totiž nemůžeme použít pro sestavení logické podmínky ve druhém argumentu výraz založený na hodnotách měřítek nebo na základě komplexní výpočtů které vracejí hodnotu TRUE nebo FALSE, jako je tomu ve funkci FILTER().
Uvažujme například následující jednoduchý DAX dotaz, který bude vracet pouze ty produkty, pro které platí že prodeje těchto produktů jsou větší než 2 500 000.
DAX dotaz:
Funkce FILTER() ve výše uvedeném DAX dotazu nejdříve načte všechny produkty pomocí funkce VALUES(). Následně je v kontextu řádku každého produktu vyhodnoceno měřítko [Prodeje]. Pokud je hodnota měřítka [Prodeje] pro aktuální produkt větší než 2 500 000, výsledkem podmínky bude hodnota TRUE a takovýto produkt bude zahrnut do výsledku. Pokud bude hodnota měřítka [Prodeje] pro aktuální produkt menší nebo rovna hodnotě 2 500 000, takovýto produkt bude z výsledku vyloučen. Výsledkem výše uvedeného DAX dotazu je pouze sedm produktů, protože pouze tyto produkty mají prodeje vyšší než 2 500 000.
V tomto konkrétním DAX dotazu nemůžeme funkci FILTER() nahradit funkcí CALCULATETABLE(), protože logická podmínka je založena na hodnotě měřítka, a hodnota měřítka není uložena v žádném sloupci v modelu, které můžeme používat ve filtrech funkce CALCULATETABLE().
S funkcí FILTER() můžeme pracovat také s tabulkami které jsou vytvořené a dostupné pouze v rámci DAX výpočtu. Pokud bychom například chtěli vypsat produkty které mají prodeje vyšší než 2 500 000, a to včetně jejich prodejů, můžeme použít následující DAX dotaz.
DAX dotaz:
Ve výše uvedeném DAX dotazu si nejdříve do proměnné ProduktyAProdeje ukládáme všechny produkty a jejich prodeje, kdy hodnota prodejů je v novém sloupci [@Prodeje] vytvořeným funkcí ADDCOLUMNS(). Virtuální tabulka ProduktyAProdeje, která obsahuje dva sloupce, je následně použita ve funkci FILTER(), kde tuto tabulku filtrujeme pouze na ty produkty pro které platí že hodnota prodejů ve sloupci [@Prodeje] je větší než 2 500 000. Celý DAX dotaz je navíc seřazen sestupně podle hodnoty prodejů.
Výsledkem je opět 7 stejných produktů, které splňují podmínku definovanou ve funkci FILTER(). Ani v tomto případě nemůže být funkce FILTER() nahrazena funkcí CALCULATETABLE(), pokud není hodnota prodejů uložena přímo v tabulce nahrané v modelu.
Ukázat si můžeme také příklad, ve kterém můžeme použít pouze funkci CALCULATETABLE() a kde funkce FILTER() není alternativou.
Představme si situaci, kdy bychom chtěli vypsat produkty které si koupil jeden konkrétní zákazník. Zákazníci jsou uloženi v tabulce 'Customer' a produkty které chceme vypsat jsou v tabulce 'Product'.
Tabulka 'Customer' nemá přímou relaci s tabulkou 'Product'. Pokud tedy zafiltrujeme model na jednoho konkrétního zákazníka, například zákazníka "Karla She", tento filtr bude propagován do tabulky 'Sales', ale nedostane se k tabulce 'Product', ze které chceme získat názvy produktů které zakoupil náš zákazník. Ve funkci CALCULATETABLE() ale můžeme mimo jiné manipulovat s relacemi v modelu, a v rámci DAX dotazu tak můžeme dočasně aktivovat obousměrnou propagaci filtrů mezi tabulkou 'Sales' a tabulkou 'Product', díky čemuž se filtry z tabulky 'Customer' dostanou přes tabulku 'Sales' až k tabulce 'Product'.
DAX dotaz:
Výsledkem výše uvedeného DAX dotazu pak budou pouze ty produkty, které zakoupila zákaznice "Karla She".
V tomto případě nemůže být funkce CALCULATETABLE() nahrazena funkcí FILTER(), protože možnosti manipulovat s relacemi v modelu je k dispozici pouze ve funkcích CALCULATE() a CALCULATETABLE().
Ve funkci CALCULATE() a CALCULATETABLE() můžeme pro sestavení jednoho predikátu použít také více sloupců, pokud tyto sloupce pocházejí ze stejné tabulky. Následující DAX dotaz tak bude vracet pouze ty záznamy z faktové tabulky 'Sales', pro které platí že aktuální produkt má rozdíl mezi katalogovou cenou a náklady vyšší než 1 477.
DAX dotaz:
Stejného výsledku můžeme v tomto konkrétním příkladu dosáhnout také při použití funkce FILTER(). Ve funkci FILTER() ale musíme pro přístup ke sloupcům 'Product'[List Price] a 'Product'[Standard Cost] použít funkci RELATED().
DAX dotaz:
Přistupovat ke sloupcům z jiné tabulky než z tabulky zadané v prvním argumentu funkce FILTER() můžeme pouze za určitých okolností. Zaprvé v prvním argumentu funkce FILTER() musíme použít celou tabulku, a za druhé sloupce ke kterým chceme přistupovat pomocí funkce RELATED() musí patřit do rozšířené verze tabulky zadané v prvním argumentu. Oba DAX dotazy budou nyní vracet stejné výsledky, nicméně způsob jakým funkce FILTER() a funkce CALCULATETABLE() fungují je jiný.
Ačkoliv oba dva poslední DAX dotazy vrací stejné výsledky, v tomto konkrétním příkladu je lepší použít funkci CALCULATETABLE(), protože ta má obecně více možností jakým způsobem bude na pozadí vyhodnocena, zatímco vyhodnocení funkce FILTER() je při práci se sloupci nahranými v modelu relativně striktní.
Rozhodnutí kdy použít funkci CALCULATETABLE() a kdy funkci FILTER() je ve většině situací jednoduché.
Obecně platí že vždy když filtrujeme sloupce nebo celé tabulky nahrané v modelu, měli bychom použít funkci CALCULATETABLE(), protože je obvykle výhodnější nejdříve zafiltrovat model a následně vypsat tabulku. Na druhou stranu ve funkci FILTER() můžeme filtrovat také tabulky které nejsou nahrané v modelu, a to pomocí podmínek které mohou obsahovat jakékoliv výpočty které vrací TRUE nebo FALSE. To znamená že funkci FILTER() budeme používat vždy když není možné použít funkci CALCULATETABLE().
Při rozhodování zda použít funkci CALCULATETABLE() nebo funkci FILTER() si ale ještě musíme dát pozor na to, že funkce CALCULATETABLE(), stejně jako funkce CALCULATE(), mění aktuální kontext řádku na kontext filtru, což přirozeně snižuje počet situací, kdy můžeme brát použití funkce FILTER() a funkce CALCULATETABLE() jako ekvivalentní.
Komentáře
Okomentovat