V tomto příspěvku si na konkrétním příkladu v Power BI ukážeme, jakým způsobem je vyhodnoceno měřítko v aktuálním kontextu vyhodnocení, který obsahuje současně jak kontext filtru, tak kontext řádku.
Finální měřítko, ke kterému postupně dojdeme iterativní formou, bude relativně komplexní. Předpokladem pro jeho úplné pochopení je znalost principu kontextu filtru, kontextu řádku a principu změny kontextu řádku na kontext filtru. Všechny tyto tři základní principy jsou popsány v odkazovaných článcích a pokud to nebude nezbytně nutné, nebudou zde znovu popisovány. V tomto příspěvku se totiž zaměříme primárně na kombinaci všech těchto tří základních principů současně.
Začít můžeme popisem cílového reportu. V použitém modelu Adventure Works DW 2020.pbix je mimo jiné dimenzní tabulka 'Reseller', která obsahuje informace o prodejcích produktů. Našim cílem pak bude vytvořit měřítko, které bude vracet počet prodejců z tabulky 'Reseller', kteří prodali alespoň 100 jízdních kol ve vybraném období. Ačkoliv se jedná o jednoduché zadání, skrývá v sobě relativně velkou míru komplexity, jak si postupně vysvětlíme.
V celém příkladu budeme pracovat primárně se čtyřmi tabulkami, s faktovou tabulku 'Sales', kde jsou uloženy mimo jiné informace o počtech prodaných produktů, a dále s dimenzními tabulkami 'Reseller', 'Date' a 'Product'.
Z tabulky 'Date' budeme používat sloupec s roky, který bude použitý v Průřezu v reportu. Tabulku 'Product' budeme potřebovat pro zafiltrování modelu pouze na produkty z kategorie Bikes, protože chceme získat počet prodejců, kteří prodali alespoň 100 jízdních kol ve vybraném období, no a v tabulce 'Reseller' jsou uloženi prodejci, které budeme sčítat, pokud bude u daného prodejce splněna podmínka prodeje alespoň 100 produktů z vybrané kategorie Bikes.
Výsledný report bude vypadat následovně.
Analytický význam jednotlivých měřítek pro nás nebude v tomto příspěvku příliš důležitý, protože se primárně zaměříme na způsob vyhodnocení jednotlivých částí DAX kódu. Našim cílem bude postupně dojít k měřítku [Prodejci jízdních kol (nad 100 kusů)]. Jakmile budeme mít toto finální měřítko, přejdeme k vysvětlení, jakým způsobem je vyhodnoceno a jak jeho výsledek ovlivňuje vnější kontext vyhodnocení, a dále kontext filtru vytvořený programově uvnitř měřítka a kontext řádku, který si v měřítku také vytvoříme programově pomocí jedné z iteračních funkcí.
Začneme ale postupně, a to přípravou reportu. Pracovat budeme jako obvykle s vizuálem Matice, kde si můžeme přehledně zobrazovat výsledky jednotlivých výpočtů. Na obrázku níže můžeme vidět výchozí report, se kterým budeme pracovat.
Ve výchozím reportu jsou ve vizuálu Průřez roky z kalendářní tabulky 'Date'. Ve vizuálu matice jsou v řádcích hodnoty ze sloupce 'Reseller'[Country-Region] a měřítko [Prodeje].
Poznámka: Pod řádkem [Not Applicable] jsou zahrnuty všechny prodeje přes internet, které v použitém modelu nemají přiřazeného konkrétního prodejce. Z pohledu významu dat můžeme o tomto záznamu v tabulce 'Reseller' přemýšlet jako o dalším prodejci, prodejci přes internet, který ale nemá přiřazenou konkrétní prodejnu, adresu atd., a shlukuje pod sebou všechny prodeje uskutečněné přes internet.
Nyní máme vše připravené a můžeme se pustit do jednotlivých výpočtů. Jak už jsme si řekli v úvodu, našim cílem bude vytvořit měřítko, které bude vracet počet prodejců, kteří ve vybraném období prodali alespoň 100 ks jízdních kol.
Začneme měřítkem, které bude vracet pouze počet prodejců. K tomuto účelu bychom teoreticky mohli použít sloupec z faktové tabulky 'Sales', která obsahuje také sloupec 'Sales'[ResellerKey], použitý pro vytvoření relace mezi tabulkou 'Sales' a 'Reseller'.
Problémem ale je, že tento sloupec neobsahuje jedinečné identifikátory prodejců, ale jedinečné identifikátory aktuální verze každého prodejce. To znamená, že v tabulce 'Reseller' může mít jeden prodejce více záznamů, protože tato tabulka je typu SCD 2, kdy pokud dojde ke změně některého z atributů u prodejce, dojde k přidání nového záznamu do tabulky s jeho aktualizovanými hodnotami.
Z pohledu našeho řešení to tedy znamená, že nebudeme počítat jedinečné hodnoty ze sloupce 'Sales'[ResellerKey], ale jedinečné hodnoty ze sloupce 'Reseller'[Reseller ID], tedy ze sloupce z dimenzní tabulky 'Reseller'. To je zásadní rozdíl, jak si vysvětlíme na první verzi měřítka, které bude vracet počet prodejců v aktuálním kontextu vyhodnocení.
Měřítko:
Pokud vložíme nové měřítko do našeho vizuálu Matice, výsledek bude vypadat následovně.
Nové měřítko vrací počet prodejců v aktuálním státě, protože sloupec 'Reseller'[Country-Region], použitý v řádcích vizuálu matice, je ze stejné tabulky jako sloupec 'Reseller'[Reseller ID]. Problém ale je, že roky v průřezu tabulku 'Reseller' nefiltrují. Pokud v průřezu vybereme jiný rok, hodnoty měřítka [Všichni prodejci] budou stále stejné.
Změna roku v průřezu nijak neovlivňuje hodnoty měřítka [Všichni prodejci]. Jak tedy můžeme vidět, už při prvním kroku jsme narazili na problém, který souvisí primárně s kontextem filtru a s propagací filtrů v modelu.
Důvod, proč nejsou propagovány filtry z tabulky 'Date' do tabulky 'Reseller', zjistíme při pohledu na model a na nastavení směru filtrace u jednotlivých relací.
Jak můžeme vidět v diagramu modelu, filtry z tabulky 'Date' se dostanou do tabulky 'Sales', ale z tabulky 'Sales' se již nedostanou k tabulce 'Reseller'.
Jednou z možností, jak zajistit propagaci filtrů z tabulky 'Date' až do tabulky 'Reseller' bez nutnosti upravovat model, je využitím konceptu rozšířených tabulek.
Protože tabulka 'Date' filtruje tabulku 'Sales', můžeme při načítání hodnot z tabulky 'Reseller' začít u tabulky 'Sales', která bude zafiltrovaná, a tuto tabulku seskupit podle sloupce 'Reseller'[Reseller ID]. Tím dostaneme tabulku se všemi jedinečnými hodnotami ze sloupce 'Reseller'[Reseller ID], které jsou dostupné v aktuálním kontextu filtru. Následně zjistíme počet těchto hodnot a získáme požadovaný výsledek.
Měřítko:
Pokud nové měřítko [Prodejci] vložíme do vizuálu Matice, výsledkem bude počet prodejců v aktuálním státě, kteří byli současně aktivní ve vybraném roce v Průřezu.
Mezi měřítky [Všichni prodejci] a [Prodejci] je zásadní rozdíl. Měřítko [Všichni prodejci] vrací počet prodejců v aktuálním státě. Měřítko [Prodejci] pak vrací také počet prodejců v aktuálním státě, ale pouze těch, kteří mají ve vybraném roce záznam ve faktové tabulce 'Sales', to znamená pouze těch, kteří prodávali produkty ve vybraném roce.
Protože v našem finálním výpočtu budeme chtít všechny prodejce, kteří ve vybraném období prodali alespoň 100 jízdních kol, budeme dále rozvíjet druhou variantu výpočtu, která respektuje také filtry z tabulky 'Date'.
Dalším krokem může být přidání filtru, který nám zajistí, že uvidíme pouze ty prodejce, kteří prodávali mimo jiné také jízdní kola. To můžeme udělat jednoduše pomocí funkce CALCULATE() následujícím způsobem.
Měřítko:
Pokud vložíme nové měřítko do vizuálu, měli bychom vidět menší počty prodejců než u měřítka [Prodejci], protože ne všichni obchodníci museli prodávat jízdní kola.
Nyní zbývá vyřešit poslední, nejtěžší úkol. Jak přidat do měřítka filtr, který zajistí, že uvidíme pouze prodejce, kteří prodali alespoň 100 jízdních kol.
Sloupec, který obsahuje počet prodaných kusů produktů, najdeme v tabulce 'Sales'.
Problém ale je, že sloupec 'Sales'[Order Quantity] nemůžeme přímo použít ve filtru funkce CALCULATE(), protože obsahuje počet prodaných kusů z jednotlivých řádků objednávek. V tabulce 'Sales' tedy nejsou data v granularitě po letech nebo po kategoriích produktů, ale jeden záznam v této tabulce odpovídá jednomu řádku na objednávce.
My ale potřebujeme zafiltrovat prodejce ne podle jednotlivých transakcí, protože žádný člověk si pro osobní potřebu asi nekoupí současně 100 kol, ale podle agregace hodnot z tohoto sloupce. Jinými slovy potřebujeme zafiltrovat prodejce podle celkového počtu prodaných jízdních kol za vybrané období.
K tomuto účelu si vytvoříme pomocné měřítko, které bude vracet sumu hodnot ze sloupce 'Sales'[Order Quantity].
Měřítko:
Měřítko [Prodané kusy] sice bude vracet agregované hodnoty ze sloupce 'Sales'[Order Quantity], ale to stále neřeší náš problém. Měřítko totiž nemůžeme použít přímo ve filtru funkce CALCULATE(), protože ve filtrech ve funkci CALCULATE() můžeme použít pouze sloupce nahrané v modelu.
Jednotlivé prodejce ale můžeme prostřednictvím měřítka filtrovat nepřímo. K tomuto účelu můžeme použití iterační funkci FILTER(), pomocí které půjdeme řádek po řádku v naší virtuální tabulce s jednotlivými prodejci, a tuto virtuální tabulku s jedním sloupcem zafiltrujeme pouze na ty hodnoty, pro které bude měřítko [Prodané kusy] vracet hodnotu větší než 100.
Měřítko:
Nové měřítko si vložíme do vizuálu Matice a zobrazíme si výsledky.
Nové měřítko [Prodejci jízdních kol (nad 100 kusů)] vrací správné hodnoty, tedy počet prodejců, kteří ve vybraném roce prodali alespoň 100 jízdních kol. Jak výpočet funguje a jak je vyhodnocen si popíšeme na jedné konkrétní buňce, protože každé měřítko je z podstaty vyhodnoceno v každém bodě vizuálu samostatně.
Uvažujme například řádek se státem Canada, kde vrací měřítko [Prodejci jízdních kol (nad 100 kusů)] hodnotu 4.
Na celý výpočet působí v této zvýrazněné buňce vnější filtr, který se skládá z filtru působícího na výpočet z vizuálu Průřez a z filtru nastaveného na aktuální stát v aktuálním řádku vizuálu Matice.
Následně je vyhodnocena funkce CALUCLATE(), pomocí které nejdříve přidáváme filtr, tentokrát na kategorii Bikes.
Prvním argumentem funkce CALCULATE() je pak výraz, který obsahuje tři vnořené funkce, a který je vyhodnocen v následujícím kontextu filtru.
Pokud máme v jazyku DAX vnořené funkce, tak k jejich vyhodnocení dochází téměř vždy postupně od nejvnitřnější funkce k vnějším funkcím.
Poznámka: Výjimku tvoří pouze funkce CALCULATE() a CALCULATETABLE(), které umožňují měnit kontext filtrů, a proto u těchto funkcí dochází nejdříve k vyhodnocení filtrů, a až následně k vyhodnocení prvního argumentu. K tomuto tématu je k dispozici samostatný příspěvek dostupný pod tímto odkazem.
Jako první je proto vyhodnocena funkce SUMMARIZE(), která bude vracet jeden sloupec se všemi prodejci, kteří prodávali jízdní kola, a to ve státě Canada a v roce 2020. Těchto prodejců je celkem 63. Na následujícím obrázku můžeme vidět prvních několik řádků z této virtuální tabulky, kterou načítáme pomocí funkce SUMMARIZE().
Na obrázku výše tak můžeme vidět, jak přesně je zafiltrovaný model před vyhodnocením výpočtu SUM(Sales[Order Quantity]) v měřítku [Prodané kusy] v každém řádku iterované tabulky uvnitř funkce FILTER(). Modře zvýrazněné řádky obsahují prodejce, kteří v splňují podmínky alespoň 100 prodaných kusů v roce 2020 ve státě Canada.
Obdobným způsobem je měřítko [Prodejci jízdních kol (nad 100 kusů)] vyhodnoceno v dalších řádcích vizuálu Matice, pouze vždy s jinou kombinací filtrů, které na výpočet působí.
Shrnutí
Na příkladu v tomto příspěvku jsme si ukázali, že jazyk DAX je velmi specifický programovací jazyk, protože výsledky výpočtů neovlivňuje pouze samotný DAX kód. Výsledky výpočtů mohou být ovlivněny vnějším kontextem vyhodnocení, strukturou modelu a nastavením relací mezi tabulkami. Všechny tyto faktory musíme brát v potaz a musíme je zakomponovat do našich úvah při řešení komplexních úloh.
Komentáře
Okomentovat