Funkce ALL() i funkce VALUES() mohou obě vracet tabulky, a v určitých situacích můžeme ke stejnému účelu použít jednu i druhou funkci. Funkce ALL() ale vždy vrací všechny hodnoty, zatímco funkce VALUES() vrací pouze hodnoty dostupné v aktuálním kontextu filtru. V tomto příspěvku si na jednoduchých příkladech ukážeme, kdy se obvykle používá funkce VALUES() a kdy naopak funkce ALL().
K tomuto tématu je k dispozici také video:
Poznámka: Funkci ALL() můžeme používat také jako funkci pro úpravu filtrů ve funkci CALCULATE(). V tomto příspěvku se ale budeme věnovat výhradně použití funkce ALL() jako funkce vracející tabulku. Použití funkce ALL() jako modifikátoru filtrů je věnován samostatný příspěvek dostupný pod tímto odkazem.
Funkci VALUES() můžeme používat s argumentem ve formě tabulky nebo s argumentem ve formě sloupce. Funkci ALL() můžeme také používat s argumentem ve formě tabulky, ve formě sloupce ale navíc také s více sloupci, pokud všechny použité sloupce pocházejí z jedné tabulky.
V tomto příspěvku budeme pro zjednodušení používat obě funkce s argumentem ve formě jednoho sloupce. Popisované principy a rozdíly ale platí také v případě použití obou funkcí s argumenty ve formě tabulky.
Protože obě funkce vrací tabulky, ať už s jedním nebo se všemi sloupci z tabulky nahrané v modelu, začneme tím, že si znázorníme rozdíly mezi funkcí ALL() a funkcí VALUES() pomocí jednoduchých DAX dotazů. Následně si ukážeme několik měřítek, ve kterých bude výběr jedné nebo druhé funkce důležitý pro dosažení požadovaného výsledku.
Hlavní rozdíl mezi DAX funkcí ALL a VALUES
Pokud použijeme funkci ALL() a funkci VALUES() s argumentem ve formě sloupce, obě tyto funkce vrací tabulku s jedinečnými hodnotami z tohoto sloupce. To si můžeme znázornit pomocí jednoduchých DAX dotazů.
Uvažujme například sloupec s kategoriemi produktů z tabulky 'Product'. Pokud bychom chtěli načíst všechny hodnoty z tohoto sloupce, můžeme použít funkci ALL() následujícím způsobem.
DAX dotaz:
Tabulka 'Product', ze které pochází sloupec 'Product'[Category], obsahuje celkem 397 řádků. Sloupec 'Product'[Category] je vyplněn v každém řádku této tabulky, nicméně obsahuje spoustu duplicitních hodnot. Protože funkce ALL() s argumentem ve formě sloupce vrací pouze jedinečné hodnoty, výsledná tabulka vypadá následovně.
Poznámka: Pokud použijeme funkci ALL() nebo funkci VALUES() s argumentem ve formě celé tabulky, tak výsledek těchto funkcí je tabulka včetně duplicitních řádků, pokud také zdrojová tabulka obsahuje duplicity. K odstranění duplicitních řádků při práci s celou tabulkou slouží funkce DISTINCT().
Podobným způsobem můžeme použít také funkci VALUES(), která také vrací jedinečné hodnoty z použitého sloupce, nicméně pouze ty hodnoty, které jsou dostupné v aktuálním kontextu filtru.
DAX dotaz:
Rozdíl mezi funkcí ALL() a VALUES() je v tom, jak jsou tyto funkce vyhodnoceny v kontextu filtru. V rámci DAX dotazů je, pokud si ho programově nevytvoříme sami, kontext filtru prázdný. Funkce VALUES() proto i v tomto případě vrací všechny jedinečné hodnoty ze sloupce 'Product'[Category].
Kontext filtru můžeme programově vytvořit pomocí funkcí CALCULATE() nebo CALCULATETABLE(). Pokud bychom si tedy chtěli nasimulovat, jak se bude funkce ALL() chovat v kontextu filtru jedné konkrétní kategorie, můžeme použít následující DAX dotaz.
DAX dotaz:
Funkce CALCULATETABLE() před vyhodnocením prvního argumentu zafiltruje model pouze na kategorie Bikes. Funkce ALL() je proto vyhodnocena v kontextu filtru této kategorie. Protože ale funkce ALL() kontext filtru ignoruje, výsledkem jsou opět všechny kategorie produktů, bez ohledu na aktuální kontext filtru.
Ve stejném kontextu filtru můžeme vyhodnotit také funkci VALUES(), například následujícím způsobem.
DAX dotaz:
Funkce VALUES(), na rozdíl od funkce ALL(), vrací pouze hodnoty dostupné v aktuálním kontextu vyhodnocení. Výsledek výše uvedeného DAX dotazu vypadá následovně.
Výsledkem předchozího DAX dotazu je tabulka pouze s jedním řádkem a kategorií Bikes, protože to je jediná dostupná kategorie v aktuální kontextu filtru, který jsme programově vytvořili pomocí funkce CALCULATETABLE().
Při práci s funkcí VALUES() musíme myslet na to, že tuto funkci ovlivňují také filtry nepřímé. Pokud před vyhodnocením funkce VALUES() jakýmkoliv způsobem zafiltrujeme tabulku, ze které načítáme hodnoty, vždy uvidíme pouze hodnoty které odpovídají aktuálnímu kontextu filtru.
To si opět můžeme ukázat na jednoduchém příkladu. V následujícím DAX dotazu před vyhodnocením funkce VALUES() zafiltrujeme model pouze na tři podkategorie produktů.
DAX dotaz:
Výsledkem výše uvedeného DAX dotazu budou pouze ty kategorie, do kterých patří podkategorie produktů použité ve filtru funkce CALCULATETABLE().
Podkategorie Road Bikes a Touring Bikes patří do kategorie Bikes, a podkategorie Shorts patří do kategorie Clothing. Při vyhodnocení funkce VALUES() v předchozím DAX dotazu jsou proto v aktuálním kontextu filtru dostupné pouze kategorie Clothing a Bikes.
Jak dobře víme, filtry jsou propagovány také mezi tabulkami, pokud to směr propagace filtrů nastavený u jednotlivých relací umožňuje.
Uvažujme například následující model, kde mají podkategorie produktů vlastní tabulku, která filtruje tabulku 'Product', ze které pochází sloupec 'Product'[Category].
Pokud nyní před vyhodnocením funkce VALUES() nastavíme filtr na podkategorie z tabulky 'Subcategory', tyto filtry budou ovlivňovat hodnoty dostupné ve sloupci 'Product'[Category], protože tabulka 'Subcategory' filtruje tabulku 'Product'.
DAX dotaz:
Výsledkem výše uvedeného DAX dotazu jsou opět pouze kategorie Bikes a Clothing.
Funkce VALUES() a ALL() se běžně používají ve výpočtech v měřítcích. Při rozhodování, kterou funkci pro daný výpočet použít, je vždy rozhodující, zda pro výpočet potřebujeme načíst všechny hodnoty, bez ohledu na aktuální filtry, nebo zda pro výpočet potřebujeme získat pouze hodnoty dostupné v aktuálním kontextu filtru.
Příklady použití DAX funkcí ALL a VALUES v měřítku
V této části příspěvku budeme pracovat s jednoduchým vizuálem matice, ve kterém v řádcích použijeme opět hodnoty ze sloupce 'Product'[Category].
Každá kategorie v každém řádku vizuálu tvoří kontext filtru. Funkce VALUES(), která respektuje kontext filtru, tak bude vracet v každém řádku vizuálu aktuální kategorii. V řádku souhrnů již na funkci VALUES() nebude působit kontext filtru obsahující kategorie, a v tomto řádku tak bude vracet funkce VALUES() všechny hodnoty. Na druhou stranu, funkce ALL() bude vracet vždy všechny kategorie produktů, bez ohledu na aktuální kontext filtru.
Typická situace, ve které záměna funkce VALUES() za funkci ALL() způsobuje nedorozumění, je výpočet pořadí na základě určitých kritérií.
Pokud zůstaneme u kategorií produktů, tak následující výpočet pořadí produktů podle hodnoty měřítka [Prodeje] nebude fungovat správně.
Měřítko:
Pokud měřítko [Pořadí kategorií (VALUES)] vložíme do vizuálu matice s kategoriemi produktů v řádcích, výsledek bude vypadat následovně.
Měřítko [Pořadí kategorií (VALUES)] vrací v každé řádku vizuálu číslo 1. Důvodem, proč nevidíme správně pořadí je právě funkce VALUES().
Abychom pochopili celý problém, budeme se jako obvykle soustředit na jednu buňku v našem vizuálu matice. Uvažujme například řádek s kategorií Bikes.
V tomto řádku, jak už víme, vrací funkce VALUES() pouze aktuální kategorii. Problémem tedy je, že se snažíme porovnat hodnotu měřítka [Prodeje], které je vyhodnoceno v kontextu filtru kategorie Bikes, s hodnotou měřítka prodeje, které je vyhodnocené uvnitř funkce RANKX() také v kontextu jedné kategorie, opět kategorie Bikes. Pokud vyhodnotíme měřítko [Prodeje] dvakrát ve stejném kontextu, a porovnáme tyto dvě hodnoty, dojdeme vždy k závěru, že aktuální kategorie je na prvním místě.
Co ve skutečnosti potřebujeme je zařadit prodeje aktuální kategorie mezi prodeje všech kategorií, a následně zjistit pořadí. Toho dosáhneme pokud nahradíme funkci VALUES() funkcí ALL(), například následujícím způsobem.
Měřítko:
Nyní, pokud jsme nahradili funkci VALUES() funkcí ALL(), může funkce RANKX() porovnat aktuální kategorii v aktuálním řádku vizuálu se všemi kategoriemi, a zjistit správné pořadí.
Při určování pořadí tak budeme obvykle používat funkci ALL() nebo další funkce, které přepisují aktuální filtry, protože potřebujeme zařadit hodnoty pro aktuální řádek vizuálu mezi všechny hodnoty ze všech řádků.
V dalším příkladu budeme pracovat s měřítkem, které vrací počet barev, ve kterých se prodávají produkty.
Měřítko:
Nové měřítko vrací jedinečný počet hodnot ze sloupce s barvami produktů v aktuálním kontextu vyhodnocení. Pokud tedy vložíme nové měřítko do našeho vizuálu matice s kategoriemi produktů v řádcích, uvidíme v kolika barvách se v každé kategorii prodávaly produkty.
Na obrázku výše pak můžeme vidět typický jev u neaditivních výpočtů, kdy součet jednotlivých hodnot v řádcích vizuálu neodpovídá celkové hodnotě v řádku souhrnů.
Pokud bychom chtěli z neaditivního výpočtu udělat aditivní, obvykle v řádku souhrnů iterujeme všechny položky zobrazené v řádcích vizuálu, a pro každou jednu položku sečteme výsledek původního měřítka. S použitím funkce ALL() by takto popsaný výpočet mohl vypadat například následovně.
Měřítko:
Pokud nové měřítko vložíme do původního vizuálu, výsledek bude vypadat následovně.
Nové měřítko vrací v každém řádku stejnou hodnotu, která odpovídá součtu hodnot původního měřítka [Počet barev] pro každou kategorii.
Co ve skutečnosti potřebujeme je získat v každém řádku s kategoriemi původní hodnotu měřítka [Počet barev], a pouze v řádku souhrnů iterovat všechny kategorie, a sečíst pro každou jednu kategorii výsledek měřítka [Počet barev].
K tomuto účelu použijeme právě funkci VALUES() namísto funkce ALL() v původním měřítku.
Měřítko:
Funkce VALUES() bude v každém řádku s kategoriemi vracet pouze aktuální kategorii, a funkce SUMX() tak vrátí výsledek měřítka [Počet barev] pouze pro tuto kategorii.
V řádku souhrnů ale funkce VALUES() vrací všechny kategorie, protože zde funkci VALUES() neovlivňují filtry z řádků vizuálu, a funkce SUMX() tak sečte výsledky měřítka [Počet barev] pro všechny kategorie.
Nová verze měřítka proto vrací v každém řádku s kategoriemi stejnou hodnotu jako původní měřítko [Počet barev], nicméně v řádku souhrnů je výsledkem součet všech hodnot tohoto původního měřítka za jednotlivé kategorie.
Jak jsme si ukazovali v úvodu tohoto článku, tak funkce VALUES() i funkce ALL() vrací stejné výsledky, pokud jsou vyhodnoceny v prázdném kontextu filtru. Totéž ale platí v situaci, kdy aktuální kontext filtru nefiltruje tabulku, ze které pocházejí sloupce použité ve funkci VALUES(). Uvažujme například následující vizuál s roky v řádcích a s měřítkem [Prodeje] v hodnotách.
Měřítko:
Funkce TOPN() načítá ve druhém argumentu všechny produkty pomocí funkce ALL(), ze kterých následně vybere pět nejlepších podle hodnoty měřítka prodeje. Pokud nové měřítko vložíme do vizuálu, dostaneme požadovaný výsledek.
Stejného výsledku ale dosáhneme také pokud nahradíme funkci ALL() funkcí VALUES(), protože tabulka 'Date', ze které pocházejí roky v řádcích vizuálu, nefiltruje tabulku 'Product'.
Jak můžeme vidět v náhledu modelu, tabulka 'Date' filtruje tabulku 'Sales', ale tyto filtry se již nedostanou do tabulky 'Product'. Funkce VALUES() tak v tomto konkrétním vizuálu načte také všechny produkty.
Měřítko:
Pokud novou verzi výpočtu vložíme do původního vizuálu, uvidíme, že obě varianty výpočtu, tedy první s použitím funkce ALL() i druhá s použitím funkce VALUES(), vrací stejné výsledky.
Přestože obě měřítka vrací v kontextu roků z datumové tabulky stejné výsledky, jedná se o rozdílné výpočty a v jiném kontextu filtru by mohla měřítka vracet rozdílné výsledky. Stejně jako v případě výpočtů, ve kterých tvoříme pořadí, tak i u výpočtů, ve kterých vybíráme pouze určitou podmnožinu položek na základě určitých kritérií, pracujeme obvykle s funkcí ALL() nebo ALLSELETED().
Shrnutí
Hlavní rozdíl mezi funkcí ALL() a funkcí VALUES() je v tom, jak tyto funkce fungují v kontextu filtru. Funkce ALL() ignoruje kontext filtru, a vrací vždy všechny hodnoty. Na druhou stranu, funkce VALUES() respektuje kontext filtru, a vrací pouze ty hodnoty, které jsou dostupné právě v aktuálním kontextu filtru. To, zda chceme přepisovat vnější filtry nebo vrátit hodnoty dostupné v aktuálním kontextu filtru je pak hlavní kritérium pro výběr jedné nebo druhé funkce.
Komentáře
Okomentovat