Jazyk DAX obsahuje celou řadu agregačních funkcí, jejichž používání je v případě jednoduchých výpočtů relativně intuitivní. V některých složitějších vzorcích, zejména pak pokud používáme agregační funkce v kontextu řádku, mohou být výsledky těchto funkcí překvapivé. V tomto příspěvku si proto na jednoduchých příkladech vysvětlíme, jak agregační funkce fungují.
V příkladech budeme pracovat s velmi jednoduchým modelem, který má pouze tři tabulky.
Soubor s řešením je pak k dispozici ke stažení níže pod tímto příspěvkem.
Jak fungují agregační funkce
Mezi běžně používané agregační funkce patří funkce jako SUM(), COUNT(), DISTINCTCOUNT(), MAX(), MIN(), AVERAGE() a další. Všechny tyto funkce mají několik společných jmenovatelů. Jako argumenty uvedených funkcí můžeme používat pouze existující sloupce v modelu, a výsledkem těchto funkcí je vždy agregovaná hodnota z použitého sloupce. Způsob agregace je pak odvozen od názvu funkce.
Mezi agregační funkce ale řadíme také funkce pokročilejší, ve kterých můžeme v prvním argumentu definovat tabulku, a ve druhém argumentu výraz určený k agregaci. Mezi tyto funkce patří všechny funkce s X na konci jejich názvu. Jedná se například o funkce SUMX(), COUNTX(), MAXX(), MINX(), AVERAGEX() atd. Kompletní výpis všech agregačních funkcí je dostupný v oficiální dokumentaci. Příklady a vysvětlení některých vybraných DAX funkcí můžete najít v samostatných článcích na stránce DAX funkce.
V tomto příspěvku se pro zjednodušení zaměříme pouze na dvě funkce, a to na funkci SUM() a SUMX(). Všechny obecné principy ale platí i pro ostatní agregační funkce. Rozdíl je pak pouze v typu agregace, podle toho kterou funkci aktuálně potřebujeme použít.
Rozdíl mezi funkcí SUM a SUMX
Ačkoliv to při pohledu na syntaxi funkcí SUM() a SUMX() nemusí být úplně zřejmé, obě funkce fungují velmi podobně. Ve skutečnosti je totiž funkce SUM() pouze syntaktickou zkratkou pro funkci SUMX(). Uvažujme například následující měřítko, které bude vracet sumu za prodeje produktů v aktuálním kontextu vyhodnocení.
Měřítko:
Pomocí měřítka [Prodeje] sčítáme všechny hodnoty ze sloupce Sales[Sales Amount] dostupné v aktuálním kontextu vyhodnocení. Jak se chovají agregační funkce v kontextu řádku a v kontextu filtru si vysvětlíme později. Nejdříve si ale ukážeme druhou verzi měřítka [Prodeje], která bude vracet za všech okolností stejné hodnoty, nyní ale s použitím funkce SUMX().
Měřítko:
Pokud obě verze výpočtů vložíme do jakéhokoliv vizuálu s jakýmikoliv atributy, vždy budou vracet stejné výsledky.
Měřítko [Prodeje] i měřítko [Prodeje (2)] vrací stejné hodnoty, protože na pozadí je funkce SUM() vyhodnocena pomocí funkce SUMX(), stejným způsobem jak je znázorněno u měřítka [Prodeje (2)]. Rozdíl mezi funkcí SUM() a funkcí SUMX() je pouze v tom, že pomocí funkce SUM() můžeme sčítat pouze hodnoty z jednoho konkrétního sloupce nahraného v modelu. Na druhou stranu, pomocí funkce SUMX() můžeme sčítat jakékoliv hodnoty nebo výrazy, které jsou vyhodnoceny v kontextu řádku tabulky zadané v prvním argumentu. Pomocí funkce SUMX() tak můžeme tvořit pokročilejší výpočty, ve kterých můžeme mimo jiné pracovat s více sloupci z tabulky. Například můžeme násobit hodnoty ze dvou sloupců, a výsledky těchto mezivýpočtů poté sčítat.
Při rozhodování, zda použít funkci SUM() nebo funkci SUMX() pak platí jednoduché pravidlo. Pokud sčítáme hodnoty z jednoho sloupce, který je nahraný v existující tabulce v modelu, použijeme funkci SUM(), protože se jedná o jednodušší zápis než při použití funkce SUMX() pro stejný účel.
Pokud ale potřebujeme sečíst hodnoty, které jsou výsledkem nějakého výpočtu, použijeme funkci SUMX(). Tím se můžeme vyhnout neustálému přidávání počítaných sloupců s jednoduchými mezivýpočty do tabulek v modelu, což zná asi každý z nás, protože to je typická praxe v začátcích práce s jazykem DAX.
Protože je chování funkcí SUM() i SUMX() při agregaci hodnot z jednoho sloupce shodné, v další části příspěvku budeme pro zjednodušení pracovat pouze s funkcí SUM(). Detailnější informace o funkcích SUM() a SUMX(), včetně dalších příkladů můžete najít v samostatném příspěvku.
Agregační funkce v kontextu řádku
Všechny agregační funkce při vyhodnocení ignorují kontext řádku. Kontext řádku vzniká automaticky při vytvoření počítaného sloupce v existující tabulce v modelu nebo programově v iteračních funkcích. Tuto vlastnost si můžeme znázornit právě na příkladu vytvoření počítaného sloupce, ve kterém budeme v tabulce 'Sales' sčítat hodnoty ze sloupce 'Sales'[Sales Amount].
Počítaný sloupec:
Nový počítaný sloupec vrací v každém řádku tabulky stejnou hodnotu, která odpovídá součtu všech hodnot ze sloupce 'Sales'[Sales Amount].
Důvod, proč vrací funkce SUM() součet všech hodnot ze sloupce 'Sales'[Sales Amount] najdeme v kontextu vyhodnocení. Kontext vyhodnocení v počítaném sloupci je dán aktuálním kontextem řádku. Na druhou stranu, na výpočet v počítaném sloupci nepůsobí žádný kontext filtru. Protože agregační funkce ignorují kontext řádku, funkce SUM() jednoduše sečte všechny hodnoty ze sloupce 'Sales'[Sales Amount] v každém řádku tabulky.
Jiná situace ale nastane pokud použijeme funkci SUM() v měřítku, které bude vyhodnoceno v reportu, ve kterém na výpočet běžně působí filtry z různých vizuálů.
Agregační funkce v kontextu filtru
Pro znázornění chování agregačních funkcí v kontextu filtru můžeme použít již dříve vytvořené měřítko [Prodeje], které má následující definici.
Měřítko:
V měřítku [Prodeje] používáme funkci SUM() pro sečtení všech hodnot ze sloupce 'Sales'[Sales Amount]. Jedná se o stejný výpočet, který jsme použili pro vytvoření počítaného sloupce 'Sales'[Součet prodejů], kde jsme mohli vidět že výsledek tohoto počítaného sloupce byl v každém řádku tabulky stejný, a vracel součet za všechny hodnoty ze sloupce 'Sales'[Sales Amount].
Pokud měřítko [Prodeje] vložíme do vizuálu v reportu, a pokud na měřítko nebudou působit žádné filtry z řádků nebo os vizuálů nebo z panelu filtrů, výsledkem bude opět součet všech hodnot ze sloupce 'Sales'[Sales Amount].
Pokud přidáme do řádků použitého vizuálu hodnoty ze sloupce s kategoriemi produktů, měřítko [Prodeje] bude vyhodnoceno v kontextu filtru každé kategorie.
Funkce SUM() použitá v měřítku [Prodeje] je nyní vyhodnocena v kontextu filtru každé kategorie, a vrací proto sumu za prodeje pouze těch produktů, které patří do kategorie zobrazené v aktuálním řádku vizuálu. V řádku souhrnů Celkem již na funkci SUM() nepůsobí žádné filtry, a výsledek měřítka [Prodeje] v řádku souhrnů odpovídá opět součtu všech hodnot ze sloupce 'Sales'[Sales Amount]. Obdobným způsobem by na funkci SUM() působily také filtry z jiných vizuálů na aktuální stránce v reportu, například z Průřezů a podobně.
Pokud přidáme do reportu například vizuál Průřez s roky s kalendářní tabulky, a vybereme jeden rok, hodnoty měřítka [Prodeje] se opět změní.
Jak můžeme vidět na obrázku výše, tak měřítko [Prodeje] nyní vrací nižší hodnoty, než tomu bylo v reportu bez Průřezu s roky a vybraného roku 2020.
Nyní vrací měřítko [Prodeje] sumu za prodeje produktů v roce 2020 a pro aktuální kategorii produktů v aktuálním řádku vizuálu Matice. V řádků souhrnů Celkem působí na výpočet pouze filtr z Průřezu, a v tomto řádku tedy vidíme prodeje za celý rok 2020.
Velmi důležité je, zejména pokud s jazykem DAX začínáme, nezaměňovat pojem aktuální řádek ve vizuálu s kontextem řádku. Každý vizuál tvoří kontext filtru, který, v případě vizuálu Tabulka nebo Matice, může pocházet z aktuálního řádku vizuálu, ale jedná se stále o kontext filtru. Je jedno, zda použijeme vizuál Matice, Tabulka, nebo jakýkoliv graf atd. Každý bod vizuálu je tvořen souborem filtrů. Na druhou stranu, kontext řádku vzniká pouze v počítaných sloupcích nebo v programově v rámci iteračních funkcí, které můžeme přirozeně používat také v definici měřítka.
Agregační funkce a změna kontextu řádku na kontext filtru
Existují situace, kdy potřebujeme vyhodnotit agregační funkci v kontextu řádku způsobem, kdy hodnoty aktuálního řádku chceme použít jako filtr. K tomuto účelu se používá funkce CALCULATE() nebo CALCULATETABLE(). Prostřednictvím těchto dvou funkcí můžeme programově změnit kontext řádku na kontext filtru.
Změně kontextu řádku na kontext filtru jsou věnované samostatné příspěvky, Změna kontextu řádku na kontext filtru a Změna kontextu řádku na kontext filtru a na co si dát pozor.
V tomto příspěvku si pouze znázorníme efekt změny kontextu řádku na kontext filtru, bez detailů popsaných ve výše uvedených příspěvcích.
Uvažujme dimenzní tabulku 'Product', do které chceme přidat sloupec obsahující celkové částky za prodeje jednotlivých produktů.
Jak už jsme si řekli dříve, v počítaném sloupci je aktivní kontext řádku, který agregační funkce ingorují. Následující počítaný sloupec tak bude opět vracet celkovou částku prodejů za všechny produkty.
Počítaný sloupec:
Výsledkem nového počítaného sloupce je stejná hodnota v každém řádku tabulky 'Product'.
Pokud ale funkci SUM() vložíme do funkce CALCULATE(), vyvoláme změnu kontextu řádku na kontext filtru.
Počítaný sloupec:
Takto vytvořený počítaný sloupec již bude vracet sumu za prodeje aktuálního produktu v aktuálním řádku tabulky, protože funkce CALCULATE() vyvolá změnu kontextu řádku na kontext filtru, a díky tomu dojde k použití všech hodnot z každého sloupce v aktuálním řádku tabulky k zafiltrování modelu při vyhodnocení funkce SUM().
Stejného efektu pak dosáhneme použitím měřítka [Prodeje] v rámci počítaného sloupce, protože každé měřítko je na pozadí skrytě vyhodnoceno ve funkci CALCULATE(). Následující počítaný sloupec proto bude vracet také částku prodejů každého produktu v aktuálním řádku tabulky 'Product'.
Počítaný sloupec:
Výsledky nového počítaného sloupce můžeme vidět na následujícím obrázku.
Více informací o změně kontextu řádku na kontext filtru je k dispozici v samostatném příspěvku dostupném pod tímto odkazem.
Shrnutí
Při používání agregačních funkcí musíme vždy přemýšlet o tom, v jakém kontextu bude vybraná funkce vyhodnocena. Všechny agregační funkce totiž ignorují kontext řádku, ale respektují kontext filtru. V pokročilejších výpočtech je pak zcela běžné, že v určité části výpočtu působí na konkrétní funkci jeden nebo více filtrů a jeden nebo více kontextů řádků současně. V těchto případech již vyžaduje pochopení chování jednotlivých funkcí určitou praxi. Relativně často také nastává situace, kdy potřebujeme v určité části výpočtu změnit kontext řádku na kontext filtru. K tomuto účelu pak můžeme použít funkci CALCULATE().
Komentáře
Okomentovat