Dotazy v jazyku DAX - funkce EVALUATE

Úvodní obrázek
Jazyk DAX obvykle používáme k rozšíření modelu o vlastní výpočty přes měřítka, počítané sloupce nebo počítané tabulky. Velkou výhodou jazyka DAX je jeho univerzálnost. Jakmile se naučíme používat jazyk DAX jako programovací jazyk, měli bychom být relativně snadno schopni používat tento jazyk také pro psaní dotazů. Při psaní DAX dotazů můžeme používat stejné funkce jako při psaní měřítek nebo počítaných sloupců, pouze s tím rozdílem, že výsledkem dotazu musí být vždy tabulka, která je vyhodnocena za klíčovým slovem EVALUATE. DAX dotazy můžeme používat k načtení dat z Tabulárního modelu ve stále více nástrojích, včetně Excelu. V ukázkách v tomto příspěvku je pro psaní DAX dotazů použité DAX Studio, připojené na model ve cvičném Power BI souboru Adventure Works DW 2020.pbix.

Dotazy v DAX - příkaz EVALUATE

Příkaz EVALUATE má následující syntaxi s jediným povinným argumentem, kterým je tabulka nebo funkce vracející tabulku.

Syntaxe EVALUATE:

/*
Volitelně definice měřítek,
počítaných sloupců, tabulek a proměnných
*/
[DEFINE {  MEASURE <NázevTabulky>[<Název>] = <Výraz> }
        {  COLUMN <NázevTabulky>[<Název>] = <Výraz> }
        {  TABLE <NázevTabulky> = <Výraz> }
        {  VAR <Název> = <Výraz>}]
       
/*Dotaz*/
EVALUATE <Tabulka>

/*Volitelně ovlivnění výsledku dotazu*/
[ORDER BY {<Výraz> [{ASC | DESC}]}[, …]
[START AT {<Výraz>|<Parametr>} [, …]]]

Nejjednodušší DAX dotaz pak může vypadat následovně.

DAX dotaz:

EVALUATE
'Product'

Výsledkem je tabulka 'Product' se všemi sloupci a všemi řádky tak, jak je nahraná v použitém modelu.

Dotazy v jazyku DAX - funkce EVALUATE 2

Jak již bylo uvedeno výše, při psaní DAX dotazů musí být výsledkem dotazu tabulka. To ale neznamená, že si nemůžeme nechat vypsat výsledek měřítka. 

Výsledkem DAX dotazu musí být tabulka

Nejjednodušší možností, jak vypsat výsledek měřítka samostatně v rámci DAX dotazu, je použít takzvaný konstruktor tabulky. Konstruktor tabulky se tvoří pomocí složených závorek. Následující dotaz vypíše výsledek měřítka [Prodeje] v tabulce s jedním řádkem a jedním sloupcem.

DAX dotaz:

EVALUATE
{
    [Prodeje]
}

Výsledkem je tabulka s jedním řádkem a jedním sloupcem, který je automaticky pojmenovaný názvem Value.

Dotazy v jazyku DAX - funkce EVALUATE 3

Předchozí dotaz vracel sumu za prodané produkty bez jakéhokoliv filtru, který by na měřítko působil. Pokud bychom chtěli zobrazit výsledek měřítka [Prodeje] v kontextu například jednotlivých kategorií produktů, můžeme použít následující DAX dotaz.

DAX dotaz:

EVALUATE
ADDCOLUMNS
(
    VALUES('Product'[Color]),
    "Prodeje",[Prodeje]
)
ORDER BY [Prodeje] DESC

Funkce ADDCOLUMNS() přidá k tabulce s jedním sloupcem, který obsahuje jednotlivé barvy produktů, sloupec se sumou prodejů za produkty v konkrétní barvě. Výsledek dotazu je pak seřazen sestupně podle hodnoty měřítka [Prodeje].

Dotazy v jazyku DAX - funkce EVALUATE 4

Jak bylo možné vidět na předcházejícím příkladu, pomocí DAX dotazu si můžeme relativně jednoduše nasimulovat, jak bude měřítko vyhodnoceno v určitém kontextu.

Definice objektů v rámci DAX dotazu

V DAX dotazech můžeme v části DEFINE vytvořit měřítka, počítané sloupce, tabulky nebo proměnně. Všechny objekty definované v rámci DAX dotazu jsou dostupné pouze v době jeho vyhodnocení. V následujícím DAX dotazu je například v části DEFINE vytvořené měřítko a proměnná. Proměnná definovaná v části DEFINE může být použita kdekoliv v dotazu, bez nutnosti použít klíčové slovo RETURN. Měřítko definované v části DEFINE musí mít před názvem samotného měřítka, který se uvádí v hranatých závorkách, uveden název některé z existujících tabulek.

DAX dotaz:

DEFINE
MEASURE 'Měřítka'[Náklady] = SUM('Sales'[Total Product Cost])
VAR vProdejeVnejsi = [Prodeje]

EVALUATE
ADDCOLUMNS
(
    VALUES('Product'[Color]),
    "mProdeje", [Prodeje],                
    "mNáklady", [Náklady],                
    "vProdejeVnejsi", vProdejeVnejsi,    
    "vProdejeVnitrni",            
    VAR vProdejeVnitrni = [Prodeje]      
    RETURN vProdejeVnitrni
)

Na obrázku níže je zobrazen výsledek předcházejícího dotazu. Zaujmout může rozdíl mezi vyhodnocením měřítka a proměnné vytvořené v části DEFINE. Zatímco měřítko je vyhodnoceno v kontextu, ve kterém je použito, proměnná je vyhodnocena v kontextu, ve kterém je definována. Dále je na výsledku předcházejícího dotazu možné vidět rozdíl mezi použitím proměnné definované uvnitř dotazu a proměnné vytvořené v části DEFINE, tedy vně dotazu.

Dotazy v jazyku DAX - funkce EVALUATE 5

Pokud definujeme v části DEFINE měřítko se stejným názvem, jako je již dříve vytvořené měřítko v modelu, v dotazu bude použita varianta měřítka vytvořená v rámci dotazu. V následujícím dotazu tak bude originální měřítko [Prodeje], které je součástí modelu, přepsáno měřítkem vytvořeným v rámci dotazu.

DAX dotaz:

DEFINE
MEASURE 'Měřítka'[Náklady] = SUM('Sales'[Total Product Cost])
MEASURE 'Měřítka'[Prodeje] = 700

EVALUATE
ADDCOLUMNS
(
    VALUES('Product'[Color]),
    "Prodeje", [Prodeje],
    "Náklady", [Náklady]  
)

Výsledkem měřítka [Prodeje] je konstanta s hodnotou 700. Díky této vlastnosti tak můžeme v DAX dotazech snadno ladit výpočty v měřítku a nemusíme přepisovat originální měřítko v modelu, dokud nemáme novou variantu výpočtu odladěnou.

Dotazy v jazyku DAX - funkce EVALUATE 6

Jak je možné vidět na předchozím příkladu, měřítko definované v rámci DAX dotazu má přednost před měřítkem se stejným názvem, které je součástí modelu. Pokud by ovšem bylo měřítko [Prodeje] použito uvnitř jiných měřítek, v rámci těchto měřítek by byla použita originální definice měřítka [Prodeje], jak je definována v rámci modelu. Jinak řečeno, definice měřítka v rámci DAX dotazu přepíše v době tohoto dotazu originální měřítko pouze pokud je toto měřítko voláno přímo v dotazu, a ne nepřímo v rámci jiných měřítek.

Relativně novou funkcí je možnost definování počítaného sloupce v dotazech. Počítaný sloupec vytvořený v části DEFINE se po dobu DAX dotazu chová jako kterýkoliv jiný sloupec v tabulce, pro kterou je vytvořen. Velkou výhodou této funkcionality je, že si můžeme relativně snadno pomocí DAX Studia změřit výkon výpočtu určeného pro počítaný sloupec. V následujícím dotazu je v části DEFINE vytvořen počítaný sloupec v tabulce 'Product', který obsahuje pořadí produktů podle jejich prodejů.

DAX dotaz:

DEFINE COLUMN 'Product'[Pořadí podle prodejů] = RANKX('Product', [Prodeje])
EVALUATE
TOPN
(
    7,  
    SELECTCOLUMNS
    (
        ADDCOLUMNS
        (
            'Product',
            "Prodeje", [Prodeje]
        ),
        "ID", 'Product'[ProductKey],
        "Název", 'Product'[Product],
        "Kategorie", 'Product'[Category],
        "Prodeje", [Prodeje],
        "Pořadí", 'Product'[Pořadí podle prodejů]
    ),
    [Pořadí],
    ASC
)
ORDER BY [Pořadí]

Výsledkem dotazu je tabulka se sedmi nejlepšími produkty, podle sumy za prodeje těchto produktů.

Dotazy v jazyku DAX - funkce EVALUATE 7

Výsledkem DAX dotazů je vždy tabulka. To může svádět k vytvoření DAX dotazu a po jeho odladění k použití tohoto dotazu pro vytvoření počítané tabulky v modelu. Počítaná tabulka v modelu je ale výsledkem výrazu, a ne dotazu. Proto, pokud chceme vytvořit na základě DAX dotazu počítanou tabulku, musíme z DAX dotazu odebrat funkci EVALUEATE, a použít pouze funkci vracející tabulku, bez části DEFINE a bez dalších částí příkazu EVALUATE, jako je například řazení výsledků dotazu.

Shrnutí

Psaní DAX dotazů vyžaduje určitou praxi a schopnost rozlišit, které funkce vracejí tabulky a které skalární hodnoty. Při psaní DAX dotazů ale nejsme omezeni používáním některých specifických funkcí. Používat  můžeme všechny dostupné funkce, stejně jako v počítaném sloupci, v měřítku nebo v počítané tabulce. Pouze musíme vždy myslet na to, že výsledkem dotazu musí být tabulka. Proto, pokud chceme získat pomocí dotazu skalární hodnotu, například výsledek měřítka, musíme použít funkci vracející tabulku nebo takzvaný konstruktor tabulky.

Stáhnout soubory s řešením.
č. 52

Komentáře