Funkce IF() použitá v měřítku může za určitých okolností a především při práci s velkými objemy dat způsobovat výkonnostní problémy při načítání Power BI reportů. V tomto příspěvku si proto na dvou příkladech představíme dvě odlišné techniky, pomocí kterých můžeme v použitých příkladech nahradit funkci IF() jiným typem výpočtu s výrazně lepším výkonem.
K tomuto tématu je k dispozici také video:
Nahrazení funkce IF() jiným typem výpočtu vyžaduje určitou míru kreativity. Současně musíme mít vždy jistotu, že alternativní výpočet bude vracet správné výsledky a dále že bude vyhodnocen rychleji. Pokud by totiž nová verze výpočtu byla pomalejší, nemá smysl nahrazovat funkci IF() alternativním, obvykle méně čitelným a proto také hůře udržovatelným DAX kódem.
Všechny příklady nahrazení funkce IF() popsané v tomto příspěvku jsou lepší alternativou za konkrétní měřítka v konkrétním modelu. Při jakékoliv optimalizaci je ale nutné brát v potaz také faktory jako jsou velikost modelu, struktura jednotlivých tabulek, typ výpočtu a další. Popsané principy optimalizace by tak čtenář měl brát pouze jako inspiraci, protože v jiném modelu nebo v jiném typu výpočtu může být celkový efekt optimalizace odlišný. Obecně se dá říct, že kdykoli je výsledkem funkce IF() číslo, dá se tato funkce nahradit jinou logikou. Zda je tato nová verze výpočtu efektivnější z pohledu výkonu je ale nutné vždy změřit.
V prvním příkladu ve kterém budeme nahrazovat funkci IF() jinou logikou budeme chtít vrátit jedno nebo druhé měřítko na základě určité podmínky. Ve druhém příkladu si pak ukážeme, jakým způsobem můžeme v jednom konkrétním výpočtu nahradit funkci IF() pomocí kombinace filtrů ve funkci CALCULATE().
Optimalizace výpočtů nahrazením funkce IF
Uvažujme situaci, ve které bychom chtěli za určitých okolností vrátit jedno měřítko, a za jiných podmínek druhé měřítko. V našem příkladu budeme pracovat s jednoduchými měřítky [Prodeje] a [Počet zákazníků].
Měřítka:
Obě nová měřítka si můžeme vložit do vizuálu Matice spolu se dny z kalendářní tabulky v řádcích.
V novém měřítku, které si můžeme nazvat obecně [Ukazatel], budeme chtít zobrazit hodnotu měřítka [Prodeje] v případě, kdy jsou aktuální prodeje v aktuální den vyšší nebo rovny průměrným denním prodejům. Pokud budou aktuální prodeje nižší než je denní průměr, zobrazíme hodnotu měřítka [Počet zákazníků]. Tento výpočet samozřejmě nemá žádný analytický význam. Měřítka [Prodeje] a [Počet zákazníků] jsou použita pouze pro zjednodušení a zobecnění daného problému. Celý výše popsaný výpočet může vypadat například následovně.
Měřítko:
V proměnné PrumerneDenniProdeje nejdříve pomocí funkce AVERAGEX() získáme hodnotu průměrných denních prodejů. Následně je tato proměnná použita v prvním argumentu funkce IF(), kde zjišťujeme zda je aktuální hodnota měřítka [Prodeje] větší nebo rovna dennímu průměru. Pokud ano, vrátíme hodnotu měřítka [Prodeje]. Pokud ne, vrátíme hodnotu měřítka [Počet zákazníků].
Stejného výsledku jako vrací měřítko [Ukazatel (IF)] můžeme dosáhnout i následujícím způsobem bez použití funkce IF().
Měřítko:
Proměnná PrumerneDenniProdeje obsahuje stejný výpočet jako v originálním měřítku. V následující proměnné je uložena pouze podmínka, která byla v prvním argumentu funkce IF() v originálním měřítku. Nejdůležitější část pak můžeme najít v proměnné Vypocet. V této proměnné je matematický výraz, který vrací hodnotu měřítka [Prodeje] pokud proměnná Podminka nabývá hodnoty TRUE a hodnotu měřítka [Počet zákazníků] pokud proměnná Podminka obsahuje hodnotu FALSE.
Celá logika vychází z předpokladu, že hodnota TRUE je v jazyku DAX na pozadí vyjádřena číslem jedna, a hodnota FALSE je na pozadí vyjádřena číslovkou 0. Pokud je tedy podmínka splněna, vynásobíme měřítko [Prodeje] číslem jedna a k této hodnotě přičteme číslovku nula vynásobenou hodnotou měřítka [Počet zákazníků], což bude za všech okolností číslo 0 a na hodnotě měřítka [Prodeje] se při splnění podmínky nic nezmění. Pokud podmínka nebude splněna, vynásobíme naopak hodnotu měřítka [Prodeje] číslem 0 a k tomuto výrazu vracejícímu vždy číslo 0 přičteme výraz jedna krát hodnota měřítka [Počet zákazníků]. Při splnění podmínky proto bude výsledkem měřítko [Prodeje], a při nesplnění podmínky měřítko [Počet zákazníků].
Jak je možné vidět na obrázku výše, obě měřítka vrací stejné hodnoty. Z pohledu výkonností je pak v tomto konkrétním příkladu vyhodnoceno měřítko [Ukazatel (bez IF)] průměrně dvakrát rychleji něž měřítko [Ukazatel (IF)]. Opět ale platí, že při optimalizaci hrají důležitou roli i další faktory než samotný výpočet a vždy je nutné před výběrem jednoho nebo druhého výpočtu změřit výkon.
V dalším příkladu optimalizace DAX výpočtu s použitím funkce IF() budeme pracovat s měřítkem [Prodeje], v rámci kterého budeme aplikovat slevu na produkty z vybrané kategorie. Pro zopakování si můžeme znovu zobrazit definici tohoto měřítka.
Měřítko:
Měřítko [Prodeje] si vložíme do vizuálu Matice spolu s kategoriemi produktů v řádcích.
V měřítku [Prodeje] násobíme v každém řádku tabulky 'Sales' počet odebraných kusů jednotkovou cenou. V nové verzi výpočtu pak budeme chtít pro produkty z kategorie "Bikes" aplikovat 30% slevu. To znamená, že v každém řádku tabulky 'Sales' budeme pomocí funkce IF() ověřovat, jestli aktuální produkt patří do kategorie "Bikes". Pokud ano, aplikujeme 30% slevu, pokud ne, ponecháme původní cenu produktu. Celý takto popsaný výpočet může vypadat například následovně.
Měřítko:
Nové měřítko si můžeme vložit do původního vizuálu a zobrazit si výsledky.
Jak je možné vidět na obrázku výše, nové měřítko vrací pro kategorii "Bikes" nižší hodnotu než měřítko [Prodeje], právě díky slevě na všechny produkty z této kategorie.
Dosáhli jsme tedy požadovaného výsledku, ale způsob použití funkce IF() v měřítku [(IF) Prodeje (kola ve slevě)] není úplně ideální z pohledu výkonnosti. Problémem je že tabulka 'Sales' je faktová tabulka která může potencionálně obsahovat velké množství záznamů, a v každém řádku této tabulky musí být vyhodnocena logická podmínka ve funkci IF(), což je z pohledu výkonnosti relativně nákladné. Výpočet v měřítku [(IF) Prodeje (kola ve slevě)] pak můžeme nahradit například následujícím způsobem.
Měřítko:
Pokud novou verzi měřítka vložíme do původního vizuálu, obě měřítka budou opět vracet stejné výsledky.
Logika výpočtu v měřítku [(Bez IF) Prodeje (kola ve slevě)] není nijak složitá. V první funkci CALCULATE() je filtr nastavený pouze na kategorii "Bikes", a pro produkty z této kategorie je potom v prvním argumentu funkce CALCULATE() aplikována 30% sleva. K výsledku první funkce CALCULATE() pak přičteme výsledek druhé funkce CALCULATE(), ve které chceme získat prodeje za všechny ostatní produkty než za produkty v kategorii "Bikes", a pro tyto produkty pak používáme jednotkovou cenu bez aplikování slevy.
Z pohledu výkonnosti je pak měřítko [(Bez IF) Prodeje (Kola ve slevě)] vyhodnoceno v průměru třikrát rychleji něž původní měřítko s použitím funkce IF().
Shrnutí
Funkce IF() může za určitých okolností způsobovat výkonnostní problémy při načítání Power BI reportů. Tato situace nastává zejména při práci s velkými objemy dat nebo při použití funkce IF() v rámci iteračních funkcí. Pokud dokážeme identifikovat, že výkonností problém způsobuje právě funkce IF(), můžeme se pokusit vytvořit novou verzi výpočtu, ve které funkci IF() nahradíme jinou logikou. To je možné zejména v situacích, kdy je výsledkem funkce IF() za všech okolností číslo, to znamená pokud je výsledkem funkce IF() číslo jak při splnění, tak při nesplnění podmínky v prvním argumentu. Výsledný efekt optimalizace ale není ani při vytvoření alternativní logiky zaručen. Proto je nutné vždy před výběrem jedné nebo druhé varianty výpočtu změřit výkon.
Komentáře
Okomentovat