Ako na chybu “Keď stĺpec dátumu pochádza z tabuľky týkajúcej sa jednosmerného alebo obojsmerného vzťahu, funkcia DATEADD očakáva súvislý výber”

Pred pár týždňami som sem písal článok o tom, ako sa popasovať s klasickou chybou pri funkciách DATEADD a SAMEPERIODLASTYEAR. V jednom konkrétnom reporte som však narazil na ďalší prípad, kedy tieto funkcie odmietajú fungovať, aj keď to ošetríte ako som to písal v predchádzajúcom článku. Preto sa pozrieme, kedy presne sa to vyskytuje, a ako sa s tým vysporiadať.

Tentokrát sa vám toto môže stať iba v Power BI a v SSAS Tabulare 2016 a novších. Preto začneme s našim vzorovým súborom Power BI.

Spravíme si takúto jednoduchú tabuľku, kde na riadkoch budú roky (stĺpec CalendarYear z tabuľky Čas), a vedľa bude zobrazený obrat za daný rok (merítko Obrat v tabuľke Objednávky). Pod ňou si spravíme 2 slicery – jeden z rokov (stĺpec CalendarYear z tabuľky Čas), a druhý z krajín (stĺpec Krajina v tabuľke Regióny). Bude to vyzerať takto:

Potom by sme chceli vidieť v tej tabuľke hore ku každému roku aj obrat pred rokom, aby sme vedeli vypočítať medziročnú zmenu. Spravíme teda merítko “Obrat pred rokom”, s pomocou funkcií Time Intelligence, a ošetríme ho pomocou predchádzajúceho článku s chybou vo funkcii DATEADD:

Obrat pred rokom = IF(HASONEVALUE(‘Čas'[CalendarYear]); CALCULATE([Obrat]; DATEADD(‘Čas'[DateKey]; -1; YEAR)))

Keď ho dosadíme do tabuľky, tak všetko, vrátane slicerov, bude fungovať správne:

Teraz by sme chceli, aby slicer s krajinami odfiltroval nielen tabuľku nad ním, ale aj slicer vedľa neho – napr. ak pre danú krajinu neboli predaje za všetky roky z číselníka. Preto podľa článku o kaskádovitých sliceroch nastavíme obojsmerné prepojenie medzi tabuľkami Objednávky a Čas:

Keď však teraz kliknete do slicera s krajinami, aby ste si odfiltrovali štatistiku napr. len na Kanadu, tak nám to nezobrazí štatistiku, ale iba chybu vizuálu:

…po rozkliknutí ktorej dostanete takúto exotickú chybovú hlášku:

Prípadne ešte môžete v mierne odlišných prípadoch naraziť na inú hlášku:

Konkrétne: Keď stĺpec dátumu nie je jedinečný, obsahuje medzery alebo časovú časť, funkcia DATEADD očakáva súvislý výber.

A teraz babo raď… Funkciou HASONEVALUE sme si ošetrili, že vo výbere je vždy len jeden rok, ale aj tak to padá. Čím to je?

Najprv som pátral veľmi dlho po internete, lebo sa mi nechcelo rozmýšľať viac ako 5 sekúnd. A všade len zmienka o tej klasickej chybe s DATEADD, ale nie s touto. Po dlhšej dobe som našiel niečo v tom zmysle, že vraj niekto z MS povedal, že pri použití obojsmerných filtrov medzi časovou tabuľkou a zvyškom dátového modelu, nie sú podporované niektoré funkcie Time Intelligence. Ktoré to ale sú, už nie je napísané ani v dokumentácii. Takže neviem či je to pravda alebo nie, ale zjavne to nefunguje.

Technicky je problém v tom, že keď dáte filter napr. na rok 2002 nad časovou tabuľkou, tak ten sa propaguje aj na tabuľku objednávok a vyfiltruje objednávky pre ten rok. Následne sa funkcia DATEADD pokúša nájsť k tým objednávkam časový výrez, ktorý potom posunie o rok dozadu, ale predtým ešte testuje, či sú dátumy v časovej tabuľke prislúchajúce dátumom v hlavnej tabuľke súvislé. A boli by, keby ste mali vždy v danom výseku objednávky za každý jeden deň medzi min. a max. dátumom, ale to sa nedá zabezpečiť vždy. Sú na to síce triky, ale potom iné štatistiky nie sú úplne presné…

Jediným riešením, ktoré všetci po internete píšu, je prepnúť to obojsmerné prepojenie naspäť na jednosmerné. To vyrieši problém okamžite. Čo ale vypne aj tie kaskádovité slicery. A to nechceme. Takže budeme musieť na to nájsť náhradné riešenie.

Druhým riešením je zlúčiť do jednej tabuľky objednávky aj časovú tabuľku, ale to je prašť jak uhoď…

Riešením je použiť univerzálny výpočtový vzorec, ktorý popisujem vo svojej knihe o Power BI. A potom si nájsť minimálny a maximálny dátum, ktorý je použitý v aktuálnom kontexte výpočtu, a vypočítať obrat medzi nimi, posunutý o rok. Výsledný vzorec bude takýto:

Obrat pred rokom2 =
VAR casOd = DATEADD(FIRSTDATE(‘Objednávky'[SaleDateKey]); -1; YEAR)
VAR casDo = DATEADD(LASTDATE(‘Objednávky'[SaleDateKey]); -1; YEAR)
RETURN
CALCULATE([Obrat]; FILTER(CALCULATETABLE(‘Objednávky’; ALL(‘Čas’)); ‘Objednávky'[SaleDateKey] >=  casOd && ‘Objednávky'[SaleDateKey] <= casDo))

…alebo vypeknené cez DAX Formatter takto:

Tento vzorec robí to, že najprv z kontextu výpočtu odstráni všetky časové filtre (ale ponechá ostatné), a potom si dá vyfiltrovať všetky objednávky medzi prvým a posledným dátumom v pôvodnom kontexte. Tie dátumy predtým ale ešte posunie o rok dozadu. Tento posun teraz zafunguje, pretože posúvame už iba po 1 hodnote, a tie sú vždy “súvislé”. Potom voči nim dá vypočítať Obrat. Čím by sme mali dostať úplne rovnaký obrat pred rokom ako na začiatku. S tým rozdielom, že bude fungovať teraz aj s tým obojsmerným prepojením medzi tabuľkami, aj keď v tom sliceri dole zaklikneme Kanadu, či inú krajinu:

Logicky by sme mali dostať rovnaký obrat ako na začiatku, a pre prvé riadky to aj funguje, ale pre ďalšie už nie. V čom je problém?

Ideme si to zdebugovať cez ďalšie merítko. Tentokrát si dáme vyrobiť merítko, ktoré iba spočíta koncovú hranicu:

test = DATEADD(LASTDATE(‘Objednávky'[SaleDateKey]); -1; YEAR)

Keď ho pridáte do reportu, tak dostanete takýto výsledok:

Tam vidíte, že to koncovú hranicu nevypočítalo správne. Ani nie kvôli tomu, že by tie funkcie fungovali nesprávne. Ale skôr kvôli tomu, že to počítame z dátumového stĺpca v tabuľke Objednávky, a tam neexistujú všetky objednávky pre celý rok pre každú kombináciu filtrov. Asi tak ako aj v reálnych dátach. Napr. pre Kanadu to dalo ako posledný dátum pre rok 2004 posunutý o rok dozadu ako 31.7.2003. Je to kvôli tomu, že posledná objednávka za Kanadu v roku 2004 bola 31.7.2004, a to posunuté o rok dozadu je to, čo sme dostali.

Preto treba ešte ošetriť aj výpočet tých premenných, kde máme dátumy od-do. Pre každú úroveň zvlášť. My si to teraz ukážeme pre roky. Techniku na detekciu úrovní a rôzne výpočty v závislosti na nich učím v pokročilom kurze Power BI, aj v pokročilom kurze PowerPivotu.

Upravené testovacie merítko pre úroveň rokov teda bude takéto:

test = ENDOFYEAR(DATEADD(LASTDATE(‘Objednávky'[SaleDateKey]); -1; YEAR))

…a koniec intervalu nám už teraz bude počítať správne:

Celý trik bol v tom, že sme daný posunutý dátum ešte posunuli na koniec príslušného roka, funkciou ENDOFYEAR. Podobne to spravíte aj so začiatkom intervalu, cez funkciu STARTOFYEAR.

Upravený finálny vzorec pre úroveň rokov bude teda takýto:

Obrat pred rokom2 =
VAR casOd = STARTOFYEAR(DATEADD(FIRSTDATE(‘Objednávky'[SaleDateKey]); -1; YEAR))
VAR casDo = ENDOFYEAR(DATEADD(LASTDATE(‘Objednávky'[SaleDateKey]); -1; YEAR))
RETURN
CALCULATE([Obrat]; FILTER(CALCULATETABLE(‘Objednávky’; ALL(‘Čas’)); [SaleDateKey] >= casOd && ‘Objednávky'[SaleDateKey] <= casDo))

…a vypeknené cez DAX Formatter:

A výsledok teraz bude už naozaj správny:

Je to síce trochu komplikované riešenie, najmä ak to ešte máte ošetriť osobitne pre jednotlivé časové úrovne. Ale to je už len zopár IF-ov navyše. A potom vám to bude fungovať nielen na tých 4 úrovniach, ktoré podporuje funkcia DATEADD – čiže rok, štvrťrok, mesiac a deň – ale aj na všetkých ostatných, ktoré si tam dáte – napr. týždeň.

Na druhej strane, takto aspoň vidíte, čo všetko musia robiť funkcie Time Intelligence na pozadí, aby zafungovali, a aj tak občas nefungujú. Takže si to v takýchto prípadoch musíme nakódiť sami.

V ideálnom prípade by sme ešte tento vzorec mali ošetriť cez HASONEVALUE rovnako ako v predošlom článku. To však už zvládnete aj sami 🙂

A s ktorou chybou v jazyku DAX bankujete vy? 😀