V tomto příspěvku si na jednoduchých příkladech vysvětlíme, proč je důležité mít pro správné fungování Time intelligence funkcí označenou kalendářní tabulku jako "tabulku kalendářních dat". Než se ale pustíme do vysvětlení daného problému, ukážeme si, jakým způsobem můžeme v Power BI označit datumovou tabulku jako "tabulku kalendářních dat".
K tomuto tématu je k dispozici také video:
Kalendářní tabulka je v metadatech modelu označena jako tabulka kalendářních dat vždy, když je sloupec použitý pro vytvoření relace ve formátu DATE anebo ve formátu DATETIME. V takovémto případě sice nemusíme ručně označovat datumovou tabulku jako tabulku kalendářních dat, nicméně i tak je to doporučovaná praxe.
Pokud používáme pro vytvoření relací s datumovou tabulkou jiné sloupce než sloupce ve formátu DATE nebo DATETIME, označení datumové tabulky jako tabulky kalendářních dat je pro správné chování Time intelligence funkcí nezbytné.
Kalendářní tabulku můžeme označit jako tabulku kalendářních dat například tak, že klikneme na danou tabulku pravým tlačítkem myši a v kontextové nabídce klikneme na možnost "Označit jako tabulku kalendářních dat".
Možnost "Označit jako tabulku kalendářních dat" se bude zobrazovat v kontextové nabídce u všech tabulek, ve kterých máme jeden nebo více sloupců ve formátu DATE nebo DATETIME.
Pokud klikneme na možnost "Označit jako tabulku kalendářních dat", otevře se dialogové okno, kde nejdříve zapneme přepínací tlačítko a následně vybereme sloupec z kalendářní tabulky. V rozevíracím seznamu pro výběr sloupce se budou nabízet pouze sloupce ve formátu DATE nebo DATETIME. Vybraný sloupec pak musí obsahovat jedinečné hodnoty bez duplicit, a dále v tomto sloupci nesmí být mezi jednotlivými datumy mezery. Pokud jsou tyto podmínky splněny, po výběru sloupce uvidíme v dialogovém okně pod rozevíracím seznamem potvrzení úspěšného ověření zvoleného sloupce.
Zvolený sloupec z rozevíracího seznamu je pak sloupec, který budeme používat v Time intelligence funkcích. U tohoto sloupce pak navíc uvidíme v seznamu sloupců speciální značku.
Jakmile máme kalendářní tabulku označenou jako tabulku kalendářních dat, můžeme začít používat zjednodušenou syntaxi při psaní Time intelligence výpočtů.
Celý princip Time intelligence funkcí vychází z předpokladu, že každý atribut z kalendářní tabulky se skládá s konkrétních dnů. Pokud tedy pracujeme se standardním Gregoriánským kalendářem, tak například víme, z jakých konkrétních dnů se skládá každý měsíc. Dále například, pokud použijeme všechny dny z prvních tří měsíců roku, máme první čtvrtletí, pokud použijeme všechny dny v roce, máme celý rok, a tak dále. Každý atribut z kalendářní tabulky tedy můžeme poskládat z jednotlivých dnů.
Všechny Time intelligence funkce proto při jejich vyhodnocení pracují s konkrétními dny, přesněji řečeno se sloupcem, který jsme vybrali v dialogovém okně při označování tabulky jako tabulky kalendářních dat, a ostatní sloupce ignorují. To znamená, že kdykoliv ve filtru funkce CALCULATE() použijeme tento speciálně označený sloupec, na pozadí dojde k odstranění všech filtrů z tabulky označené jako tabulka kalendářních dat. Tento fakt zjednodušuje psaní Time intelligence výpočtů v jazyku DAX, jak si vysvětlíme na následujících jednoduchých příkladech.
Time intelligence výpočty s tabulkou označenou jako tabulka kalendářních dat
V této části příspěvku budeme pracovat se souborem, ve kterém je tabulka 'Date' označena jako tabulka kalendářních dat. V tomto souboru je navíc vytvořeno měřítko [Prodeje], které jednoduše vrací sumu za prodeje produktů v aktuálním kontextu vyhodnocení.
Měřítko:
Měřítko [Prodeje] si můžeme vložit do vizuálu matice, spolu s roky a s měsíci v řádcích.
Pro demonstraci toho, jak se chovají Time intelligence funkce v případě, kdy pracujeme s tabulkou označenou jako tabulka kalendářních dat, si můžeme vytvořit dvě jednoduchá měřítka.
První měřítko bude vracet prodeje za stejné období v předchozím roce.
Měřítko:
Funkce SAMEPERIODLASTYEAR() má pouze jeden argument, kterým je sloupec ve formátu DATE nebo DATETIME. Obecně se předpokládá, že ve všech Time intelligence funkcích budeme používat sloupec, který jsme vybrali v dialogovém okně při označování datumové tabulky jako tabulky kalendářních dat.
Funkce SAMEPERIODLASTYER() pak načte všechny dny v aktuálním kontextu vyhodnocení, a vrátí dny za stejné období v předchozím roce. Tyto dny za předchozí rok jsou přidány do filtru před vyhodnocením měřítka [Prodeje] v prvním argumentu funkce CALCULATE().
Měřítko [Prodeje (PY)] pak podle očekávání vrací v každé buňce použitého vizuálu hodnotu prodejů za stejné období v předchozím roce. Tento typ výpočtu se často používá pro porovnávání výkonnosti v aktuálním období s výkonností v předchozím roce, a to buď v absolutních nebo relativních hodnotách, jak je podrobněji popsáno v samostatném příspěvku pod tímto odkazem.
Pro nás je ale nyní důležité pochopit, jakým způsobem je celý výpočet v měřítku [Prodeje (PY)] vyhodnocen. Pokud je čtenář dobře seznámen s kontextem filtru a s tím, jak funguje funkce CALCULATE(), mohl by dojít k závěru, že na výpočtu v měřítku [Prodeje (PY)] je něco zvláštního.
Pro vysvětlení je vždy dobré zaměřit se na jednu konkrétní buňku vizuálu. Uvažujme například zvýrazněnou buňku za měsíc červen 2018. V tomto řádku vizuálu působí na měřítko vnější filtr, který je nastaven na sloupec 'Date'[Měsíc] a hodnotu "červenec" a na sloupec 'Date'[Rok] a hodnotu 2018. V kontextu těchto filtrů je vyhodnocena funkce SAMEPERIODLASTYEAR(), která načte všechny dny za červenec 2018, a vrátí všechny dny stejné období v předchozím roce, tedy dny v červenci 2017. Tyto dny následně přidá do filtru před vyhodnocením měřítka [Prodeje].
Pokud by výše popsané platilo, tak by výsledný filtr vytvořený funkcí CALCULATE() v měřítku [Prodeje (PY)] obsahoval měsíc červenec a rok 2018 (vnější filtry z řádku vizuálu), a všechny dny z měsíce červenec 2017 (funkce SAMEPERIODLASTYEAR()). Tyto filtry by se navzájem vylučovaly a výsledkem měřítka by měla být prázdná hodnota BLANK.
Přesně tento výsledek bychom dostali, pokud bychom neoznačili datumovou tabulku jako tabulku kalendářních dat. Pokud ale datumovou tabulku označíme jako tabulku kalendářních dat, tak kdykoliv kdy použijeme ve filtru funkce CALCULATE() náš klíčový sloupec 'Date'[Date], automaticky dojde k odstranění všech vnějších filtrů z celé tabulky 'Date'. Výsledný filtr vytvořený funkcí CALCULATE() v řádku červenec 2018 proto obsahuje pouze dny za červenec 2017, které vrací funkce SAMEPERIODLASTYEAR(), a všechny ostatní filtry z tabulky 'Date' jsou automaticky odstraněny.
Důležité je si uvědomit, že tato vlastnost, tedy odstranění filtrů z tabulky 'Date', se vztahuje výhradně ke sloupci 'Date'[Date], který jsme vybrali v dialogovém okně při označování datumové tabulky jako tabulky kalendářních dat. Proto v Time intelligence funkcích používáme tento sloupec. Stejného efektu ale dosáhneme, i když tento sloupec použijeme například pro sestavení logického filtru ve funkci CALCULATE().
Uvažujme například následující měřítko, které bude vracet kumulativní prodeje od prvního dne s prodeji po aktuální den.
Měřítko:
I tento výpočet bude vracet správné výsledky, bez nutnosti odstraňovat vnější filtry z řádků vizuálu.
Pro úplnost si v následující části ukážeme, jak se se měřítka [Prodeje (PY)] a [Prodeje (RT)] budou chovat, pokud datumová tabulka nebude označena jako tabulka kalendářních dat.
Time intelligence výpočty s tabulkou která není označená jako tabulka kalendářních dat
V této části příspěvku budeme pracovat s tabulkou 'Date' která není označená jako tabulka kalendářních dat. Aby nedošlo ani k automatickému označení tabulky 'Date' jako tabulky kalendářních dat, nesmíme pro vytvoření relací s tabulku 'Date' používat sloupce ve formátu DATE nebo DATETIME. Relace mezi tabulkou 'Sales' a tabulkou 'Date' je proto vytvořena na základě sloupců 'Sales'[OrderDateKey] a 'Date'[DateKey], které jsou ve formátu INTEGER.
Pokud v takto vytvořeném modelu vytvoříme stejný vizuál matice s roky a měsíci, do kterého vložíme měřítka [Prodeje (PY)] a [Prodeje (RT)], výsledek bude vypadat následovně.
Měřítko [Prodeje (PY)] vrací v každém řádku vizuálu prázdnou hodnotu BLANK. Pro vysvětlení se opět zaměříme na jeden konkrétní řádek vizuálu. Uvažujme například měsíc listopad 2018. V tomto řádku tvoří vnější filtr měsíc listopad a rok 2018. Ve funkci CALCULATE() pak používáme ve filtru funkci SAMEPERIODLASTYEAR(), která vrací všechny dny v listopadu 2017. Protože se vnější filtry vylučují s filtrem vytvořeným ve funkci CALCULATE(), výsledkem měřítka je vždy prázdná hodnota BLANK.
U měřítka [Prodeje (RT)] je pak situace jiná, protože toto měřítko vrací v každém řádku vizuálu stejnou hodnotu jako samotné měřítko [Prodeje]. Pokud se opět zaměříme například na listopad 2018, tak vnější filtry jsou dány aktuálním řádkem vizuálu, tedy opět rok 2018 a měsíc listopad, a vnitřní filtr ve funkci CALCULATE() vrací všechny dny od posledního dne v měsíci listopad až po první den, který máme v tabulce 'Date'. Průnikem těchto dvou filtrů jsou všechny dny v listopadu 2018, a proto vrací měřítko [Prodeje (RT)] stejné hodnoty jako samotné měřítko [Prodeje].
Pokud tedy pracujeme s datumovou tabulkou, která není označena jako tabulka kalendářních dat, ať už z jakéhokoliv důvodu, musíme pro správné chování Time intelligence výpočtů programově odstraňovat vnější filtry z datumové tabulky, například pomocí funkce REMOVEFILTERS().
Měřítko:
Stejným způsobem můžeme odstranit vnější filtry také u výpočtu kumulativních prodejů.
Měřítko:
Nyní už budou obě nové verze měřítek vracet správné výsledky, a to i v situaci, kdy pracujeme s datumovou tabulkou, která není označena jako tabulka kalendářních dat.
Pokud pracujeme s datumovou tabulkou která není označena jako tabulka kalendářních dat, nedochází k automatickému odstranění filtrů z této tabulky, a pro správné fungování Time intelligence výpočtů musíme tyto filtry odstranit sami, například pomocí funkce REMOVEFILTERS().
Shrnutí
Označit datumovou tabulku jako tabulku kalendářních dat můžeme dvěma způsoby. První způsob může být na první pohled skrytý, protože pouhým vytvořením relace na základě sloupců ve formátu DATE nebo DATETIME dojde automaticky k označení datumové tabulky jako tabulky kalendářních dat. I přesto se ale doporučuje označit datumovou tabulku jako tabulku kalendářních dat vlastnoručně, protože při tomto označení dojde k validaci sloupce ve formátu DATE nebo DATETIME, který následně používáme v Time intelligence výpočtech. Kdykoliv pak tento sloupec použijeme ve filtru funkce CALCULATE(), dojde automaticky k odstranění všech filtrů z kalendářní tabulky. Díky tomu jsou Time intelligence výpočty jednodušší.
Označovat datumovou tabulku jako tabulku kalendářních dat samozřejmě není nutné a v určitých situacích to ani není možné. Pracovat můžeme například s modelem, který obsahuje agregovaná data za týdny nebo měsíce. V těchto typech modelů pak obvykle nemá smysl používat vestavěné Time intelligence funkce, protože tyto funkce fungují spolehlivě pouze se sloupcem ve formátu DATE nebo DATETIME, to znamená s daty na úrovni jednotlivých dnů. Pokud pracujeme s vlastním kalendářem v jiné granularitě, a nebo pokud tvoříme vlastní Time intelligence výpočty pomocí základních DAX funkcí, například z důvodu optimalizace, odstraňujeme filtry z datumové tabulky programově, například pomocí funkce REMOVEFILTERS().
Stáhnout soubor s řešením 1.
Stáhnout soubor s řešením 2.
č. 113
Komentáře
Okomentovat