Měřítka a počítané sloupce v DAX

Úvodní obrázek

Na pozadí Power BI reportů je Tabulární model, do kterého můžeme načítat data z mnoha různých zdrojů. Tabulární model můžeme dále programově rozšířit o Počítané tabulky, Počítané sloupce, Měřítka a další objekty. V tomto článku najdete na jednom místě hlavní rozdíly mezi Počítanými sloupci (Calculated Columns) a Měřítky (Measures). 

Rozdíl mezi měřítkem a počítaným sloupcem

Pokud s jazykem DAX začínáte, může zdát rozdíl mezi Počítaným sloupcem a Měřítkem malý. V některých případech můžeme dokonce dosáhnout stejného výsledku oběma způsoby. Rozdíly mezi Počítaným sloupcem a Měřítkem jsou ale velké. 

Smysl počítaných sloupců zná každý uživatel Excelu nebo vývojář se zkušenostmi s SQL jazykem velmi dobře. Hlavní výhodou Tabulárního modelu jsou však měřítka. Měřítko je revoluční koncept, díky kterému můžeme vytvořit univerzální výpočet, který může být použitý v různých kontextech opakovaně bez nutnosti měnit vzorec v definici měřítka.

Než se dostaneme k hlavním rozdílům mezi dvěma typy výpočtů, můžeme si na jednoduchých příkladech ukázat, jak vůbec vytvořit Měřítko, jak vytvořit Počítaný sloupec a jaký je výsledek těchto výpočtů. V ukázkách budeme pracovat s Power BI souborem Adventure Works DW 2020.pbix, který je volně dostupný ke stažení na internetu. Tento soubor je také dostupný ke stažení níže pod tímto příspěvkem.

Jak vytvořit v Power BI nový Počítaný sloupec

Počítaný sloupec můžeme vytvořit pouze v již existující tabulce. Nový Počítaný sloupec se po vytvoření stane součástí této tabulky. Může proto být použitý v reportech, ve výpočtech nebo pro nastavení relací mezi ostatními tabulkami v modelu.

Jako příklad Počítaného sloupce si můžeme ve cvičném souboru Adventure Works DW 2020.pbix v tabulce 'Product' vytvořit nový Počítaný sloupec s názvem 'Product'[Zisk]. V tabulce 'Product' je pro každý prodávaný výrobek uvedena cena výrobku a náklady výrobku. Nový počítaný sloupec bude v každém řádku tabulky počítat rozdíl mezi těmito dvěma hodnotami.

Měřítka a počítané sloupce v DAX 2

Jednou z možností, jak v Power BI vytvořit nový Počítaný sloupec, je kliknutí pravým tlačítkem myši na tabulku, ve které chceme nový sloupec vytvořit, a v kontextové nabídce vybrat možnost "New column" ("Nový sloupec").  

Měřítka a počítané sloupce v DAX 3

Tím se aktivuje DAX editor nad tabulkou, do kterého můžeme zadat požadovaný výpočet. V tomto příkladu bude DAX vzorec pro výpočet zisku vypadat následovně.

Počítaný sloupec:

Zisk = 'Product'[List Price] - 'Product'[Standard Cost]

Text před znaménkem rovná je název nového počítaného sloupce. Za znaménkem rovná se následuje samotný výpočet, na základě kterého budou dopočítány hodnoty nového počítaného sloupce v každém řádku tabulky. Vzorec určený pro výpočet nového počítaného sloupce je vyhodnocen řádek po řádku v kontextu každého řádku tabulky

Měřítka a počítané sloupce v DAX 4

Nový počítaný sloupec je nyní součástí tabulky. Takto vytvořený sloupec proto zabírá místo v paměti a zvětšuje velikost modelu. Výpočet hodnot v novém sloupci probíhá vždy při vytvoření nového Počítaného sloupce a pak při každé aktualizaci dat v modelu. Název nového sloupce musí být jedinečný v rámci tabulky, ve které je vytvořen.

Jak vytvořit v Power BI nové Měřítko

Stejně jako Počítaný sloupec, také Měřítko musí být součástí některé z existujících tabulek. Na rozdíl od Počítaného sloupce ale můžeme Měřítka mezi tabulkami libovolně přesouvat, protože Měřítka nejsou závislá na konkrétní tabulce – nejsou vyhodnocena v kontextu řádků určité tabulky. Měřítko je vyhodnoceno až na úrovni reportu v době generování dotazu a je vyhodnoceno v kontextu filtru. Měřítko nezabírá fyzické místo v paměti, ale spotřebovává výpočetní výkon při vykreslování reportu. Nové Měřítko můžeme vytvořit podobně jako Počítaný sloupec. V prvním kroku klikneme pravým tlačítkem na tabulku, ve které chceme nové Měřítko vytvořit. Z kontextové nabídky následně vybereme možnost "New measure" (Nové měřítko).

Měřítka a počítané sloupce v DAX 5

Následně se opět v Power BI aktivuje DAX editor, do kterého můžeme vložit výpočet. Pokud se pokusíme vložit do řádku vzorců stejný výpočet, který jsme použili pro výpočet nového Počítaného sloupce, výsledek bude vypadat následovně.

Měřítka a počítané sloupce v DAX 7

Měřítko nejde vytvořit, protože při vyhodnocení není k dispozici žádný kontext řádku, ve kterém by mohly být odkazy na sloupce vyhodnoceny. Vyřešit tento problém můžeme buď použitím některé z iteračních funkcí, které vytvoří kontext řádku programově, nebo použitím některých sumarizačních funkcí, které agregují hodnoty ze sloupců v kontextu aktivních filtrů. Níže uvedená definice Měřítka je již přípustná.

Měřítko:

(Měřítko) Zisk =
SUMX
(
'Product',
'Product'[List Price] - 'Product'[Standard Cost]
)

Takto vytvořené Měřítko již je platné a můžeme ho použít v reportu. Nově vytvořené měřítko [(Měřítko) Zisk] je sice součástí tabulky 'Product', ale k jeho vyhodnocení dojde až při jeho použití v reportu. Současně můžeme takto vytvořené Měřítko přesunout do kterékoliv tabulky v modelu, a na výsledku výpočtu se nic nezmění. 

Měřítka a počítané sloupce v DAX 8

Měřítko [(Měřítko) Zisk] je v reportu na obrázku výše vyhodnoceno v každém řádku tabulky samostatně, v kontextu filtru dané kategorie. To je zásadní rozdíl oproti Počítanému sloupci. Tabulka ve vizuálech netvoří kontext řádku, jak je tomu u tabulky v modelu a v případě vyhodnocení Počítaných sloupců. Řádek v tabulce ve vizuálu, stejně jako hodnota zobrazená v kterémkoliv jiném vizuálu, tvoří kontext filtru, ve kterém je měřítko vyhodnoceno.

Měřítko je vyhodnoceno v každé buňce tabulky samostatně. Pro představu můžeme například uvažovat druhý řádek vizuálu tabulky na obrázku výše. Při vyhodnocení nového Měřítka ve druhém řádku tabulky působí na výpočet filtr, který filtruje celý model pouze na hodnoty odpovídající kategorii produktů Bikes. Při vyhodnocení Měřítka je nejdříve tabulka 'Sales'  uvedená v prvním argumentu funkce SUMX() filtrována pouze na produkty v kategorii Bikes. Následně dojde k vyhodnocení výpočtu uvedeného v druhém argumentu funkce. Ten je vyhodnocen řádek po řádku pro v tabulce 'Product', filtrované pouze na produkty patřící do kategorie Bikes. Po vyhodnocení výpočtu v každém řádku tabulky 'Product' dojde k sečtení všech hodnot z každého řádku této tabulky, uvedené v prvním argumentu funkce SUMX(). Tento součet je pak výsledkem měřítka [(Měřítko) Zisk] ve druhém řádku tabulky na obrázku výše.

Každé měřítko musí vracet skalární hodnotu a je vyhodnoceno v kontextu filtru, ve kterém je měřítko použito. Měřítko je vyhodnoceno v době generování reportu, a spotřebovává proto výpočetní výkon. Výsledek měřítek tedy není součástí modelu a můžeme tak při jeho použití namísto počítaných sloupců šetřit paměť a nezvětšovat tak velikost modelu, jako je tomu u počítaných sloupců.

Hlavní rozdíly mezi Počítanými sloupci a Měřítky

Měřítko je vyhodnoceno v kontextu filtru, který obvykle působí v reportu na výpočet z řádků nebo sloupců v matrixu nebo v tabulce, z průřezů a grafů nebo z kterýchkoliv jiných vizuálů. Počítaný sloupec je na druhou stranu vyhodnocen v kontextu řádku tabulky, ve které je vytvořen a je součástí tabulky.

Počítaný sloupec je vyhodnocen při jeho vytvoření a následně během každé aktualizace dat v datovém modelu. Měřítko je vyhodnoceno v době načítání reportu, ve kterém je použito.

Hodnoty, které jsou výsledkem Počítaného sloupce jsou součástí tabulky v modelu a zabírají místo v paměti – tzn. zvětšují model. Měřítka nezabírají místo v paměti, ale spotřebovávají výpočetní výkon při načítání reportu.

Měřítko může být použito pro vytvoření nového počítaného sloupce. Stejně tak počítaný sloupec může být použit pro výpočty v měřítku.

Kdy použít Měřítko a kdy Počítaný sloupec

V některých případech můžeme použít k dosažení požadovaného výsledku jak Měřítko, tak Počítaný sloupec. V každé situaci ovšem použijeme jiný DAX vzorec, protože měřítko je vyhodnoceno v kontextu filtru, zatímco počítaný sloupec v kontextu řádku.

Pokud s jazykem DAX začínáme, většinou používáme častěji Počítané sloupce, kde je možné postupně vytvářet různé mezivýpočty v tabulkách a tyto mezivýpočty si můžeme snadno zobrazit a následně znovu používat pro dosažení požadovaného výsledku. Po určitém čase a osvojení základních principů jazyka DAX můžeme tvořit efektivně a daleko jednodušeji všechny části výpočtů přímo v Měřítku. Pokud se nám nedaří dostat celou logiku výpočtu do Měřítka, můžeme si u některých složitějších konstrukcí předpočítat část hodnot v počítaném sloupci a tyto hodnoty následně použít jako základ pro výpočet v Měřítku. Tento postup může být použit také jako jedna z optimalizačních technik, pokud složité výpočty v Měřítcích zpomalují načítání reportů.

Existují ovšem situace, kdy nemáme na výběr, a správného výpočtu dosáhneme pouze pomocí Měřítek, nebo pouze pomocí Počítaných sloupců.

Obecně lze doporučit použít Počítaný sloupec vždy, pokud chceme:

- vložit výsledek výpočtu do řádků tabulky, na osu grafů, do sloupců v matrixu apod,
- vložit výsledek výpočtu do průřezů, díky kterým může uživatel filtrovat model,
- rozdělit záznamy v tabulce staticky do kategorií, bez ohledu na filtry v reportech,
- vytvořit nové záznamy v tabulce závislé striktně na kontextu řádku, například spojení textových řetězců z různých sloupců, apod.

Měřítko bychom měli použít vždy, když chceme:

- manipulovat při výpočtech s filtry, například při počítání procentuálních podílů nebo různých poměrových ukazatelů,
- agregovat hodnoty v rámci kategorií, podkategorií, let, měsíců nebo jiných ukazatelů,
- tvořit dynamické výpočty závislé na filtrech, které uživatel může měnit v reportech,
- manipulovat při výpočtech s relacemi v modelu, například aktivovat neaktivní relace mezi tabulkami nebo měnit směr filtrace v relacích mezi tabulkami.  

Shrnutí

Rozhodnutí, zda pro výpočet použít Měřítko nebo Počítaný sloupec, může záviset na mnoha faktorech. V některých případech může být volba jednoznačná a nemáme ani možnost výběru. V některých typech výpočtu můžeme použít také kombinaci obou přístupů, kdy si část výpočtu předpočítáme v Počítaném sloupci, a uložený mezivýpočet použijeme následně v Měřítku. Rozhodnutí o způsobu výpočtu mohou ovlivňovat také další faktory jako jsou velikost modelu, počet jedinečných hodnot ve sloupcích,  rychlost načítání reportu nebo složitost výpočtu a čitelnost a udržovatelnost DAX kódu. Obecně jsou ale měřítka hlavní výhodou a silnou stránkou, kterou máme k dispozici při rozšiřovaní Tabulárního modelu o vlastní výpočty, především díky jejich vyhodnocení v různých kontextech filtru, které si uživatel reportu může  sám měnit pomocí různých typů vizuálů. Kontext vyhodnocení pak může měnit také autor reportu programově, například pomocí funkce CALCULATE()

č. 26

Komentáře