Funkce ALLSELECTED() se ve funkci CALCULATE() používá k načtení vnějších filtrů působících na vizuál a současně k odstranění filtrů z řádků vizuálu, v rámci kterého je měřítko vyhodnoceno. Typickým příkladem použití funkce ALLSELECTED() ve funkci CALCULATE() je výpočet podílu aktuální hodnoty vůči sumě hodnot za všechny položky vybrané v průřezu.
(Aktualizace 3. 6. 2023)
Funkce ALLSELECTED() může být použita jako modifikátor ve funkci CALCULATE(), ale také jako funkce vracející tabulku. Tento článek obsahuje popis funkce ALLSELECTED() při použití jako funkce pro úpravu filtrů ve funkci CALCULATE(). Článek je rozdělen na dvě části. První část obsahuje základní popis funkce s jednoduchým příkladem. Druhá část článku obsahuje podrobnější popis funkce a příklad situace, kdy použití funkce ALLSELECTED() není vhodné.
Funkce ALLSELECTED ve funkci CALCULATE
Funkci ALLSELECTED() můžeme použít s argumentem ve formě sloupce nebo sloupců, s argumentem ve formě tabulky a nebo bez argumentu. Pokud funkci ALLSELECTED() použijeme přímo v měřítku, které je vyhodnoceno v konkrétním vizuálu, funkce ALLSELECTED() odstraní filtry z tohoto vizuálu a načte vnější filtry, které působí na vizuál, například z průřezů. Fungování funkce si můžeme názorněji vysvětlit na následujícím příkladu.
Příklad použití funkce ALLSELECTED
Typickým příkladem použití funkce ALLSELECTED() je výpočet procentuálního podílu, kdy chceme aby byl dělitel vyhodnocen v kontextu filtrů, které uživatel nastaví v reportu. Pro ukázku můžeme použít soubor Adventure Works DW 2020.pbix, který je volně dostupný ke stažení na internetu. Soubor s řešením je dostupný ke stažení níže pod tímto příspěvkem.
Začněme jednoduchým příkladem. Na obrázku níže je vidět report s kategoriemi produktů v průřezu a s tabulkou obsahující sumu prodejů rozpadlou také přes jednotlivé kategorie produktů.
Měřítko [Prodeje] použité ve vizuálu na obrázku výše má následující jednoduchou definici.
Měřítko:
Pokud chceme vypočítat podíl prodejů v jednotlivých kategoriích na celkových prodejích ve všech kategoriích, které vybral uživatel v průřezu, musíme při výpočtu dělitele upravit kontext filtru tak, aby hodnota dělitele odpovídala sumě prodejů za všechny vybrané kategorie v průřezu. Toho docílíme použitím funkcí CALCULATE() a ALLSELECTED().
Měřítko:
Dvě výše uvedená měřítka můžeme vložit do připravené tabulky v Power BI reportu. Měřítko [Prodeje] je vyhodnoceno v kontextu filtru každého řádku v tabulce a vrací proto prodeje za produkty v dané kategorii. Měřítko [Prodeje (všechny vybrané kategorie)] tento filtr generovaný pro každý řádek tabulky ignoruje, a je vyhodnoceno v kontextu vnějšího filtru, tedy filtru který působí na vizuál z průřezu.
Hodnota měřítka [Prodeje (všechny vybrané kategorie)] odpovídá v každém řádku tabulky sumě prodejů za kategorie vybrané v průřezu (Bikes, Clothing, Components), tedy ve vnějším filtru, ve kterém je dotaz na pozadí vizuálu vyhodnocen.
Pokud v průřezu nevybereme žádné kategorie, bude výsledkem měřítka [Prodeje (všechny vybrané kategorie)] hodnota prodejů za všechny kategorie.
Nyní tedy máme k dispozici hodnoty pro dělence (měřítko [Prodeje]) i dělitele (měřítko [Prodeje (všechny vybrané kategorie)]). Na základě těchto dvou měřítek můžeme vypočítat procentuální podíl prodejů v jednotlivých kategoriích na celku. Pro výpočet podílu použijeme funkci DIVIDE(), která ošetří případné dělení nulou nahrazením chybové hlášky hodnotou BLANK. Prvním argumentem funkce DIVIDE() je dělenec, který vrací v každém řádku tabulky aktuální prodeje pro danou kategorii. Druhým argumentem funkce DIVIDE() je dělitel, který představuje sumu prodejů za kategorie vybrané v průřezu.
Měřítko:
Nové měřítko opět vložíme do tabulky ve stejném reportu. Výsledek je možné vidět na obrázku níže.
Pokud bychom namísto funkce ALLSELECTED() použili funkci REMOVEFILTERS() nebo funkci ALL() s argumentem ve formě sloupce s kategoriemi produktů, dělitel by obsahoval sumu prodejů za všechny kategorie, bez ohledu na výběr filtrů v průřezu. Rozdíl mezi funkcí ALLSELECTED() a funkcí REMOVEFILTERS() při výpočtu procentuálního podílu je popsán v samostatném příspěvku.
V předchozím jednoduchém příkladu bylo možné vidět použití funkce ALLSELECTED() s argumentem ve formě sloupce. Pokud bychom použili tuto funkci bez argumentu, bude výše uvedený princip platit ne na jeden konkrétní sloupec, ale pro všechny sloupce ve všech tabulkách v celém modelu.
Pokud bychom jako argument funkce ALLSELECTED() uvedli název konkrétní tabulky, dojde opět k aplikování stejného principu, ovšem na všechny sloupce dané tabulky a také na sloupce z její rozšířené verze.
Další variantou pak může být použití funkce ALLSELECTED() s více sloupci, za podmínky že všechny sloupce pocházejí ze stejné tabulky.
Výše uvedený popis funkce ALLSELECTED() na příkladu výpočtu procentuálního podílu je relativně jednoduchý. Funkce ALLSELECTED() je ale ve skutečnosti složitá a komplexní funkce, která může při nevhodném použití vracet neočekávané výsledky.
Pokud se chceme při použití funkce ALLSELECTED() vyvarovat problémům, neměli bychom nikdy vkládat měřítka která obsahují funkci ALLSELECTED() jako argumenty do jiných měřítek. Současně se nedoporučuje používat funkci ALLSELECTED() ve výpočtech, které obsahují iterační funkce. Důvody pro tato dvě pravidla jsou popsána v následující části.
Funkce ALLSELECTED podrobněji
Funkce ALLSELECTED() je ve skutečnosti velmi složitá a komplexní funkce. V této části příspěvku je chování funkce ALLSELECTED() popsáno do velkého detailu, který nemusí být srozumitelný pro občasného autora DAX kódu. I přesto bych každému doporučil přečíst si následující text. Z informací uvedených níže totiž vyplývají dvě jednoduchá pravidla, která je dobré dodržovat, abychom se vyhnuli případným problémům při použití funkce ALLSELECTED().
Pro zjednodušení uvažujme o použití funkce ALLSELECTED() s argumentem ve formě jednoho sloupce. Chování funkce ALLSELECTED() při použití jako modifikátoru funkce CALCULATE() je možné popsat následovně.
Funkce ALLSELECTED() vrací všechny hodnoty, které jsou dostupné v tabulce v poslední iterační funkci, ve které je funkce ALLSELECTED() vyhodnocena.
Tato definice nemusí být na první přečtení příliš srozumitelná, nicméně postupně bude krok po kroku vysvětlena na jednoduchých ukázkách a bude také několikrát zopakována, aby bylo zřejmé, jak funkce ALLSELECTED() funguje.
Jak funguje funkce ALLSELECTED
Pro vysvětlení chování funkce ALLSELECTED() můžeme použít předchozí příklad, ve kterém jsme počítali procentuální podíl prodejů v jednotlivých kategoriích vůči celkovým prodejům za všechny kategorie vybrané v průřezu. Výpočet dělitele obsahoval funkci ALLSELECTED() a vypadal následovně.
Měřítko:
Pokud měřítko [Prodeje (všechny vybrané kategorie)] vložíme znovu do vizuálu Tabulka, spolu s kategoriemi produktů v řádcích a dále s měřítkem [Prodeje], dostaneme následující obrázek.
Funkce ALLSELECTED() vrací všechny hodnoty, které jsou dostupné v tabulce v poslední iterační funkci, ve které je funkce ALLSELECTED() vyhodnocena.
Pokud se zamyslíme nad definicí funkce ALLSELECTED(), vodítkem pro přesné pochopení toho jak tato funkce funguje bude vždy nalezení poslední iterační funkce, ve které je funkce ALLSELECTED() vyhodnocena. Měřítko [Prodeje (všechny vybrané kategorie)] ale žádnou iterační funkci neobsahuje.
Tím se dostáváme k vysvětlení prvního pravidla pro bezpečné použití funkce ALLSELECTED(), kterým je snažit se vyhnout použití této funkce uvnitř jakékoliv iterační funkce. Důvodem pro toto pravidlo je, že téměř každý Power BI vizuál už jednu iterační funkci na pozadí obsahuje. Každý vizuál generuje před zobrazením výsledků našich výpočtů DAX dotaz, který, až na některé výjimky, obsahuje téměř vždy také iterační funkci SUMMARIZECOLUMNS().
DAX dotaz vygenerovaný na pozadí vizuálu Tabulka, který je zobrazený na obrázku výše, vypadá následovně.
Pro naši analýzu funkce ALLSELECTED() je však zjednodušená varianta DAX dotazu dostačující. Nyní se můžeme vrátit zpět k definici funkce ALLSELECTED() a následně si popsat, jak je tato funkce ve skutečnosti vyhodnocena.
Funkce ALLSELECTED() vrací všechny hodnoty, které jsou dostupné v tabulce v poslední iterační funkci, ve které je funkce ALLSELECTED() vyhodnocena.
Poslední iterační funkce, která ve které je funkce ALLSELECTED() vyhodnocena v měřítku [Prodeje (všechny vybrané kategorie)], je na obrázku výše funkce SUMMARIZECOLUMSN(). Ta obsahuje všechny kategorie, které jsou vybrané v reportu v průřezu a jsou použity jako filtr ve funkci SUMMARIZECOLUMNS() v pátém řádků na obrázku výše. Právě tyto vybrané kategorie načte funkce ALLSELECTED() vyhodnocená v měřítku [Prodeje (všechny vybrané kategorie)] v devátém řádku DAX kódu na obrázku výše.
Měřítko [Prodeje] vrací prodeje za jednu konkrétní kategorii v aktuálním řádku. Měřítko [Prodeje (všechny vybrané kategorie)] ale ignoruje filtr z řádků, a je vyhodnoceno v kontextu všech kategorií, protože funkce ALLSELECTED() načte všechny kategorie dostupné v poslední iterační funkci, tedy ve funkci SUMMARIZECOLUMNS().
Všechny tyto drobné detaily můžeme ignorovat pokud se vyhneme použití funkce ALLSELECTED() uvnitř některé z iteračních funkcí. Proč bychom neměli používat funkce ALLSELECTED() uvnitř iteračních funkcí si můžeme opět ukázat na příkladu.
Představme si například situaci, kdy bychom chtěli vidět ve vizuálu prodeje za všechny kategorie vybrané v průřezu, ale pouze pro produkty v jedné barvě, řekněme například v modré. Bez detailní znalosti toho jak funguje funkce ALLSELECTED() bychom mohli zkusit následující výpočet.
Měřítko:
Ve výše uvedeném měřítku je v prvním argumentu funkce SUMX() tabulka 'Product', zafiltrovaná pouze na modré produkty. Následně je pro každý modrý produkt vyhodnocen druhý argument funkce SUMX(), kterým je funkce CALCULATE(), ve které chceme sečíst prodeje za všechny kategorie právě pouze pro modré produkty. Funkce ALLSELECTED() ale v tomto případě nebude fungovat podle očekávání. Pokud měřítko vložíme do našeho vizuálu s kategoriemi produktů v řádcích, nové měřítko bude vracet sumu za prodeje modrých produktů, ale ne za všechny kategorie, ale pouze za aktuální kategorii v aktuálním řádku vizuálu.
Měřítko:
To si můžeme ověřit na následujícím obrázku.
Funkce ALLSELECTED() vrací všechny hodnoty, které jsou dostupné v tabulce v poslední iterační funkci, ve které je funkce ALLSELECTED() vyhodnocena.
Pro nalezení poslední iterační funkce, ve které je funkce ALLSELECTED() vyhodnocena, si můžeme zopakovat definici měřítka [Prodeje (všechny vybrané kategorie, modré - špatně)].
Měřítko:
Funkce ALLSELECTED() je vyhodnocena uvnitř iterační funkce SUMX(). Tabulka 'Product' v prvním argumentu této funkce obsahuje také sloupec s kategoriemi produktů. Při vyhodnocení měřítka v řádcích vizuálu dojde nejdříve k zafiltrování tabulky 'Product' uvnitř funkce FILTER() pouze na produkty z aktuální kategorie v aktuálním řádku vizuálu. Následně jsou tyto produkty z dané kategorie zafiltrované pouze na modré produkty. Funkce ALLSELECTED() obsahuje argument ve formě odkazu na sloupec 'Product'[Category]. V tabulce v prvním argumentu funkce SUMX() je ale k dispozici v každém řádku vizuálu pouze jedna kategorie, a to stejná kategorie která je v aktuálním řádku vizuálu, a právě tuto jednu kategorii načte funkce ALLSELECTED().
Správná definice měřítka, které by vracelo prodeje za všechny kategorie vybrané v průřezu a současně pouze za produkty v modré barvě, bude vypadat následovně.
Měřítko:
Nové verze výpočtu již neobsahuje iterační funkci a funkce ALLSELECTED() načte všechny kategorie vybrané v průřezu.
Výsledkem měřítka [Prodeje (všechny vybrané kategorie a modrá barva)] jsou tedy prodeje za všechny kategorie vybrané v průřezu, ale pouze za produkty s modrou barvou. Takovéto měřítko sice nemá žádný praktický význam, cílem tohoto příkladu bylo pouze co nejjednodušším způsobem vysvětlit rozdíl mezi použitím funkce ALLSELECTED() uvnitř iterační funkce (měřítko [Prodeje (všechny vybrané kategorie, modré - špatně)]), kde je odhalení případného problému velmi složitý úkol, a měřítkem kde je funkce ALLSELECTED() použita bezpečným způsobem, bez použití iteračních funkcí.
Při psaní DAX kódu obvykle nechceme takto podrobně rozebírat každý detail, který může ovlivnit výsledek výpočtů. V případě funkce ALLSELECTED() se můžeme vyhnout případným problémům relativně snadno. Stačí se držet dvou jednoduchých pravidel.
1. Funkci ALLSELECTED() bychom neměli používat v iteračních funkcích.
2. Měřítka obsahující funkci ALLSELECTED() bychom neměli používat uvnitř jiných měřítek, protože při použití odkazu na měřítko můžeme snadno zapomenout na jeho definici a můžeme tak vložit měřítko s funkcí ALLSELECTED() do některé z iteračních funkcí.
Pokud budeme dodržovat tato dvě pravidla, vystačíme si s jednodušší definicí funkce ALLSELECTED(). Funkce ALLSELECTED() odstraní filtry z vizuálu, ve kterém je měřítko s touto funkcí použito, a načte vnější filtry, které působí na tento vizuál.
Shrnutí
Funkce ALLSELECTED() je složitá funkce, která může být použita jako funkce vracející tabulku, nebo jako funkce ovlivňující výsledek výrazu v CALCULATE(). Tento článek obsahuje popis funkce ALLSELECTED() při použití jako modifikátoru CALCULATE(). V první části článku je uveden příklad typického použití funkce ALLSELECTED() pro výpočet procentuálního podílu na celku.
Definice funkce ALLSELECTED() v druhé části článku může vypadat složitě. Cílem druhé části nebylo odradit čtenáře od používání funkce ALLSELECTED(), ale upozornit na možné neočekávané výsledky, které mohou nastat, pokud funkci ALLSELECTED() použijeme při vnoření více iteračních funkcí do sebe. Ačkoliv se tato situace může zdát na první pohled málo pravděpodobná, opak je pravdou. Na pozadí téměř každého vizuálu je spouštěn dotaz, který již většinou jednu iterační funkci obsahuje. Z toho vyplývají výše uvedená dvě pravidla, která bychom měli dodržovat, abychom se vyhnuly neočekávaným výsledkům při používání funkce ALLSELECTED().
Komentáře
Okomentovat