DAX funkce ALL() a REMOVEFILTERS() mohou být v určitém kontextu použity ke stejnému účelu a s dosažením stejných výsledků. V tomto příspěvku si vysvětlíme, proč v jazyku DAX existují dvě funkce, které můžeme brát za určitých okolností za ekvivalentní a jaký je mezi těmito dvěma funkcemi rozdíl.
K tomuto tématu je k dispozici také video:
Funkce ALL() je v jazyku DAX k dispozici od první verze vydání DAX Enginu. Na druhou stranu, funkce REMOVEFILTERS() byla do knihovny DAX funkcí přidána až v roce 2019. Abychom pochopili důvod pro přidání funkce REMOVEFILTERS(), musíme si nejdříve vysvětlit dva možné způsoby použití funkce ALL().
Jak už název funkce ALL() napovídá, tato funkce vrací všechny hodnoty ze sloupce, sloupců anebo z tabulek, které zadáme v argumentech této funkce. Tato definice funkce ALL() ale není úplná, jak si trochu oklikou vysvětlíme prostřednictvím následujícího příkladu.
V příkladu budeme pracovat pouze se třemi tabulkami ze cvičného Power BI souboru Adventure Works DW 2020.pbix. Power BI soubor s řešením je k dispozici ke stažení níže pod tímto příspěvkem.
Tabulky 'Date' a 'Product' jsou obě dimenzní tabulky, které filtrují tabulku 'Sales'. Filtry z tabulky 'Date' již ale nejsou propagovány do tabulky 'Product'. Stejně tak, filtry z tabulky 'Product' nejsou propagovány do tabulky 'Date'.
Představme si nyní situaci, kdy bychom chtěli zjistit, kolik produktů se prodávalo v jednotlivých letech. Názvy produktů jsou uloženy ve sloupci 'Product'[Product], a jejich počet můžeme zjistit například pomocí funkce DISTINCTCOUNT() následujícím způsobem.
Měřítko:
Pokud vložíme do řádků vizuálu matice roky z tabulky 'Date', a do hodnot stejného vizuálu nové měřítko [Počet produktů], výsledek bude vypadat následovně.
Měřítko [Počet produktů] vrací v každém řádku vizuálu stejnou hodnotu. To by nemělo být překvapující. Na výpočet působí v každém řádku vizuálu filtr na aktuální rok. Roky jsou z tabulky 'Date'. Jak už jsme si ale řekli dříve, tabulka 'Date' nefiltruje tabulku 'Product', ze které zjišťujeme počet produktů v měřítku [Počet produktů]. Hodnota, kterou vrací měřítko [Počet produktů] tak odpovídá počtu všech produktů v tabulce 'Product', bez ohledu na aktuální rok.
Dosáhnout požadovaného výsledku, tedy počtu produktů, které se prodávaly v daném roce, můžeme více různými způsoby. Cílem každé varianty výpočtu by bylo přenést filtr z tabulky 'Date' přes tabulku 'Sales' až do tabulky 'Product'. My k tomuto účelu použijeme asi ten nejhorší možný způsob z pohledu výkonnosti, nicméně způsob, který bude vhodně sloužit pro následnou demonstraci chování funkce ALL().
Měřítko:
Pokud vložíme měřítko [Počet produktů (Sales)] do našeho vizuálu, výsledek bude vypadat následovně.
Měřítko [Počet produktů (Sales)] vrací v každém řádku vizuálu počet produktů, které se prodávaly v daném roce nebo za celé období, v případě řádku souhrnů Celkem.
Důvod, proč nová verze výpočtu vrací požadované výsledky je, že tabulka 'Sales' ve své rozšířené verzi obsahuje také celou tabulku 'Product'. Pokud tedy použijeme ve filtru funkce CALCULATE() celou tabulku 'Sales', filtrujeme ve skutečnosti také tabulku 'Product'. Jinými slovy, ve filtru funkce CALCULATE() jsme pomocí tabulky 'Sales' vytvořili most mezi tabulkou 'Date' a tabulkou 'Product'. Koncept rozšířených tabulek je podrobněji popsán v samostatném příspěvku, který je dostupný pod tímto odkazem.
Pokud se vrátíme zpět k rozdílu mezi funkcí ALL() a funkcí REMOVEFILTERS(), tak pro vysvětlení celé problematiky se zaměříme výhradně na řádek souhrnů Celkem v našem vizuálu matice.
V řádku souhrnů Celkem nepůsobí na ani jedno měřítko žádný vnější filtr, jako je tomu v jednotlivých řádcích s roky. V tomto řádku pak vrací měřítko [Počet produktů] hodnotu 295, což jsou všechny produkty v tabulce 'Product'. Měřítko [Počet produktů (Sales)] vrací v řádku souhrnů číslo 266, což je počet všech produktů, které se prodávaly a mají tak alespoň jeden záznam v tabulce 'Sales'. Zbylých 29 produktů nemá záznam v tabulce 'Sales' a vůbec se neprodávalo. To znamená, že i když v řádku souhrnů není tabulka 'Sales', kterou jsme použili ve filtru funkce CALCULATE() v měřítku [Počet produktů (Sales)], nijak filtrována, tato tabulka tvoří sama o sobě filtr a i v řádku souhrnů stále aktivně filtruje výpočet v prvním argumentu funkce CALCULATE().
Než se posuneme o krok dále k dalšímu měřítku, doporučuji ještě jednou přečíst předchozí odstavec a uvědomit si, že jakmile použijeme ve filtru funkce CALCULATE() jakékoliv hodnoty z jednoho nebo více sloupců modelu anebo celou tabulku jako v našem případě, tyto filtry jsou stále aktivní a působí na výpočet v prvním argumentu funkce CALCULATE().
Pokud si tento fakt uvědomíme, přirozeně bychom měli dojít k názoru, že následující měřítko bude v každém řádku našeho vizuálu vracet hodnotu 266, tedy počet produktů, které mají alespoň jeden záznam v tabulce 'Sales'.
Měřítko:
Nové měřítko ale vrací v každém řádku vizuálu hodnotu 295, tedy počet všech produktů, bez ohledu na to, jestli má nebo nemá daný produkt záznam v tabulce 'Sales'.
Důvod, proč měřítko [Počet produktů (ALL Sales)] vrací v každém řádku vizuálu číslo 295, a ne číslo 266, je rozdíl ve fungování funkce ALL() pokud ji použijeme přímo ve filtru funkce CALCULATE(), anebo pokud ji použijeme jako funkci vracející tabulku. Pokud totiž použijeme funkci ALL() přímo ve filtru funkce CALCULATE(), tak funkce ALL() nevrací žádné hodnoty, ale pouze odstraňuje filtry ze sloupců anebo z tabulek. Zde je velmi důležité vnímat rozdíl mezi odstraněním filtrů, a přidáním filtru se všemi hodnotami.
Uvažuje například následující, opět ne úplně efektivní měřítko, které bude vracet v každém řádku vizuálu číslo 266, tedy počet produktů, které mají záznam v tabulce 'Sales'.
Měřítko:
V měřítku [Počet produktů (ALL FILTER Sales)] není funkce ALL() s tabulkou 'Sales' použita přímo ve filtru funkce CALCULATE(), ale funkce ALL() je nyní použita v prvním argumentu funkce FILTER(). Funkce FILTER() tak nejdříve načte všechny řádky z tabulky 'Sales', a následně vyhodnotí druhý argument, kde obvykle píšeme logickou podmínku vracející hodnoty TRUE nebo FALSE. Pokud použijeme ve druhém argumentu funkce FILTER() přímo hodnotu TRUE, říkáme že chceme vrátit všechny řádky z tabulky v prvním argumentu. Výsledná tabulka, kterou vrací funkce FILTER(), obsahuje všechny řádky a všechny sloupce z rozšířené verze tabulky 'Sales', a tato tabulka je použita ve filtru funkce CALCULATE() před vyhodnocením prvního argumentu.
Co tedy vyplývá ze všech těchto příkladů? Pokud použijeme funkci ALL() přímo ve filtru funkce CALCULATE(), tato funkce nevrací hodnoty z tabulky nebo ze sloupců, ale pouze odstraňuje filtry z těchto tabulek a jejich rozšířených verzí nebo ze sloupců. Pokud ale použijeme funkci ALL() kdekoliv jinde než přímo ve filtrech funkce CALCULATE(), vrací funkce ALL() hodnoty z tabulky nebo sloupců, které použijeme v argumentech této funkce.
Rozdíl mezi odstraněním filtrů z tabulky nebo sloupců, a mezi filtrováním všech hodnot z tabulky a nebo sloupců, může být zásadní, jak můžeme vidět na předchozím obrázku, kde měřítko [Počet produktů (ALL Sales)] vrací jiné hodnoty, než měřítko [Počet produktů (ALL FILTER Sales)].
Právě kvůli této skryté nejednoznačnosti při používání funkce ALL() byla v roce 2019 přidána do jazyka DAX nová funkce REMOVEFILTERS().
Měřítko:
Funkce REMOVEFILTERS(), na rozdíl od funkce ALL(), slouží pouze k odstraňování filtrů ze sloupců nebo z tabulek před vyhodnocením prvního argumentu ve funkci CALCULATE() nebo CALCULATETABLE().
Funkce REMOVEFILTERS() se tedy chová naprosto přesně jako funkce ALL(), pokud funkci ALL() použijeme přímo ve filtrech funkce CALCULATE() nebo CALCULATETABLE(). Pokud ale použijeme funkci REMOVEFILTERS(), víme naprosto přesně že odstraňujeme filtry. Pokud ale použijeme přímo ve filtru funkce CALCULATE() funkci ALL(), můžeme nesprávně dojít k závěru, že načítáme všechny hodnoty, což, jak jsme si ukázali na příkladech v tomto příspěvku, není pravda.
Co tedy z toho všeho vyplývá pro nás jako pro autory DAX výpočtů? Funkce ALL() použitá přímo ve filtrech funkce CALCULATE() pouze odstraňuje filtry, a nevrací žádné hodnoty. Pokud funkci ALL() použijeme kdekoliv jinde než přímo ve filtru funkce CALCULATE(), tak funkce ALL() vrací všechny hodnoty, bez ohledu na vnější filtry.
Pokud tedy odstraňujeme filtry před vyhodnocením prvního argumentu funkce CALCULATE(), můžeme použít jak funkci ALL(), tak funkci REMOVEFILTERS(), a vše bude naprosto stejné. Pokud si ale zvykneme, že k odstraňování filtrů ve funkci CALCULATE() slouží výhradně funkce REMOVEFILTERS(), nedojdeme nikdy k falešnému závěru, že pomocí ALL() přidáváme filtr se všemi hodnotami, což, jak jsme si popsali v tomto příspěvku, není pravda, a rozdíl mezi přidáním filtru se všemi hodnotami a mezi odstraněním filtrů může být v některých typech výpočtů zásadní.
Komentáře
Okomentovat