Funkce ALLEXCEPT() může být v jazyku DAX použita jako funkce vracející tabulku nebo jako modifikátor funkcí CALCULATE() a CALCULATETABLE(). Tento článek popisuje chování funkce ALLEXCEPT() při použití jako modifikátoru CALCULATE(). ALLEXCEPT() odstraňuje filtry z tabulky a z její rozšířené verze, kromě filtrů aplikovaných na sloupce, které jsou uvedené v druhém a dalších argumentech funkce.
Příklad ALLEXCEPT
Syntaxe funkce ALLEXCEPT:
Prvním argumentem funkce ALLEXCEPT() je tabulka, ze které chceme odstranit filtry. Druhý a další argumenty jsou sloupce, na kterých chceme filtry zachovat. Pokud pracujeme s tabulkou, která ve své rozšířené verzi obsahuje i další tabulky, mohou být ve druhém a dalších argumentech této funkce uvedeny názvy celých tabulek, které patří do rozšířené verze tabulky uvedené v prvním argumentu.
V příkladech budeme pracovat se cvičným Power BI souborem Adventure Works DW 2020, který obsahuje tabulky zobrazené na následujícím obrázku.
Zvýrazněná tabulka 'Product' nemá ve své rozšířené verzi žádné další tabulky. Pokud bychom chtěli při výpočtu sumy prodejů odstranit všechny filtry z tabulky 'Product', a ponechat filtry na sloupci s kategoriemi produktů ze stejné tabulky, výpočet by mohl vypadat následovně.
Měřítko:
Pro porovnání budeme pracovat také s měřítkem [Prodeje], které má následující definici a vrací sumu za prodané produkty v aktuálním kontextu vyhodnocení.
Měřítko:
Pokud do Power BI vizuálu Tabulky vložíme do řádků roky, a do hodnot měřítko [Prodeje] a měřítko [(Product) Prodeje ALLEXCEPT Category]. Výsledek obou měřítek je ve výchozím stavu stejný.
Výsledek obou měřítek je stejný, protože roky v řádcích tabulky nejsou součástí tabulky 'Product', ze které odstraňujeme filtry. Pokud budeme v reportu pomocí průřezu filtrovat pouze modrou a černou barvu, hodnoty použitých měřítek již budou jiné.
V měřítku [(Product) Prodeje ALLEXCEPT Category] se po použití filtru na sloupec s barvami (filtr v průřezu) nic nezměnilo. Sloupec s barvami 'Product'[Color] patří do tabulky 'Product'. Při výpočtu sumy prodejů dojde díky ALLEXCEPT() k odstranění všech filtrů z tabulky 'Product', kromě filtrů aplikovaných na sloupec s kategoriemi produktů. Měřítko [Prodeje] pak filtry z průřezů respektuje a vrací v každém roce pouze sumu prodejů za pro produkty s černou a modrou barvou.
Hodnoty obou měřítek jsou stejné, protože ve funkci ALLEXCEPT(), kterou jsme použili jako modifikátor výsledku měřítka [(Product) Prodeje ALLEXCEPT Category], odstraňujeme filtry ze všech sloupců tabulky 'Product', kromě sloupce s kategoriemi produktů, který je filtrován v průřezu.
Měřítka:
Pokud obě verze měřítek vložíme do vizuálu v reportu, můžeme na obrázku níže vidět, že obě verze vrací stejné hodnoty. V prvním měřítku odstraníme všechny filtry z tabulky 'Product', kromě filtrů na sloupec s kategoriemi. V druhém měřítku pomocí funkce REMOVEFILTERS() odstraňujeme všechny filtry z tabulky 'Product', opět kromě filtrů aplikovaných na sloupec s kategoriemi, který ve výčtu sloupců chybí. Jedná se tedy o zápis stejného dvěma způsoby.
Výsledek měřítka [(Product) Prodeje ALLEXCEPT Category], stejně jako jeho ekvivalentu s odlišným zápisem filtrů, vrací v každém řádku zobrazené tabulky sumu prodejů za v řádcích uvedený fiskální rok a za vybrané kategorie v průřezu, bez ohledu na filtrované barvy.
Funkce ALLEXCEPT a rozšířené tabulky
Stejně jako v prvním příkladu při práci s tabulkou 'Product', i nyní vytvoříme podobné měřítko, pouze budeme odstraňovat pomocí funkce ALLEXCEPT() filtry z tabulky 'Sales' a její rozšířené verze, a opět ponecháme filtry na sloupci s kategoriemi.
Měřítko:
Nyní, pokud vložíme nové měřítko [(Sales) Prodeje ALLEXCEPT Category] do původního reportu s roky v řádcích vizuálu Tabulky, můžeme si porovnat verzi výpočtu odstraňující filtry z tabulky 'Product' a verzi výpočtu odstraňující filtry z tabulky 'Sales'.
Při pohledu na výsledek nového měřítka [(Sales) Prodeje ALLEXCEPT Category] je zřejmé, že hodnota v každém řádků odpovídá celkovým prodejům za všechny roky, bez ohledu filtr s fiskálními roky, které jsou uvedeny v řádcích vizuálu.
Měřítko:
Výsledek obou měřítek již bude opět stejný.
Na předchozím příkladu bylo vidět, jak je důležité při práci s filtry brát v úvahu i rozšířené verze tabulek v modelu. V měřítku [(Sales) Prodeje ALLEXCEPT Category] jsme sice přesně uvedli, že chceme odstranit filtry z tabulky 'Sales', nicméně došlo také k odstranění filtrů i z tabulky 'Date', která obsahuje sloupec s roky, protože ta je součástí rozšířené verze tabulky 'Sales'.
Měřítko:
Pokud vložíme nové měřítko do stejného vizuálu v Power BI, který má v řádcích kategorie, a v průřezu nastavíme filtr pouze na černou a modrou barvu, výsledek bude vypadat následovně.
Výsledek nového měřítka [(Sales) Prodeje ALLEXCEPT Category a Color] je stejný jako výsledek měřítka [Prodeje], protože nové měřítko respektuje filtry aplikované na sloupec s barvami v průřezu, tak i filtr aplikovaný na sloupec s kategoriemi v řádcích vizuálu tabulky. Pokud v průřezu s roky zafiltrujeme fiskální rok 2018, výsledek je následující.
Měřítko [Prodeje] ve zvýrazněné buňce představuje sumu prodejů černých a modrých kol za fiskální rok 2018. Měřítko [(Sales) Prodeje ALLEXCEPT Category a Color] ve zvýrazněné buňce představuje sumu prodejů modrých a černých kol, ale za všechny roky.
Informace o dalších funkcích určených pro úpravu filtrů ve funkci CALCULATE() můžete najít na stránce Jazyk DAX. Praktické příklady pak můžete najít na stránce DAX - Příklady.
Komentáře
Okomentovat