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 uvedené v prvním argumentu v její rozšířené verzi, kromě filtrů aplikovaných na sloupce, které jsou uvedené v druhém a dalších argumentech.
Příklady použití funkce ALLEXCEPT v měřítku
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.
Poznámka: Koncept rozšířených tabulek je podrobně popsán v samostatném příspěvku pod tímto odkazem.
V příkladech budeme pracovat se cvičným Power BI souborem Adventure Works DW 2020 v jeho zjednodušené formě, který je k dispozici ke stažení níže pod tímto příspěvkem.
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 Tabulka vložíme do řádků roky, a do hodnot měřítko [Prodeje] a měřítko [Prodeje ALL (Product) EXCEPT (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 [Prodeje ALL (Product) EXCEPT (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 [Prodeje ALL (Product) EXCEPT (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 [Prodeje ALL (Product) EXCEPT (Category)], stejně jako jeho ekvivalentu s odlišným způsobem odstranění filtrů, vrací v každém řádku zobrazené tabulky sumu prodejů za v řádcích uvedený rok a za vybrané kategorie v průřezu, bez ohledu na filtrované barvy.
Funkce ALLEXCEPT a rozšířené tabulky
Při použití ALLEXCEPT() jako modifikátoru v CALCULATE() tato funkce odstraní všechny filtry z tabulky uvedené v prvním argumentu funkce a z její rozšířené verze, a ponechá pouze filtry na sloupcích z rozšířené verze tabulky v prvním argumentu, které jsou uvedeny v dalších argumentech funkce. Druhým a dalšími argumenty funkce mohou být sloupce z tabulky a z její rozšířené verze, nebo také celé tabulky patřící do rozšířené verze tabulky uvedené v prvním argumentu funkce.
Pokud se opět vrátíme k modelu v Power BI souboru Adventure Works DW 2020, můžeme vidět, že tabulka 'Sales' ve své rozšířené verzi obsahuje všechny tabulky v celém modelu, protože jsou všechny ostatní tabulky v modelu ve vztahu s tabulkou 'Sales' na straně ONE.
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 [Prodeje ALL (Sales) EXCEPT (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 [Prodeje ALL (Sales) EXCEPT (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 roky, které jsou uvedeny v řádcích vizuálu.
Na druhou stranu původní měřítko [Prodeje ALL (Product) EXCEPT (Category)] je filtrem působícím z řádků tabulky ovlivněno. Jelikož je tabulka 'Date' s roky součástí rozšířené verze tabulky 'Sales', tak stejného výsledku obou měřítek dosáhneme v případě, že u nového měřítka, které odstraňuje filtry z tabulky 'Sales', uvedeme sloupec s roky v argumentu funkce ALLEXCEPT() tak, aby z tohoto sloupce nebyly filtry odstraněny.
Měřítko:
Výsledek obou měřítek již bude nyní 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 [Prodeje ALL (Sales) EXCEPT (Category)] jsme sice přesně uvedli, že chceme odstranit filtry z tabulky 'Sales', nicméně došlo také k odstranění filtrů z tabulky 'Date', která obsahuje sloupec s roky, protože ta je součástí rozšířené verze tabulky 'Sales'.
V další ukázce vytvoříme měřítko, které bude respektovat filtry aplikované na sloupec s kategoriemi a nově také filtry aplikované na sloupec s barvami.
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 [Prodeje ALL (Sales) EXCEPT (Category, 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 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 rok 2018. Měřítko [Prodeje ALL (Sales) EXCEPT (Category, Color)] ve zvýrazněné buňce vrací sumu za prodeje modrých a černých kol, ale za všechny roky.
Chování funkce ALLEXCEPT() jako funkce pro úpravu filtrů ve funkci CALCULATE() je jednoduché, odstraní filtry z tabulky uvedené v prvním argumentu, kromě filtrů aplikovaných na sloupce uvedené ve druhém a dalších argumentech. V případě že tabulka v prvním argumentu funkce ALLEXCEPT() obsahuje ve své rozšířené verzi i další tabulky, dojde k odstranění filtrů i ze sloupců z tabulek patřících do rozšířené verzi tabulky, pokud tyto sloupce nebo tabulky neuvedeme ve druhém a dalších argumentech funkce ALLEXCEPT().
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