V tomto příspěvku je popsána jedna ze situací, ve které může dojít k duplicitnímu započítání hodnot při vyhodnocení výpočtu, ve kterém dochází ke změně kontextu řádku na kontext filtru.
Změna kontextu řádku na kontext filtru
V ukázce budeme pracovat z jednoduchou tabulkou, která bude obsahovat pouze čtyři sloupce a sedm řádků. Tabulku si můžeme vytvořit přímo v Power BI. Na kartě "Modelování" vybereme možnost "Nová tabulka", a do řádku vzorců můžeme vložit následující DAX výraz, který vygeneruje tabulku.
Počítaná tabulka:
Prodeje =
SELECTCOLUMNS
(
{
("A", "1.1.2021", 10, 8 ),
("A", "1.1.2021", 10, 8 ),
("B", "1.1.2021", 20, 16),
("C", "2.1.2021", 30, 24),
("B", "2.1.2021", 20, 16),
("C", "3.1.2021", 30, 24),
("A", "3.1.2021", 10, 8 )
},
"Produkt", [Value1],
"Datum", [Value2],
"Cena", [Value3],
"Naklady", [Value4]
)
Výsledná tabulka by měla vypadat následovně.
Ke změně kontextu řádku na kontext filtru dochází, pokud použijeme funkci
CALCULATE() nebo funkci
CALCULATETABLE() v kontextu řádku. V této souvislosti je ještě důležité zmínit, že každé měřítko je na pozadí obalené do pro uživatele skryté funkce
CALCULATE(). Ke změně kontextu proto dochází také při vyhodnocení měřítek v kontextu řádků. Jelikož změna kontextu úzce souvisí s
kontextem řádku a
kontextem filtru, nejdříve si můžeme připomenout, co znamenají tyto dva pojmy.
Kontext řádku
Kontext řádku vzniká automaticky při vytvoření nového počítaného sloupce v tabulce nebo při vyhodnocení výpočtů v iteračních funkcích. Příkladem může být počítaný sloupec ve dříve připravené tabulce, ve kterém odečteme náklady od ceny produktu, čímž dostaneme hrubý zisk.
Počítaný sloupec:
Zisk = Prodeje[Cena] - Prodeje[Naklady]
Výsledek může vypadat následovně.
Výpočet, který je vidět na obrázku výše v řádku vzorců, je vyhodnocen řádek po řádku, jak je patrné z výsledku nového kalkulovaného sloupce. Ve vzorci ale není nikde uvedeno, pro který řádek má být výpočet vyhodnocen. Ve vzorci se odkazujeme pouze na názvy sloupců. To, že jsou v počítaném sloupci pro výpočet použity hodnoty ze stejného řádku, je dáno právě kontextem, ve kterém je vzorec vyhodnocen. Tento kontext se nazývá kontext řádku. Kontext řádku vzniká také při vyhodnocení výrazů v iteračních funkcích.
Výše uvedený příklad vypadá přirozeně a na první pohled jednoduše. Při psaní DAX výrazů je ale nutné s tímto kontextem vždy počítat, stejně jako s kontextem filtru, ve kterém jsou výrazy vyhodnoceny.
Sumarizační funkce, jako je například funkce SUM(), kontext řádku ignorují. To si můžeme ověřit vytvořením nového počítaného sloupce, kde ve vzorci použijeme sumarizační funkci SUM().
Počítaný sloupec:
Suma zisku = SUM(Prodeje[Zisk])
Výsledkem je nový sloupec, který vrací v každém řádku stejnou hodnotu.
Výsledkem je v každém řádku stejná hodnota, která představuje součet celého sloupce 'Prodeje'[Zisk], bez ohledu na kontext řádku, který agregační funkce ignorují.
Kontext filtru
Kontext filtru je sada filtrů, které jsou aplikovány v datovém modelu před vyhodnocením výrazu. Příkladem může být měřítko vyhodnocené v kontextu filtru, který přichází z průřezu. K vytvoření měřítka použijeme stejný výpočet, který jsme dříve použili pro vytvoření počítaného sloupce se sumou ze sloupce 'Prodeje'[Zisk].
Měřítko:
Suma zisku měřítko = SUM(Prodeje[Zisk])
Nové měřítko můžeme vložit do vizuálu Karta, a v průřezu zafiltrovat pouze produkty B a C.
Výsledná hodnota je vypočítána jako suma hodnot ve sloupci 'Prodeje'[Zisk] pro produkty B a C. Produkty s názvem A nejsou do výpočtu zahrnuty, výsledná hodnota je tedy 20. Zjednodušeně si je možné představit, že uvedený model s jednou tabulkou je před vyhodnocením počítané míry filtrovaný následně.
Kontext filtru nemusí přicházet pouze z průřezů, ale o kontextu filtru můžeme mluvit i následujícím příkladu, kdy počítaná míra, která je definována jako suma ze sloupce 'Prodeje'[Zisk], je v každém řádku vizuálu tabulky vyhodnocena v kontextu filtru daného produktu. Například v prvním řádku tabulky je počítaná míra [Suma zisku měřítko] vyhodnocena v kontextu filtru produktů A.
V prvním řádku tabulky je suma hodnot ze sloupce 'Prodeje'[Zisk] pro všechny prodané produkty s názvem A. Ve druhém řádku suma za všechny prodané produkt B a ve třetím řádku suma pro produkty C. Při vyhodnocení měřítka v řádku "Celkem" není aplikovaný na výpočet žádný kontext filtru, a jedná se proto o sumu všech hodnot pro sloupec 'Prodeje'[Zisk].
Funkce CALCULATE a změna kontextu řádku na kontext filtru
Funkce CALCULATE() má níže uvedenou syntaxi. První argument funkce je povinný, ostatní argumenty jsou volitelné.
Syntaxe funkce CALCULATE():
CALCULATE(<výraz>[, <filtr1> [, <filtr2> [, …]]])
Jednou z vlastností funkce CALCULATE() je změna kontextu řádku na kontext filtru. Tuto vlastnost většina autorů při psaní DAX kódu ignoruje a ve většině případů ji ani znát nemusí. Nicméně v některých situacích může při změně kontextu docházet k neočekávaným výsledkům, jak je možné vidět v následující ukázce.
Změna kontextu
Jak bylo uvedeno výše, agregační funkce, jako je funkce SUM(), ignorují při vyhodnocení kontext řádku. Výsledkem je, že ve sloupci 'Prodeje'[Suma zisku] je v každém řádku stejná hodnota 26, což je součet všech hodnot ze všech řádků ve sloupci 'Prodeje'[Zisk].
Pokud stejný vzorec obalíme do funkce CALCULATE(), dojde při vyhodnocení počítaného sloupce ke změně kontextu řádku na kontext filtru. Funkce SUM() je tedy vyhodnocena pro každý řádek v kontextu filtru, který je nově vytvořen funkcí CALCULATE().
Počítaný sloupec:
Suma zisku CALCULATE = CALCULATE(SUM(Prodeje[Zisk]))
Výsledek nového počítaného sloupce s použitím funkce CALCULATE() je následující.
Výsledkem je nový počítaný sloupec, který může v některých řádcích obsahovat překvapivé hodnoty. V prvním a druhém řádku je výsledek výrazu hodnota 4, tedy dvojnásobek zdrojové hodnoty ze sloupce 'Prodeje'[Zisk]. V dalších pěti řádcích jsou hodnoty v novém sloupci stejné jako ve zdrojovém sloupci.
Po změně kontextu řádku na kontext filtru je výraz ve funkci CALCULATE() vyhodnocen v kontextu filtru. Tento nový filtr obsahuje hodnoty všech sloupců v aktuálním řádku. Pokud tabulka nemá primární klíč, může pak tomuto filtru v některých situacích odpovídat i více řádků.
Výraz uvedený v řádku vzorců, který definuje nový počítaný sloupec 'Prodeje'[Suma zisku CALCULATE], je vyhodnocený řádek po řádku. V prvním řádku je pro výpočet sumy ze sloupce 'Prodeje'[Zisk] aplikován na model filtr, který obsahuje všechny hodnoty ze všech sloupců prvního řádku. Jelikož jsou první dva řádky shodné, tedy duplicitní, po aplikování filtru pro výpočet hodnoty v prvním řádku je výsledkem filtru tabulka se dvěma řádky, a výsledná hodnota je tedy dvojnásobná oproti původní hodnotě ve sloupci 'Prodeje'[Zisk]. Stejná situace se opakuje ve druhém řádku a výsledek je opět dvojnásobek původní hodnoty, tedy suma hodnot sloupce 'Prodeje'[Zisk]v prvních dvou řádcích. V dalších řádcích již aplikovaný filtr zobrazí pouze jeden řádek, protože ostatní řádky jsou již jedinečné.
Řešením, jak se vyhnout duplicitnímu započítání hodnot při změně kontextu řádku na kontext filtru, může být přidání sloupce s primárním klíčem, který obsahuje jedinečné hodnoty pro každý řádek. Můžeme si proto vytvořit novou tabulku prodejů, do které přidáme nový sloupec s názvem 'Prodeje 2'[ID] s primárním klíčem, který bude jedinečný v každém řádku tabulky.
Počítaná tabulka:
Prodeje 2 =
SELECTCOLUMNS
(
{
(1, "A", "1.1.2021", 10, 8 ),
(2, "A", "1.1.2021", 10, 8 ),
(3, "B", "1.1.2021", 20, 16),
(4, "C", "2.1.2021", 30, 24),
(5, "B", "2.1.2021", 20, 16),
(6, "C", "3.1.2021", 30, 24),
(7, "A", "3.1.2021", 10, 8 )
},
"ID", [Value1],
"Produkt", [Value2],
"Datum", [Value3],
"Cena", [Value4],
"Naklady", [Value5],
"Zisk", [Value4] - [Value5]
)
V nové tabulce si můžeme vytvořit stejný výpočet, ve kterém opět dojde ke změně kontextu řádku na kontext filtru, díky funkci CALCULATE().
Počítaný sloupec:
Suma zisku CALCULATE = CALCULATE(SUM('Prodeje 2'[Zisk]))
Výsledek v případě tabulky bez duplicitních řádků vypadá následovně.
Nyní jsou všechny řádky v tabulce jedinečné a výsledek výrazu pro výpočet kalkulovaného sloupce odpovídá sloupci 'Prodeje 2'[Zisk] v každém řádku tabulky.
Shrnutí
Změna kontextu řádku na kontext filtru funkcionalita, která velmi zjednodušuje většinu výpočtů v jazyku DAX. Pozor bychom si měli dát při použití funkce CALCULATE() v iteračních funkcích a zejména v počítaných sloupcích, kdy může docházet k duplicitnímu započítání hodnot, pokud pracujeme s tabulkami, které obsahují duplicitní záznamy. Kombinace použití funkce CALCULATE() k vytvoření nového počítaného sloupce v tabulce obsahující duplicity je zřejmě málo pravděpodobná, nicméně dojít k této situaci může a proto je dobré o vlastnosti popsané v tomto příspěvku vědět.
č. 5
Komentáře
Okomentovat