Funkce ALL, Allselected a Allexcept
Pokud jste někdy psali trochu složitější výpočty, asi jste se s funkcemi All, AllSelected a AllExcept setkali.
Já s nimi vždy trochu bojoval a ze začátku jsem DAX psal stylem pokus omyl, dokud jsem se nedobral požadovaného výsledku. Pojďme se tedy podívat na rozdíly mezi All, AllSelected a AllExcept, abyste neopakovali moje chyby a vždy přesně věděli, co váš kód dělá.
Na úvod ještě malý disclaimer. Funkce All, AllSelected a AllExcept slouží jako modifikátor funkce CALCULATE, jinými slovy filtrují data nebo mažou filtry, se kterými funkce pracuje. Alternativně tyto funkce vrací tabulku či sloupec s vybranými řádky, například při použití jako parametr funkce SUMX. V tomto článku si představíme základní rozdíly v těchto funkcích při použití jako modifikátor funkce CALCULATE. Nicméně určitě se k nim v budoucnu vrátíme a ukážeme si i zajímavé vzory v DAX.
Popis modelu
Vysvětlení funkcí All, AllSelected a AllExcept si předvedeme na jednoduchém modelu. Máme tabulku s prodeji (Sales), která je navázaná na údaje o produktech (Pricelist) a o prodejcích (Employees).
Jednoduchý report obsahuje tabulku s názvy produktů z tabulky Pricelist a sumu zisku pro jednotlivé produkty z tabulky Sales.
All
Funkce All odstraní aplikované filtry buď z vybraného sloupce (či více sloupců) nebo z celé tabulky. Alternativně je možné použít funkci REMOVEFILTERS, která se chová úplně stejně.
Odstranění filtru jednoho sloupce
Odstranění filtru jednoho sloupce se provede následujícím příkazem.
Sum (All Product) = CALCULATE ([Sum of profit], ALL(Pricelist[Product name]))
Výsledkem je pak v každém řádku tabulky hodnota sumy zisku pro všechny produkty. Tento vzorec se vyplatí, pokud chcete třeba vypočítat kolika procenty se daný produkt podílí na celkovém zisku. Tím, že jste vybrali jen sloupec „Product name“, výsledek se například přepočítá při filtrování prodejců.
Odstraní se filtr aplikovaný v rámci vizualizace – tedy v každém řádku představujícím jiný produkt je stejné číslo. A zároveň filtry aplikované mimo vizualizaci – v tomto případě tedy bez ohledu na zvolené hodnoty ve filtru (sliceru) „Product name“.
Pozor, pokud byste si řekli, že místo sloupce „Product name“ z tabulky „Pricelist“ použijete „Product ID“ z tabulky „Sales“ nebo „Pricelist“, tak se výsledku nedoberete. Při vyhodnocování funkce se berou v potaz filtry aplikované pouze na specifikovaný sloupec.
Odstranění filtru více sloupců
Užitím výpočtu Sum (All Product) můžeme vybrat jednoho prodejce a zjistit, kolik procent z jeho prodejů představují jednotlivé produkty (výpočet “Profit %”).
Pokud nyní nastavíme filtr na určitou kategorii produktů, výpočet odstraní filtr ze sloupce „Product name“, ale zachová filtr na kategorii. Bude tedy počítat se všemi produkty ale pouze dané kategorie. Výsledkem tedy budou přepočítané hodnoty pro danou kategorii se sumou všech položek rovnou 100 %.
Co když je zadání takové, abych jen filtroval hodnoty a viděl poměr zisku ze všech kategorií? Pak musím změnit výpočet „Sum (All Product)“, aby odstranil filtr i ze sloupce „Category“.
Odstranění filtru celé tabulky
A pokud chci vždy počítat poměr zisku daného produktu vůči všem prodaným produktům bez ohledu na prodejce, kategorii a případné další filtry? Pak musím použít funkci All s parametrem tabulky „Sales“. Případně stejného výsledku lze docílit s použitím funkce All bez jakéhokoliv parametru.
Sum (Table Sales) = CALCULATE( [Sum of profit], ALL())
AllSelected
Funkce AllSelected funguje na stejném principu jako All. Hlavní rozdíl spočívá v tom, že AllSelect zachová filtry mimo dotaz – například bere v potaz slicery na stránce.
AllSelect ignoruje filtry jen v dotazu. Například ignoruje, že v tabulce filtrujeme každý řádek podle sloupce „Product name“. Slicer na sloupci „Product name“ umístěný na stránce bude tedy zohledněn.
Co to tedy znamená, když funkci AllSelect použijeme na matici? V matici jsou hodnoty pro každý produkt „Product name“ dále rozpadnuty na pohlaví prodejce „Gender“ a pak na konkrétního prodejce „Employee name“. Podíváme se na variace AllSelect s následujícími parametry:
- Sloupec „Employee name“
- Tabulka „Employee“
- Tabulka „Sales“
- Bez udání parametru
- Sloupec „Product name“
Sloupec „Employee name“
Funkce AllSelect(Employee[Employee name]) při vyhodnocování odstraní (ignoruje) filtr aplikovaný pro sloupec „Employee name“. Pro každého zaměstnance je tedy pod daným produktem a případně dalšími kategoriemi stejná částka.
Pro řádky s pohlavím a samotným produktem jsou tyto filtry zachovány. V matici jsou pro tyto řádky zobrazena čísla odpovídající výpočtu „Profit“.
Tabulka „Employees“
Při použití názvu tabulky jako parametru funkce AllSelect docílíme smazání filtru pro všechny sloupce dané tabulky. V matici se koukáme na rozpad zisku podle sloupců „Gender“ a „Employee name“ z tabulky „Employees“. Výsledkem výpočtu AllSelect(Employee) je tedy stejná hodnota ve všech řádcích příslušících danému produktu.
Tabulka „Sales“ a neuvedení parametru
Co když je cílem mít v každém řádku matice stejnou hodnotu reprezentující sumu všech vybraných produktů (např. filtry na stránce nad libovolným sloupcem)? Řešením je použití funkce AllSelect s parametrem celé tabulky sales nebo neuvedení parametru vůbec.
Sum (AllSelected No param)) = CALCULATE([Sum of profit],ALLSELECTED())
* Nepovedlo se mi odhalit, jaký je mezi použitím funkce s parametrem tabulky Sales a bez parametru rozdíl. Pokud někdo víte, určitě se podělte v komentářích.
Sloupec „Product name“
Protože používáme funkci AllSelected, je zachován zvolený filtr na stránce pro sloupec „Product name“ (viz vyšší obrázky). Co v tomto případě znamená ignorování filtrů v dotazu?
- Řádky obsahující hodnoty pro sloupec „Product name“ – výpočet zobrazí pro každý produkt sumu zisku za všechny produkty. Odstraní v rámci dané vizualizace filtr na sloupci „Product name“.
- Řádky obsahující hodnoty pro sloupec „Gender“ – opět je odstraněn filtr na sloupci „Product name“. Nicméně je zachován filtr v rámci vizualizace na sloupci “Gender”. Pro všechny výskyty hodnoty „F“ je zobrazena suma za všechny produkty (1 100 457). Analogicky je u všech výskytů „M“ je opět všude stejné číslo, tentokrát jiná suma za všechny produkty (1 183 996).
- Řádky obsahující hodnoty pro sloupec „Employee“ – všechny výskyty konkrétního zaměstnance mají uvedeno stejné číslo – suma zisku za všechny produkty (se zachováním sliceru pro „Product name“).
AllExcept
Funkce AllExcept má otočenou logiku od funkce AllSelected. Při užití AllExcept dojde ke smazání všech ostatních filtrů, kromě filtru nad specifikovaným sloupcem (případně více sloupci, pokud jsou uvedeny jako další parametry).
Malá rekapitulace rozdílů jednotlivých funkcí se sloupcem „Product name“ jako parametrem:
- All – odstraní všechny filtry, takže v každém řádku je suma za všechny produkt.
- AllSelected – odstraní filtry aplikované v dotazu, ale zachová vnější filtry. Opět ale v každém řádku stejná hodnota.
- AllExcept – zachová filtry nad sloupcem v dotazu. Proto v každém řádku hodnota odpovídající sloupci „Profit“.
Filtr nad jiným sloupcem
AllExcept ale odstraní filtry ze všech ostatních sloupců tabulky uvedené jako první parametr. Pokud tedy nastavím filtr i na sloupec „Product ID“ nebudete tento filtr ve výpočtu zohledněn.
Pro jednotlivé řádky vrací funkce hodnotu odpovídající daným produktům. Od prosté sumy zisku jednotlivých produktů (sloupec „Profit“) se liší agregovanou hodnotou v řádku „Celkem“. Zajímavé využití funkce AllExcelpt v praxi naleznete zde.
Pro poslední ukázku funkce AllExcept použijeme parametr „Employee“. Filtry nad jednotlivými zaměstnanci jsou tedy zachovány a data přepočítána. Pokud ale vyzkoušíme filtr nad jiným sloupcem tabulky „Employee“, je tento filtr ignorován. V příkladu níže při filtru nad sloupcem „Gender“.
Shrnutí
Při užití jednotlivých funkcí All, AllSelected a AllExcept jako modifikátoru funkce Calculate nad jedním sloupcem jsou výsledky následující.
Funkce | Filtr v dotazu (např. řádky tabulky) | Filtr mimo dotaz (např. slicery na stránce) | Filtr nad ostatními sloupci tabulky |
|---|---|---|---|
| ALL | Odstraněn | Odstraněn | Data přepočítána |
| ALLSELECTED | Odstraněn | Data přepočítána | Data přepočítána |
| ALLEXCEPT | Data přepočítána | Data přepočítána | Odstraněn |
