Funkce WINDOW() je funkce vracející tabulku, v rámci které se můžeme pohybovat před nebo za aktuální pozici v aktuálním řádku vizuálu nebo v aktuálním řádku tabulky, a to v libovolném rozsahu. Rozsah tabulky, kterou vrací funkce WINDOW(), může být určen pomocí dvou absolutních odkazů, dvou relativních odkazů nebo pomocí kombinace relativního a absolutního odkazu pro určení prvního a posledního řádku výsledné tabulky.
Funkce WINDOW() patří do stejně nazvané kategorie – do kategorie WINDOW funkcí. V této kategorii najdeme také funkce INDEX(), OFFSET(), RANK() a ROWNUMBER().
V první části tohoto příspěvku jsou popsány jednotlivé argumenty funkce WINDOW(). Druhá část příspěvku obsahuje jednoduché příklady s cílem vysvětlit, jak funkce WINDOW() funguje.
Všechny příklady v tomto příspěvku jsou vytvořeny ve cvičném Power BI souboru Adventure Works DW 2020.pbix. Soubor s řešením je dostupný ke stažení níže pod tímto příspěvkem.
Syntaxe a popis argumentů DAX funkce WINDOW
Počeštěná syntaxe funkce WINDOW() vypadá následovně.
Syntaxe funkce WINDOW:
Ačkoliv má funkce WINDOW() relativně velké množství argumentů, její používání není při dodržovaní určitých pravidel příliš složité. Ze syntaxe uvedené výše lze vyčíst, že většina argumentů je nepovinná. Nepovinné argumenty můžeme při zadávání dalších argumentů přeskakovat. To znamená že při přeskakování některého z argumentů nemusíme namísto vynechaného argumentu vkládat čárku. Některé z těchto nepovinných argumentů jsou ale nepovinné pouze při zadání jiných argumentů, jak si vysvětlíme později. Dále, ve vybraných argumentech funkce WINDOW() můžeme používat pouze speciální DAX funkce, které mají vlastní syntaxi a vlastní pravidla použití. Z výše popsaných důvodů si každý argument zaslouží krátkou vysvětlivku.
Od (originálně from) – povinný argument, který určuje pozici prvního řádku výsledné tabulky. V argumentu <od> můžeme zadat hodnotu nebo jakýkoliv výraz vracející skalární hodnotu. Způsob použití argumentu <od> závisí na následujícím argumentu <od typ>. V argumentu <od typ> můžeme určit, zda má být pozice prvního řádku výsledné tabulky určena relativně k aktuálnímu řádku, který je vybrán na základě vnějšího univerzálního kontextu, nebo absolutně v rámci celé tabulky zadané v argumentu <tabulka>.
Od typ (from_type) – volitelný argument, který určuje způsob použití předchozího argumentu <od>. Zadat můžeme ABS pro absolutní odkaz nebo REL pro relativní odkaz. Při vynechání tohoto argumentu je výchozí nastavení REL.
- ABS – Pokud bude číslo zadané v argumentu <od> kladné, například číslo 1, jde o odkaz na první řádek v seřazené tabulce zadané v argumentu <tabulka>. Číslo 2 bude odkazovat na druhý řádek v této tabulce, a tak dále. Záporné číslo v argumentu <od> pak bude odkazovat na řádky od konce seřazené tabulky zadané v argumentu <tabulka>. Například číslo -1 bude odkazovat na poslední řádek, číslo -2 na předposlední řádek, a tak dále.
- REL – Pokud bude číslo zadané v argumentu <od> kladné, například číslo 1, půjde o odkaz na řádek za aktuálním řádkem v seřazené tabulce zadané v argumentu <tabulka>. Číslo 2 pak bude odkazovat na řádek v pozici dva řádky za aktuálním řádkem, a tak dále. Aktuální řádek je pak určen na základě takzvaného univerzálního kontextu (kontext řádku, kontext filtru nebo oba), ve kterém je celá funkce vyhodnocena. Záporné číslo v argumentu <od> bude odkazovat na řádky před aktuálním řádkem. Například číslo -1 bude odkazovat na předchozí řádek, číslo -2 pak bude odkaz na řádek dva řádky před aktuálním řádkem, a tak dále.
Do (to) – povinný argument, který určuje poslední řádek výsledné tabulky. V argumentu <do> můžeme zadat hodnotu nebo jakýkoliv výraz vracející skalární hodnotu. Způsob použití argumentu <do> závisí na následujícím argumentu <do typ>. V argumentu <do typ> můžeme určit, zda má být pozice posledního řádku určena relativně k aktuálnímu řádku, který je určen na základě vnějšího univerzálního kontextu, nebo absolutně v rámci celé tabulky zadané v argumentu <tabulka>.
Do typ (to_type) – volitelný argument. Stejně jako v případě argumentu <od typ> můžeme vybrat buď ABS pro absolutní odkaz nebo REL pro relativní odkaz. Při vynechání tohoto argumentu je výchozí nastavení REL.
Tabulka (relation) – tabulka nebo funkce vracející tabulku, která bude seřazena a následně použita k vytvoření výsledné tabulky. Rozsah výsledné tabulky bude určen na základě argumentů <od> a <do> a na způsobu jejich použití, který je definován v argumentech <od typ> a <do typ>. Argument <tabulka> je nepovinný v případě, kdy je zadán argument <řazení> a současně sloupce v argumentu <řazení> a případně v argumentu <rozdělit podle> musí pocházet ze stejné tabulky v modelu. V takovémto případě je na pozadí místo tohoto argumentu vytvořena automaticky tabulka pomocí funkce ALLSELECTED(), se sloupci zadanými v argumentu <řazení> a případně v argumentu <rozdělit podle>, pokud je použit.
Řazení (orderBy) – volitelný argument vyhrazený pro speciální funkci ORDERBY(). Pokud je tento argument vynechán, tabulka v argumentu <tabulka> musí být zadána. Argumenty ve funkci ORDERBY() se zadávají v páru sloupec nebo výraz určený pro řazení, a nepovinně (pouze v případě zadání jednoho výrazu k řazení) způsob řazení. Pokud není zadán způsob řazení ve funkci ORDERBY(), defaultní nastavení je ASC – vzestupné. Ve funkci ORDERBY() můžeme určit způsob řazení tabulky v argumentu <tabulka> pomocí hodnot ve sloupcích z této tabulky nebo řazení na základě hodnot, které jsou výsledkem výrazu, tedy i měřítka. Řazení může být sestupné (DESC, 0 nebo FALSE) nebo vzestupné (ASC, 1 nebo TRUE). Ve druhém argumentu funkce ORDERBY() můžeme specifikovat také zařazení prázdných BLANK hodnot. Například výraz DESC BLANK FIRST seřadí hodnoty sestupně, ale prázdné hodnoty budou vždy v pořadí na začátku.
Prázdné hodnoty (blanks) – volitelný argument, jehož vyplněním můžeme specifikovat, jak bude zacházeno při řazení s prázdnými hodnotami BLANK. Toto nastavení platí pro všechny řazené hodnoty, kromě těch, pro které je případně určen vlastní způsob zacházení s prázdnými hodnotami v argumentu <řazení>, viz předchozí odstavec. V době psaní tohoto příspěvku můžeme zadat pouze DEFAULT – pro textové hodnoty jsou hodnoty BLANK vždy na prvním místě, pro numerické hodnoty jsou BLANK hodnoty vždy mezi nulou a zápornými čísly. Další volby pro zacházení s prázdnými hodnotami mohou být přidány v budoucnu.
Rozdělit podle (partitionBy) – volitelný argument vyhrazený pro funkci PARTITIONBY(), pomocí které můžeme rozdělit tabulku zadanou v argumentu <tabulka> na dílčí tabulky. Pokud je tabulka rozdělena na dílčí tabulky podle sloupce zadaného ve funkci PARTITIONBY(), tak dílčí tabulka která bude použita v aktuálním kontextu bude ta, jejíž hodnota ve sloupci určeném pro rozdělení odpovídá hodnotě ze stejného sloupce ve vnějším univerzálním kontextu, ve kterém je celá funkce vyhodnocena.
Spárovat s (matchBy) – volitelný argument vyhrazený pro funkci MATCHBY(). Ve funkci MATCHBY() můžeme specifikovat sloupce, které budou použity pro určení aktuálního řádku, ve kterém je použitá WINDOW funkce vyhodnocena. Tento argument je užitečný zejména při použití v počítaných sloupcích, kde se díky specifikaci jednoho nebo více sloupců z tabulky, ve které je funkce WINDOW() použita, můžeme vyhnout problémům s cyklickou závislostí. Použití funkce MATCHBY() může také v některých typech výpočtů pomoci při optimalizaci, a to díky menšímu počtu sloupců které budou použity pro určení aktuálního řádku v tabulce <tabulka> v porovnání s vnějším univerzálním kontextem.
Ačkoliv má funkce WINDOW() velké množství argumentů, její používání nebude ve většině případů příliš složité. Současně, některé argumenty jsou k dispozici pouze pro řešení velmi specifických situací.
Následující příklady pak mohou posloužit pro základní pochopení toho, jak funkce WINDOW() funguje a pro jaké typy výpočtů se může hodit.
Příklad použití funkce WINDOW v Power BI
Funkce WINDOW() vrací tabulku, která může obsahovat jeden nebo více sloupců a jeden nebo více řádků, v závislosti na zadaných argumentech a kontextu vyhodnocení. To znamená že tuto funkci můžeme používat v DAX výpočtech všude tam, kde můžeme používat funkce vracející tabulky. Funkce WINDOW() se bude často používat pro sestavení filtrů ve funkci CALCULATE(), a dále v prvním argumentu některých iteračních agregačních funkcí, například ve funkcích AVERAGEX(), MAXX() nebo SUMX(). Jak už ale bylo uvedeno výše, funkci WINDOW() můžeme používat kdekoliv kde je očekávána tabulka nebo funkce vracející tabulku, a to bez jakýchkoliv omezení.
Měřítka, která budou zobrazena v následující části příspěvku nemají příliš velký analytický význam a jsou vytvořena zejména za účelem vysvětlení chování funkce WINDOW().
V příkladech v tomto příspěvku budeme pracovat s jednoduchým měřítkem [Počet prodaných kusů], které má následující definici.
Měřítko:
Nové měřítko si vložíme do vizuálu Tabulka, spolu se sloupcem 'Date'[Měsíc rok] z kalendářní tabulky.
Měřítko:
Klíčem k pochopení toho jak funkce WINDOW() funguje je tabulka uvedená v pátém argumentu této funkce. V měřítku [Počet prodaných kusů (-2 REL, 0 REL)] je tato tabulka vytvořena pomocí funkce ALL() se sloupcem 'Date'[Měsíc rok], který je v řádcích použitého vizuálu, a se sloupcem 'Date'[Měsíc rok číslo], který je použitý pro řazení hodnot ve sloupci 'Date'[Měsíc rok]. Výsledkem této tabulky tak budou všechny platné kombinace hodnot ze dvou použitých sloupců.
Tato tabulka se dvěma sloupci pak bude seřazena podle sloupce 'Date'[Měsíc rok číslo], a to od prvního po poslední měsíc, díky funkci ORDERBY() v šestém argumentu funkce WINDOW(). V takto seřazené tabulce pak funkce WINDOW() v každém řádku vizuálu určí, který měsíc odpovídá aktuálnímu měsíci, tedy měsíci v aktuálním řádku vizuálu Tabulka, ve kterém je celý výpočet vyhodnocen. Jakmile je určen aktuálním měsíc, vybere funkce WINDOW() pouze tři řádky z tabulky zadané v pátém argumentu. Výsledné tři řádky pak budou v rozsahu od měsíce o dvě pozice před aktuálním měsícem až po aktuální měsíc, včetně. Tyto tři měsíce pak budou použity jako filtr ve funkci CALCULATE() před vyhodnocením měřítka [Počet prodaných kusů] v prvním argumentu. Nové měřítko si můžeme vložit do původního vizuálu s graficky si znázornit, které měsíce jsou výsledkem funkce WINDOW() v jednom konkrétním řádku vizuálu, řekněme třeba v řádku s měsícem květen 2018.
Drobnou úpravou měřítka [Počet prodaných kusů (-2 REL, 0 REL)] můžeme rozšířit období také o měsíce za aktuálním měsícem. Následující měřítko tak bude vracet počet prodaných kusů za pět měsíců, a to za aktuální měsíc, dva předchozí měsíce a dva následující měsíce.
Měřítko:
Jedinou změnou oproti původnímu výpočtu je třetí argument funkce WINDOW(), kde nyní v argumentu <do> odkazujeme na druhý měsíc za aktuálním měsícem.
Relativní odkazy můžeme ve funkci WINDOW() kombinovat s absolutními odkazy. Pokud bychom například chtěli vytvořit kumulativní součet hodnot od prvního po poslední měsíc, stačí upravit předchozí výpočet tak, aby první řádek výsledné tabulky odkazoval vždy na první měsíc absolutně, a poslední řádek výsledné tabulky na aktuální řádek. Aktuální řádek se pak bude měnit podle toho, ve kterém řádku vizuálu Tabulka bude funkce WINDOW() vyhodnocena.
Měřítko:
Nové měřítko si můžeme vložit do původního vizuálu a zobrazit si výsledky.
Měřítko [Počet prodaných kusů (1 ABS, 0 REL)] vrací v prvním řádku vizuálu počet prodaných kusů pouze za měsíc červenec 2017. Ve druhém řádku pak měřítko vrací počet prodaných kusů za měsíc červenec 2017 a srpen 2017. Ve třetím řádku jde o počet prodaných kusů za první tři měsíce, a tak dále.
První řádek ve funkci WINDOW() je tedy určen absolutně, a jde vždy o první měsíc v zobrazeném vizuálu. Poslední řádek ve funkci WINDOW() je určen relativně, a jedná se vždy o aktuální měsíc v aktuálním řádku vizuálu Tabulka.
Rozsah výsledné tabulky může být určen také pomocí dvou absolutních odkazů. Následující měřítko bude vracet počet prodaných kusů za všechny měsíce.
Měřítko:
Funkce WINDOW() bude nyní v každém řádku vizuálu vracet všechny měsíce. První odkaz bude vždy hledat první řádek tabulky, druhý odkaz bude vždy hledat poslední řádek tabulky zadané v pátém argumentu funkce WINDOW(). Výsledkem měřítka [Počet prodaných kusů (1 ABS, -1 ABS)] proto bude počet prodaných kusů za všechny měsíce, bez ohledu na to ve kterém řádku je vizuálu Tabulka je měřítko vyhodnoceno.
Výpočet průměru a výpočet klouzavého průměru pomocí funkce WINDOW
V této části příspěvku budeme pracovat s vizuálem Matice, ve kterém budou v řádcích roky a čtvrtletí z kalendářní tabulky a v hodnotách dříve vytvořené měřítko [Počet prodaných kusů].
Měřítko:
Funkce WINDOW() nyní obsahuje v pátém argumentu tabulku se všemi roky a čtvrtletími. Oba odkazy na tuto tabulku jsou nyní absolutní, první odkazuje na první řádek tabulky, druhý na poslední řádek tabulky. Pro výpočet ale není použita vždy celá tabulka. Tabulka v pátém argumentu funkce WINDOW() je rozdělena na dílčí tabulky podle sloupce 'Date'[Rok]. Pokud je výpočet vyhodnocen v roce 2017, výsledná tabulka obsahuje pouze řádky s rokem 2017 a se všemi čtvrtletími. Pokud je výpočet vyhodnocen v roce 2018, výsledná tabulka obsahuje řádky s rokem 2018 a se všemi čtvrtletními, a tak dále. Tato tabulka je následně použita v prvním argumentu funkce AVERAGEX(), v rámci které je vyhodnoceno měřítko [Počet prodaných kusů]. Výsledkem je proto průměrný čtvrtletní počet prodaných kusů v každém roce.
Měřítko:
Pro výpočet klouzavého průměru jsou ve funkci WINDOW() použity relativní odkazy, protože pro každé čtvrtletí musí výsledná tabulka obsahovat jiné řádky, vždy odvozené od aktuálního čtvrtletí. Nové měřítko si můžeme vložit do původního vizuálu a zobrazit si výsledek.
Kdykoliv použijeme ve funkci WINDOW() alespoň jeden relativní odkaz, funkce WINDOW() se snaží určit aktuální řádek v tabulce zadané v pátém argumentu této funkce, a to na základě vnějšího univerzálního kontextu, ve kterém je celá funkce vyhodnocena. Pokud jsme ve vizuálu na úrovni konkrétního čtvrtletí, určit aktuální řádek není problém, jedná se o řádek s aktuálním čtvrtletím a s aktuálním rokem. V mezisoučtech s roky ale není možné určit jeden konkrétní aktuální řádek v tabulce zadané v argumentu <tabulka>, protože v řádcích s mezisoučty pro roky není jedno konkrétní čtvrtletí, ale pouze jeden rok. Funkce WINDOW() v takové situaci použije všechny dostupné řádky pro daný rok, to znamená tabulku se čtyřmi řádky, jeden řádek pro každé čtvrtletí. Pro každé jedno čtvrtletí pak aplikuje stejný postup – vrátí tabulku se čtvrtletími od řádku tři čtvrtletí před aktuálním čtvrtletím až po aktuální čtvrtletí. Výsledné tabulky, jednu pro každé čtvrtletí v daném roce, pak spojí a vrátí jedinečné hodnoty ze všech načtených hodnot. Výsledkem funkce WINDOW() v řádku s rokem 2019 je v tomto příkladě tabulka se sedmi řádky. Tabulka začíná druhým čtvrtletím roku 2018 a končí čtvrtým čtvrtletím roku 2019. Tato tabulka je pak použita ve funkci AVERAGEX() pro výpočet průměru za rok 2019. Má takovýto výpočet nějaký význam? Určitě nemá a proto bychom ho neměli ani zobrazovat.
Následující druhá verze měřítka [Počet prodaných kusů (průměr za poslední 4 čtvrtletí)] obsahuje v prvním argumentu funkce IF() logickou podmínku, ve které před vyhodnocením hlavního výpočtu ověřujeme, zda je měřítko vyhodnoceno v kontextu jednoho čtvrtletí. Pokud není měřítko vyhodnoceno v kontextu konkrétního čtvrtletí, výpočet nedává smysl a nebudeme ho zobrazovat.
Měřítko:
Pokud si nové měřítko vložíme do vizuálu, výsledek bude vypadat následovně.
Pokud se tedy chceme vyhnout složitě interpretovatelným výsledkům při použití funkce WINDOW(), měli bychom tuto funkci používat pouze v kontextu, na základě kterého je možné jednoznačně určit jeden aktuální řádek v tabulce zadané v pátém argumentu této funkce. V opačném případě bude ve většině příkladů lepší nahradit výsledek měřítka hodnotou BLANK, tak jako v předchozím příkladu. Toto pravidlo platí zejména pokud ve funkci WINDOW() používáme relativní odkazy.
Shrnutí
Funkce WINDOW() je pokročilá funkce, pomocí které se můžeme pohybovat před nebo za aktuální řádek v tabulce nebo ve vizuálech, a to v libovolném rozsahu. Všechny funkce patřící do kategorie WINDOW funkcí jsou primárně vytvořeny za účelem tvorby takzvaných vizuálních výpočtů, tedy výpočtů, které jsou založeny na již zobrazených hodnotách ve vizuálech. Tento fakt nám ale nebrání v používání WINDOW funkcí libovolným způsobem podle našeho vlastního uvážení. Pozor si ovšem musíme dát na situace, kdy pracujeme s relativními odkazy a funkce WINDOW() je současně vyhodnocena v kontextu, ve kterém není možné na základě vnějšího univerzálního kontextu (kontext řádku, kontext filtru nebo oba) určit aktuální řádek v tabulce zadané v pátém argumentu. V takovém případě mohou být výsledky funkce WINDOW() složitě interpretovatelné, a proto je často lepší variantou ošetřit celý výpočet tak, aby se výsledky zobrazovaly pouze v kontextu, pro který je výpočet primárně určen.
Oficiální Microsoft dokumentace funkce WINDOW:
https://learn.microsoft.com/cs-cz/dax/window-function-dax
Komentáře
Okomentovat