Funkce All, AllSelected a AllExcept

-> NÁVODY -> DAX

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).

All, AllSelected, AllExcept datový model

Jednoduchý report obsahuje tabulku s názvy produktů z tabulky Pricelist a sumu zisku pro jednotlivé produkty z tabulky Sales.

All, AllSelected, AllExcept tabulka profit

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ů.

All parametr jeden sloupec ukázka filtrování

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“.

All parametr jeden sloupec ukázka filtru na stejném sloupci

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.

All parametr jeden sloupec ukázka na špatném sloupci
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 %”).

All ukázka výpočtu 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 %.

All výpočet profit, filtr na sloupci kategorie

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“.

All výpočet profit, parametr více sloupců
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())

All výpočet Profit from all Sales

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.

AllSelected parametr sloupec ukázka filtrová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“
AllSelected různé parametry ukázka na matici
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 AllExceptotoč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).

AllExcept porovnání s All a AllSelected

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.

AllExcept filtr nad jiným sloupcem

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“.

AllExcept filtr nad tabulkou zaměstnanci

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
ALLOdstraněnOdstraněnData přepočítána
ALLSELECTEDOdstraněnData přepočítánaData přepočítána
ALLEXCEPTData přepočítánaData přepočítánaOdstraněn

Related Posts

Leave a comment

Privacy Preferences
When you visit our website, it may store information through your browser from specific services, usually in form of cookies. Here you can change your privacy preferences. Please note that blocking some types of cookies may impact your experience on our website and the services we offer.