DAX funkce VALUES

Funkce VALUES() je jednou ze základních funkcí v jazyku DAX, a to především z důvodů její univerzálnosti a široké použitelnosti v různých typech DAX výpočtů. Funkce VALUES() má jeden povinný argument, kterým může být odkaz na sloupec nebo odkaz na tabulku. Pokud použijeme funkci VALUES() s argumentem ve formě sloupce, výsledkem budou všechny jedinečné hodnoty z tohoto sloupce, tedy hodnoty bez duplicit, které jsou dostupné v aktuálním kontextu vyhodnocení. Pokud použijeme funkci VALUES() s argumentem ve formě tabulky, výsledkem budou všechny sloupce z této tabulky se všemi řádky dostupnými v aktuálním kontextu, včetně případných duplicitních záznamů. Funkce VALUES() se ale obvykle používá s argumentem ve formě sloupce.

Příklad VALUES

Příklady v tomto příspěvku jsou vytvořeny ve cvičném Power BI souboru Adventure Works DW 2020.pbix, který je volně dostupný ke stažení na internetu. Soubor s řešením je dostupný ke stažení níže pod tímto příspěvkem.

Začněme velmi jednoduchým příkladem. Funkce VALUES() vrací tabulku a můžeme tedy tuto funkci použít v Power BI pro vytvoření nové počítané tabulky.

Počítaná tabulka:

Barvy = VALUES('Product'[Color])

Při vytvoření nové počítané tabulky nepůsobí na funkci VALUES() žádný filtr. Výsledkem nové počítané tabulky je proto tabulka s jedním sloupcem se všemi barvami ze sloupce 'Product'[Color].

Výsledná tabulka obsahuje pouze deset řádků, i když v tabulce produktů 'Product' je uvedena barva u všech 397 produktů. Na výše uvedeném příkladu tak můžeme názorně vidět, že výsledkem funkce jsou pouze jedinečné hodnoty z použitého sloupce. V počítané tabulce si pro názornost můžeme vytvořit programově také filtr a podívat se, jak se chová funkce VALUES() v kontextu filtru. K vytvoření filtru můžeme použít funkci CALCULATETABLE().

Počítaná tabulka:

Barvy (modrá a červená) =

CALCULATETABLE

(

    VALUES('Product'[Color]),

    'Product'[Color] IN {"Blue", "Red"}

)

Funkce VALUES() je nyní vyhodnocena v kontextu filtru barev Blue a Red. Výsledkem je proto tabulka pouze se dvěma řádky, které odpovídají filtru, ve kterém je funkce VALUES() vyhodnocena.

Funkce VALUES() se obvykle nepoužívá tímto způsobem pro vytvoření počítané tabulky. Pokud používáme funkci VALUES() v definici počítaných tabulek, tak jako součást složitějších výpočtů, které zahrnují více vnořených funkcí nebo nové počítané sloupce. Výše uvedené ukázky tedy sloužili pouze pro znázornění chování funkce VALUES() bez přítomnosti filtru a s přítomností filtru. Nyní se přesuneme k použití funkce VALUES() v měřítku, což je obvyklejší použití této funkce. Při definici měřítka musíme vždy brát v úvahu, že výsledkem musí být jedna hodnota (skalární hodnota). Funkci VALUES() proto nemůžeme použít bez nějakého typu agregace přímo v měřítku, až na jednu výjimku popsanou níže v článku. Co ale můžeme je vložit funkci VALUES() do funkce COUNTROWS(), a nechat si vypsat počet barev, které jsou dostupné v aktuálním kontextu vyhodnocení.

Měřítko:

Počet barev = COUNTROWS(VALUES('Product'[Color]))

Nové měřítko [Počet barev]  vložíme do vizuálu karta a můžeme se podívat na výsledek.

Výsledkem je číslo 10. To znamená, že v aktuálním kontextu vyhodnocení je k dispozici všech deset barev, protože na výpočet nepůsobí žádný filtr. Pokud do reportu přidáme průřez s barvami produktů a vybereme pouze modrou a červenou barvu, výsledek bude vypadat následovně.

Nyní již na funkci VALUES() působí filtr, který je vytvořený pomocí průřezu v Power BI reportu. Jedná se o stejný filtr, který jsme si dříve vytvořili programově při definici počítané tabulky 'Barvy (modrá a červená)'. 

Měřítko [Počet barev] můžeme použít například pro znázornění počtu barev, ve kterých se prodávají produkty v jednotlivých kategoriích.

Nyní na funkci VALUES() působí filtr z každého řádku vizuálu tabulky.  Pokud se zaměříme například na první řádek vizuálu, tak při vyhodnocení měřítka [Počet barev] je v tomto řádku aktivní filtr, který obsahuje kategorii "Components". Celý model je proto v době vyhodnocení měřítka v tomto konkrétním řádku filtrovaný pouze na produkty, které patří do kategorie "Components". Výsledkem je počet barev, ve kterých se prodávají produkty v kategorii uvedené v aktuálním řádku tabulky. V řádku souhrnů již na měřítko nepůsobí žádný filtr, a výsledkem je proto počet všech barev.

Při pohledu na vizuál na obrázku výše často vyvstává otázka, zda je možné zobrazit si přímo v řádcích vizuálu také názvy jednotlivých barev. Zobrazit si jednotlivé barvy přímo v měřítku můžeme, pouze potřebujeme všechny barvy dostat do jednoho textového řetězce, protože výsledkem měřítka musí být jedna skalární hodnota.

Měřítko:

Názvy barev =

CONCATENATEX

(

    VALUES('Product'[Color]),

    'Product'[Color],

    ", "

)

K vytvoření jednoho textového řetězce je použita funkce CONCATENATEX(), která spojí všechny barvy dostupné v aktuálním kontextu vyhodnocení do jedné skalární hodnoty. Jako oddělovač je pak použita čárka a mezera ve třetím argumentu funkce CONCATENATEX().

Měřítko [Názvy barev] vrací všechny barvy dostupné v aktuálním kontextu vyhodnocení. Protože funkce VALUES() vrací tabulku, můžeme ji použít mimo jiné v prvním argumentu iteračních funkcí.

Funkce VALUES v iteračních funkcích

Než se dostaneme k použití funkce VALUES() v prvním argumentu iterační funkce, představme si situaci, kdy potřebujeme zjistit počet zákazníků, kteří měli alespoň jeden nákup ve vybraném roce. Takovýto požadavek může být splněn pomocí následujícího měřítka.

Měřítko:

Počet zákazníků = DISTINCTCOUNT(Sales[CustomerKey])

Měřítko [Počet zákazníků] můžeme vložit do vizuálu Tabulka, spolu s roky v řádcích.

Měřítko [Počet zákazníků] vrací v každém řádku počet zákazníků, kteří uskutečnili alespoň jeden nákup v daném roce. V roce 2017 nakoupilo alespoň jeden produkt 1202 zákazníků, v roce 2018 to bylo 2734 zákazníků, a tak dále. Zaměřme se ale na poslední řádek v tabulce, na řádek souhrnů. Číslo v řádku souhrnů neodpovídá součtu jednotlivých hodnot v řádcích tabulky. Na první pohled můžeme vidět, že pouze za roky 2019 a 2020 by součet byl vyšší, než je hodnota v řádku souhrnů. Číslo v řádku souhrnů není špatně, pouze musíme vědět, jak má být interpretováno. V řádku souhrnů vrací měřítko [Počet zákazníků] počet jedinečných zákazníků, kteří nakoupili alespoň jeden produkt v kterémkoliv roce. Pokud ale stejný zákazník nakupoval ve více letech, v řádku souhrnů je započítán pouze jednou. Protože mnoho zákazníků nakupovalo opakovaně v různých letech, je číslo v řádku souhrnu menší, než je součet jednotlivých hodnot v letech. Tento typ výpočtu se také označuje jako neaditivní. Vysvětlení výpočtu v řádku souhrnů může být sice srozumitelné, přesto může vyvstat požadavek, kdy v řádku souhrnů budeme potřebovat zobrazit součet jedinečných zákazníků za jednotlivé roky. Takovou hodnotu můžeme získat pomocí následujícího výpočtu.

Měřítko:

Počet zákazníků 2 =

SUMX

(

    VALUES('Date'[Rok]),

    [Počet zákazníků]

)

Nové měřítko si můžeme vložit do původního vizuálu, a porovnat si výsledek s původním výpočtem.

Pro pochopení výpočtu v měřítku [Počet zákazníků 2] si musíme být vědomi toho, že každé měřítko je vyhodnoceno v každém řádku vizuálu samostatně, a v každém řádku vizuálu působí na měřítko jiný filtr. V řádku s rokem 2017 je výsledkem prvního argumentu funkce SUMX() tabulka s jedním sloupcem a jedním řádkem, obsahujícím pouze rok 2017. Ve druhém řádku je výsledkem funkce VALUES() pouze rok 2018, atd. V řádku souhrnů již na měřítko nepůsobí žádný filtr, výsledkem funkce VALUES() jsou proto všechny roky. Pro každý rok je pak vyhodnoceno měřítko [Počet zákazníků] a tyto hodnoty z každého roku jsou následně sečteny funkcí SUMX().

V řádcích  s jednotlivými roky je proto výsledek stejný jako u původního výpočtu. V řádku souhrnů je pak výsledkem součet všech jedinečných zákazníků za jednotlivé roky, namísto počtu jedinečných zákazníků napříč roky, jak je tomu u původního výpočtu. Která hodnota v řádku souhrnů je ta správná záleží na požadavcích uživatelů reportu. Jako autoři reportu bychom však měli být schopni vytvořit oba typy výpočtu a měli bychom také být schopni interpretovat správně výsledky. Další rozdíl mezi těmito dvěma přístupy je, že měřítko [Počet zákazníků] můžeme použít v jakémkoliv kontextu v kterémkoliv vizuálu, zatímco měřítko [Počet zákazníků 2] je použitelné pouze v kontextu jednotlivých let, a s jinými hodnotami v řádcích tabulky by byla interpretace tohoto měřítka poměrně složitá.

Dalším typickým příkladem použití funkce VALUES() může být použití této funkce k sestavení filtrů ve funkci CALCULATE(), jak bude možné vidět v následujícím příkladu.

Funkce VALUES a obnovení odstraněného filtru

Začněme opět jednoduchým příkladem. V použitém Power BI souboru si vytvoříme dvě měřítka. První měřítko bude vracet sumu za prodané produkty v aktuálním kontextu vyhodnocení, druhé měřítko bude vracet sumu prodejů za předcházející měsíc.

Měřítka:

Prodeje = SUM(Sales[Sales Amount])

Prodeje předchozí měsíc =

CALCULATE

(

    [Prodeje],

    DATEADD('Date'[Date], -1, MONTH)

)

Obě měřítka si vložíme do vizuálu Matrix, spolu s roky a měsíci v řádcích.

Měřítko [Prodeje] vrací v každém řádku tabulky sumu za prodané produkty v aktuálním měsíci a roce. Měřítko [Prodeje předchozí měsíc] pak podle očekávání vrací sumu za prodané produkty v předcházejícím měsíci. Pokud se podíváme na hodnotu měřítka [Prodeje předchozí měsíc] v kterémkoliv měsíci, hodnota vždy přesně odpovídá výsledku měřítka [Prodeje] v předcházejícím měsíci na předcházejícím řádku. Situace se ale změní v případě, kdy do reportu přidáme průřez se dny v týdnu a vybereme v průřezu pouze dny od pondělí do pátku.

V takto vytvořeném reportu již hodnota měřítka [Prodeje předchozí měsíc] neodpovídá výsledkům měřítka [Prodeje] v přechozím měsíci. Problémem je že funkce DATEADD() je nyní vyhodnocena navíc také v kontextu filtru vybraných dnů v průřezu. Funkce DATEADD() tedy v řádcích s měsíci načte dny v aktuálním měsíci a roku, omezené pouze na konkrétní dny v týdnu vybrané v průřezu. Takto načtené dny pak přesune do předchozího měsíce. Rozdíl je pak mimo jiné dán tím, že kombinace dne v měsíci a konkrétního dne v týdnu v aktuálním měsíci obvykle naplatí pro měsíc předcházející. Jinak řečeno, pokud například první den v aktuálním měsíci připadá na pondělí, v předcházejícím měsíci mohl být první den v měsíci například neděle, v závislosti na počtu dnů v porovnávaných měsících. Řešení tohoto problému je relativně jednoduché a bude se skládat z následujících tří kroků.

1) Načtení dnů v týdnu, které jsou vybrány v průřezu.
2) Načtení všech dnů v předcházejícím měsíci, bez filtru konkrétních dnů v týdnu.
3) Použití tabulek z kroku 1 a 2 ve filtru funkce CALCULATE().

Samotný výpočet pak bude vypadat následovně.

Měřítko:

Prodeje předchozí měsíc 2 =

VAR VybraneDnyVTydnu = VALUES('Date'[Den])

VAR VsechnyDnyMinulyMesic =

    CALCULATETABLE

    (

        DATEADD('Date'[Date], -1, MONTH),

        REMOVEFILTERS('Date'[Den],'Date'[Den v týdnu])

    )

VAR Vysledek =

    CALCULATE

    (

        [Prodeje],

        VsechnyDnyMinulyMesic,

        VybraneDnyVTydnu

    )

RETURN

    Vysledek

Do první proměnné (VybraneDnyVTydnu) jsme uložili filtr aplikovaný na sloupec se dny v týdnu z průřezu. Ve druhé proměnné (VsechnyDnyMinulyMesic) jsou pak uloženy všechny dny v předchozím měsíci, bez filtru nastaveného v průřezu na dny v týdnu, který je odstraněn pomocí funkce REMOVEFILTERS(). Ve funkci REMOVEFILTERS() musíme odstranit jak filtr ze sloupce použitého v průřezu, tak filtr ze sloupce, který je použitý pro řazení hodnot ve sloupci použitém v průřezu. Tabulky uložené v prvních dvou proměnných následně použijeme společně ve filtru funkce CALCULATE() v proměnné (Vysledek).

Jak je možné vidět na obrázku výše, nová verze výpočtu již vrací správné výsledky. V tomto příkladu byla použita funkce VALUES() pro sestavení dříve odstraněného filtru, což je jeden z běžných způsobů použití funkce VALUES() v měřítku. 

Funkce VALUES s jedním sloupcem a jedním řádkem

V úvodu článku jsem se zmínil o jedné situaci, kdy funkci VALUES() můžeme používat také jako funkci vracející skalární hodnotu. Z dříve uvedených příkladu jíž víme, že funkce VALUES() je funkce vracející tabulku. Pokud ale tato tabulka obsahuje pouze jeden sloupec a jeden řádek, můžeme funkci VALUES() použít také jako funkci vracející skalární hodnotu. To znamená že v situaci kdy máme jistotu, že výsledkem funkce VALUES() bude pouze jedna hodnota, můžeme tuto funkci použít přímo v měřítku. Podívejme se například na následující definici měřítka.

Měřítko:

Vybraná barva (špatně) = VALUES('Product'[Color])

Pokud měřítko [Vybraná barva (špatně)] vložíme do některého z vizuálu v Power BI reportu, a v průřezu vybereme pouze jednu barvu, výsledkem měřítka bude název právě vybrané barvy.

Pokud máme jistotu, že výsledkem funkce VALUES() bude pouze jedna hodnota, můžeme tuto funkci použít jako skalární funkci. V této ukázce ale takovou jistotu nemáme, protože uživatel může vybrat v průřezu více nebo žádnou barvu. V takovém případě bude měřítko vracet chybu.

Pokud bychom chtěli mít jistotu, že výsledkem funkce VALUES() bude vždy pouze jedna hodnota, můžeme použít následující postup.

Měřítko:

Vybraná barva =

IF

(

    COUNTROWS(VALUES('Product'[Color])) = 1,

    VALUES('Product'[Color]),

    "Vybráno více barev"

)

Nyní v prvním argumentu funkce IF() ověřujeme, zda tabulka s barvami produktů obsahuje v aktuálním kontextu pouze jednu hodnotu, pokud ano, výsledkem bude vybraná barva, v opačném případě necháme vypsat alternativní výsledek.

Pokud vybere uživatel v průřezu více nebo žádnou barvu, měřítko [Vybraná barva] nebude vracet chybu, ale alternativní výsledek.

Měřítko [Vybraná barva] obsahuje před vypsáním výsledku ověření, zda v aktuálním kontextu bude vracet funkce VALUES() pouze jednu hodnotu. Jelikož je tato konstrukce v jazyku DAX poměrně častá, autoři DAX Enginu vytvořili funkci speciálně určenou pro tuto úlohu, kterou je funkce SELECTEDVALUE(). V současné době je proto možné přepsat měřítko [Vybraná barva] následujícím jednodušším způsobem, bez nutnosti ověřovat počet položek vrácených funkcí VALUES().

Měřítko:

Vybraná barva 2 = SELECTEDVALUE('Product'[Color], "Vybráno více barev")

Takto vytvořené měřítko bude vracet konkrétní barvu v situaci, kdy v aktuálním kontextu bude dostupná pouze jedna hodnota ve sloupci uvedeném v prvním argumentu této funkce. V opačném případě bude výsledek alternativní výsledek, v případě že je zadaný ve druhém argumentu. Pokud druhý argument funkce SELECTEDVALUE() nezadáme, je automaticky nastaven na hodnotu BLANK. Více informací o funkci SELECTEDVALUE(), včetně dalších příkladů použití této funkce, můžete najít v samostatném příspěvku.

Funkce VALUES a prázdné hodnoty BLANK

Funkce VALUES(), ať už s argumentem ve formě sloupce nebo tabulky, vrací také případné prázdné řádky. Prázdné řádky s hodnotami BLANK mohou být přirozenou součástí tabulky nebo sloupce. Prázdné řádky ale mohou vznikat také na pozadí modelu, a to v případě kdy pracujeme s tabulkami, mezi kterými je porušena referenční integrita*. Pokud nechceme, aby výsledkem funkce byly také prázdné řádky, které vznikají na pozadí z důvodu porušené referenční integrity, můžeme použít namísto funkce VALUES() funkci DISTINCT(). Ve standardně a správně vytvořeném datovém modelu bychom si ale měli vystačit s funkcí VALUES().

*Porušená referenční integrita mezi tabulkami je stav, kdy sloupec použitý pro vytvoření relace v tabulce na straně ONE neobsahuje všechny hodnoty, které jsou ve sloupci použitém pro vytvoření relace v tabulce na straně MANY. Příkladem může být tabulka prodejů, která obsahuje záznamy o prodaných produktech (MANY), a tabulka produktů, která obsahuje jedinečné záznamy o produktech (ONE). O porušené referenční integritě bychom mohli mluvit v situaci, kdy v tabulce prodejů (MANY) je záznam o prodaném produktu, který ovšem ještě nemá záznam v samotné tabulce produktů (ONE), například z důvodu rozdílných časů aktualizace těchto tabulek.

Shrnutí

Funkce VALUES() je jednou z nejdůležitějších funkcí v jazyku DAX. Jedná se o jednoduchou funkci, která má velmi širokou škálu použití. Klíčem k pochopení fungování funkce VALUES() je porozumění kontextu, ve kterém je tato funkce vyhodnocena. Pokud si nejsme jistí, jaký je výsledek funkce VALUES() v aktuálním kontextu, ve kterém je ve složitějších výpočtech použita, můžeme vložit funkci VALUES() do funkce CALCULATETABLE() a vytvořit si všechny filtry programově a zobrazit si tak výsledek funkce v konkrétním kontextu filtru. Praktické příklady, některé také s použitím funkce VALUES(), můžete najít na stránce DAX příklady.

Oficiální Microsoft dokumentace funkce VALUES:
https://learn.microsoft.com/cs-cz/dax/values-function-dax

Stáhnout soubor s řešením.
č. 64