V tomto příspěvku si ukážeme tři příklady, ve kterých odstranění filtrů pomocí funkcí ALL() nebo REMOVEFILTERS() nemusí fungovat tak jak bychom očekávali. Na konkrétních příkladech si vysvětlíme proč tomu tak je a jak tyto situace vyřešit.
K tomuto tématu je k dispozici také video:
Funkce ALL() a funkce REMOVEFILTERS(), pokud jsou obě použity pro odstranění filtrů přímo ve funkci CALCULATE(), fungují identicky. Důvod, proč jsou v jazyku DAX k dispozici dvě funkce, které můžeme používat k odstraňování filtrů ve funkci CALCULATE(), je podrobněji popsán v samostatném příspěvku.
V příkladech v tomto příspěvku budeme z výše popsaného důvodu pracovat pouze s funkcí REMOVEFILTERS(). Pokud bychom v kterémkoliv výpočtu uvedeném v tomto příspěvku nahradili funkci REMOVEFILTERS() funkcí ALL(), dostali bychom naprosto totožné výsledky.
Odstranění filtrů ze sloupců seřazených podle hodnot v jiném sloupci
Typická situace, při které se může zdát, že funkce REMOVEFILTERS() nefunguje správně, je odstraňování filtrů ze sloupce, který je seřazený podle hodnot v jiném sloupci.
Uvažujme například následující jednoduchý report.
Pokud bychom chtěli vytvořit měřítko, které bude vracet procentuální podíl prodejů v aktuálním měsíci vůči prodejům za celý rok, následující výpočet nebude fungovat podle očekávání.
Měřítko:
Nové měřítko si můžeme vložit do vizuálu a zobrazit si výsledek.
Měřítko [% Prodeje v roce (špatně)] vrací v každém řádku vizuálu 100 %, protože nedělíme prodeje v aktuálním měsíci prodeji za celý rok, ale prodeje v aktuálním měsíci zase prodeji za aktuální měsíc. Odstranění filtrů ze sloupce s názvy měsíců totiž nezafungovalo podle očekávání. To si můžeme znázornit například tak, že si vytvoříme měřítko, které bude obsahovat část výpočtu, která má vracet prodeje za celý rok.
Měřítko:
Nové měřítko vrací stejné hodnoty jako měřítko [Prodeje].
Důvod, proč nezafunguje odstranění filtrů ze sloupce 'Date'[Měsíc] spočívá v tom, že tento sloupec je na pozadí seřazený podle sloupce 'Date'[Měsíc číslo], a to proto, aby se měsíce ve vizuálech zobrazovaly v pořadí, jak jdou za sebou v roce, a ne v abecedním pořadí.
Power BI tak při generování DAX dotazu použilo také sloupec 'Date'[Měsíc číslo], a každé měřítko, které je vyhodnocené v kontextu filtru sloupce 'Date'[Měsíc], bude vyhodnoceno také v kontextu filtru sloupce 'Date'[Měsíc číslo].
Řešení je v tomto případě jednoduché. Abychom dostali v každém řádku vizuálu s měsíci v řádcích prodeje za celý rok, musíme odstranit filtry také ze sloupce 'Date'[Měsíc číslo], který je na pozadí použitý pro řazení výsledků DAX dotazu pro vizuál.
Měřítko:
Po odstranění filtrů jak ze sloupce 'Date'[Měsíc], tak ze sloupce 'Date'[Měsíc číslo], dostaneme v každém řádku vizuálu opravdu prodeje za celý rok.
Ačkoliv sloupec 'Date'[Měsíc číslo] není přímo zobrazen ve vizuálu, na pozadí je tento sloupec určený pro řazení použitý, a ve výpočtech proto musíme pracovat i s tímto sloupcem, zejména pokud manipulujeme s filtry pomocí funkce CALCULATE().
S nově získanou znalostí tak můžeme dokončit měřítko [% Prodeje v roce].
Měřítko:
Nová verze výpočtu již bude vracet správné výsledky.
S velmi podobnou situací se můžeme setkat také pokud pracujeme se sloupcem, který je seskupen podle jednoho nebo více jiných sloupců, jak si ukážeme v následující části příspěvku.
Odstranění filtrů a vlastnost Seskupit podle sloupců
V tabulárním modelu můžeme u sloupců nastavit seskupení podle jiných sloupců. To může mít určité výhody, nicméně opět musíme při psaní DAX výpočtů myslet na to, že sloupce použité pro seskupení jsou součástí DAX dotazů generovaných vizuály.
Uvažujme například jména zákazníků a prodeje těmto zákazníkům.
Je přirozené, že jména zákazníků mohou být duplicitní. Jinými slovy stejné jméno může mít více zákazníků. V takovém případě ale Power BI automaticky seskupí více zákazníků se stejným jménem do jednoho řádku, a zobrazí nám celkovou částku za všechny zákazníky se stejným jménem v jedné buňce.
Efekt, který vidíme na předchozím obrázku, kdy jsou dva zákazníci se stejným jménem na dvou samostatných řádcích, není výsledkem výchozího chování vizuálů, ale vlastnosti Seskupit podle sloupce (Group By Columns), v rámci které je sloupce 'Customer'[Full Name] seskupen také podle sloupce 'Customer'[CustomerKey].
Tuto vlastnost nemůžeme nastavit přímo v Power BI desktop, ale můžeme ji nastavit například pomocí Tabular Editoru.
Ačkoliv může být použití této vlastnosti užitečné v několika specifických situacích, může způsobovat také problémy při tvorbě DAX výpočtů.
Pokud bychom například chtěli získat prodeje za všechny zákazníky, následující výpočet nebude fungovat.
Měřítko:
Nové měřítko si můžeme vložit do vizuálu se jmény zákazníků v řádcích a zobrazit si výsledky.
Pokud používáme vlastnost Seskupit podle sloupců, musíme se sloupci použitými pro seskupení pracovat také v DAX výpočtech, protože tyto sloupce jsou vždy součástí DAX dotazů, které generují vizuály.
V našem příkladu je sloupec 'Customer'[Full Name] seskupen podle sloupce 'Customer'[CustomerKey].
Měřítko, které bude vracet prodeje za všechny zákazníky, tak bude vypadat následovně.
Měřítko:
Pokud odstraníme filtry také ze sloupce použitého pro seskupení, dostaneme požadovaný výsledek.
Vlastnost Seskupit podle sloupců může být velmi užitečná, nicméně může přinášet nadměrné složitosti při psaní DAX výpočtů, a tentokrát se nemusí jednat pouze o odstraňování filtrů ve funkci CALCULATE().
Odstranění filtrů a rozšířené tabulky
Další situace, kdy chování funkce REMOVEFILTERS() nemusí být úplně intuitivní, je situace, kdy odstraňujeme filtry z celé tabulky a současně pracujeme se SnowFlake modelem.
Model, se kterým pracujeme v tomto příspěvku, vypadá následovně.
V této části příspěvku nás budou zajímat především tabulky 'Product', 'Subcategory' a 'Category'. Podkategorie a kategorie produktů jsou atributy, které logicky patří k produktům. V použitém modelu jsou ale tyto atributy normalizované v samostatných tabulkách. Tento model tak není klasické Star schema, ve kterém by kategorie a podkategorie produktů byly denormalizované v tabulce 'Product', ale jedná se o takzvané Snowflake schema.
Tato úroveň normalizace (atributy patřící do tabulky 'Product' jsou v samostatných tabulkách) může dávat za určitých okolností smysl, ale musíme myslet na to, že se jedná o vybočení z takzvané dobré praxe a že tento krok může mít také vliv na psaní DAX výpočtů.
Uvažujme například následující jednoduchý report, kde jsou v průřezu kategorie produktů z tabulky 'Category', a ve vizuálu matice podkategorie produktů z tabulky 'Subcategory' a měřítko prodeje v hodnotách.
Pokud bychom chtěli, například pro výpočet procentuálního podílu, vytvořit výpočet který bude vracet prodeje za všechny podkategorie, které patří do aktuálně vybrané kategorie v průřezu, následující výpočet nebude fungovat správně.
Měřítko:
Tabulka 'Subcategory' obsahuje kromě sloupce s podkategoriemi produktů pouze další dva sloupce, které obvykle nepoužíváme v reportech a které slouží pouze pro vytvoření relací s ostatními tabulkami.
Protože tabulka 'Subcategory' obsahuje pouze jeden sloupec který používáme v reportech, může se zdát, že pokud v měřítku [Prodeje všechny podkategorie (špatně)] odstraňujeme filtry z celé tabulky 'Subcategory', je to to samé, jako bychom odstranili filtry ze sloupce 'Subcategory'[Subcategory] použitého v řádcích vizuálu matice.
Hodnoty, které vrací měřítko [Prodeje všechny podkategorie (špatně)], pokud máme v průřezu vybranou jednu nebo více kategorií, tak mohou být překvapivé.
Měřítko [Prodeje všechny podkategorie (špatně)] vrací v každém řádku vizuálu prodeje za všechny produkty, ačkoliv v tomto měřítku odstraňujeme filtry z tabulky 'Subcategory', a na výpočet by měly stále působit filtry ze sloupce 'Category'[Category], který je použitý v průřezu a kde je vybraná kategorie "Accessories".
Důvodem, proč nyní měřítko [Prodeje všechny podkategorie (špatně)] nevrací hodnotu 700 759,96, tedy prodeje za produkty v kategorii "Accessories", ale hodnotu 29 358 677,22, tedy prodeje za produkty ve všech kategoriích, je že pokud odstraňujeme filtry z celé tabulky, odstraňujeme ve skutečnosti filtry z této tabulky v její rozšířené verzi.
Do rozšířené verze tabulky patří všechny sloupce z tabulek, které jsou s danou tabulkou propojeny pomocí relací a jsou současně v tomto vztahu na straně 1.
Pokud tedy odstraňujeme filtry z celé tabulky 'Subcategory', odstraňujeme filtry také z tabulky 'Category' a ze sloupce 'Category'[Category], který je v průřezu.
Abychom se vyhnuli efektu odstranění filtrů z rozšířené verze tabulky, musíme pracovat s jednotlivými sloupci, a ne s celými tabulkami.
Požadovaného výsledku tak dosáhneme pomocí následujícího měřítka.
Měřítko:
Nové měřítko již bude ignorovat filtry z řádků vizuálu, ale stále bude reagovat na filtry působící na výpočet z průřezu. Výsledkem tak budou prodeje za všechny produkty ve vybrané kategorii.
Díky výpočtu v měřítku [Prodeje všechny podkategorie] tak můžeme získat takzvaný vizuální podíl, to znamená podíl, kde budeme dělit prodeje za produkty ve vybrané podkategorii v aktuálním řádku vizuálu prodeji za všechny podkategorie, které patří do kategorie vybrané v průřezu.
Komentáře
Okomentovat