Keď objavíte čaro merítok v jazyku DAX, tak po nejakom čase si všimnete nečakanú vec – a to že merítka vám vo veľa prípadoch začnú “pridávať” nové riadky do tabuliek a kontingenčiek v reporte, ktoré tam predtým neboli. A tým pádom ich neželane zväčšujú. A je jedno, či ste v PowerPivote, alebo v Power BI. Ak neviete, prečo sa to deje, tak je veľmi frustrujúce prísť na koreň problému. Pretože to, čo vyzerá ako pridávanie nových riadkov do tabuľky, je v skutočnosti spôsobené niečim iným.

Ako merítka zväčšujú počet riadkov v kontingenčke

Na demonštráciu opäť využijeme náš vzorový súbor PowerPivotu, resp. Power BI. Vytvoríme si kontingenčku, kde:

1. v oblasti hodnôt bude merítko Obrat z tabuľky Objednávky,
2. v oblasti riadkov budú stĺpce CalendarYear a MonthName z tabuľky Čas.

Keď kontingenčku rozbalíte na úroveň mesiacov, tak posledný rok bude vyzerať takto:

Sú tam mesiace od januára do júla, plus december. Lebo len v tých mesiacoch roka 2004 bol nejaký obrat.

A teraz si pridáme klasiku – kumulatívny obrat od začiatku roka, resp. obrat YTD. Bude mať takýto jednoduchý vzorec:

Obrat od začiatku roka := CALCULATE([Obrat]; DATESYTD(‘Čas'[DateKey]))

Nezabudnite si ešte označiť tabuľku Čas ako časovú tabuľku, aby vám správne zafungovala funkcia DATESYTD. Po dosadení merítka do kontingenčky to bude vyzerať takto:

Obrat od začiatku roka sa síce vypočítal správne, ale vypočítal sa aj pre tie mesiace, ktoré nemajú žiaden obrat. A najmä, zrazu sa tam objavili mesiace z roku 2004, ktoré tam predtým neboli. A nepríjemne zväčšujú kontingenčku.

Čím to je?

Je to jednoducho tým, že merítka sa vždy počítajú voči všetkým položkám stĺpcov z číselníkov, ktoré boli použité v oblasti riadkov či v stĺpcov kontingenčky. Aj keď pre tieto položky neexistujú údaje v hlavnej tabuľke.

V našom prípade sme použili v kontingenčke 2 stĺpce v oblasti riadkov – stĺpce CalendarYear a MonthName. Stĺpec CalendarYear obsahuje hodnoty/položky 2001 až 2004, a stĺpec MonthName obsahuje hodnoty/položky January až December. Keď sa začína počítať kontingenčka, tak si to viete predstaviť tak, že najprv sa vytvoria osi kontingečky, a potom sa spočíta oblasť hodnôt kontingenčky – čie zosumované polia a merítka. Osi kontingenčky sa vytvárajú tak, že zoberú všetky kombinácie všetkých položiek zo všetkých stĺpcoch použitých na osiach kontingenčky. Čiže v našom prípade sa spravia všetky kombinácie rokov 2001 – 2004 so všetkými mesiacmi January – December. Čo je 4 * 12 = 48 riadkov. A ak sú použité stĺpce z jednej tabuľky, tak sa z týchto kombinácií odstránia tie, ktoré neexistujú v tej tabuľke. Čiže v našom prípade sa nespraví os kontingenčky ako 2001-January až 2004-December, ale iba ako 2001-June až 2004-December. Pretože v tabuľke Čas existujú iba tieto kombinácie (po slovensky – sú tam iba dátumy od 30. júna 2001 do 31. decembra 2004). Tejto funkcionalite sa hovorí autoexist.

Keď sú vytvorené osi kontingenčky, tak sa vykoná výpočet oblasti hodnôt kontingenčky. Čiže sa spustí výpočet merítok a zosumarizovaných stĺpcov, pre každú z kombinácií na osiach kontingenčky. V našom prípade sú to merítka “Obrat” a “Obrat od začiatku roka”.

Po dokončení výpočtu nastáva posledná fáza spracovania kontingenčky – skryjú sa prázdne riadky (a v prípade použitia oblasti stĺpcov kontingenčky, aj prázdne stĺpce). Možno to poznáte z excelovskej kontingenčky ako riadky bez údajov. Čiže z kontinenčky sa skryjú tie riadky, na ktorých sa v oblasti hodnôt nič nevypočítalo, prípadne kde celé riadky majú v oblasti hodnôt iba prázdne hodnoty. A vo výsledku zostanú zobrazené iba tie riadky kontingenčky, na ktorých bola v oblasti hodnôt vypočítaná aspoň jedna hodnota.

Toto správanie je veľmi dobre vidno aj na našom príklade. Keď sme tam mali iba Obrat, tak sa nezobrazovali mesiace August – November z roku 2004, pretože tam nebol vypočítaný žiaden Obrat. Akonáhle sme tam ale dali merítko “Obrat od začiatku roka”, tak sa toto vypočítalo pre všetky mesiace v roku 2004, a preto to zobrazilo aj predtým skryté riadky. A správanie, ktoré vyzeralo predtým tak, že merítko “pridalo” ďalšie riadky do kontingenčky, resp. že merítka zväčšujú kontingenčku, je týmto objasnené. Nepridalo ich tam. Tie riadky tam boli aj predtým, len tentokrát ich už kontingenčka neskryla.

Ako zobraziť prázdne riadky, resp. riadky bez údajov

Na overenie si odstráňte z kontingenčky to merítko “Obrat od začiatku roka”. Znova vám to skryje mesiace na konci roka 2004. Ak by ste ich chceli zobraziť, tak je potrebné si to zobrazovanie zapnúť. Ak pracujete v Exceli, tak je postup takýto: kliknite hocikde dovnútra kontingenčky pravým tlačítkom myši, a vyberte položku “Možnosti kontingenčnej tabuľky…”:

Potom prejdite na záložku “Zobrazenie”, a tam zafajknite políčko “Zobraziť položky bez údajov v riadkoch”, prípadne aj “…v stĺpcoch” (ak to chcete zapnúť aj nad oblasťou stĺpcov v kontingenčke), a stlačte OK:

A ak pracujete v Power BI, tak je postup takýto: prejdite do stredového panelu – do nastavení vizuálu/tabuľky/kontingenčky – a tam kliknite na šípku vedľa prvého poľa použitého v kontingenčke (v našom prípade CalendarYear). Potom vyberte v menu možnosť “Zobraziť položky bez údajov”:

Následne sa nám zobrazia v kontingenčke aj predtým skryté riadky, ktoré tam predtým merítko “Obrat od začiatku roka” akože pridávalo:

Ako nezobrazovať, resp. skryť riadky v kontingenčke

Záhada je teda vyriešená, a zostáva už len poskrývať naspäť tie riadky, ktoré pred pridaním merítka “Obrat od začiatku roka” boli skryté. To spravíme tak, že využijeme vlastnosti kontingenčky, popísané vyššie, a vzorec pre merítko “Obrat od začiatku roka” upravíme tak, že sa nebude počítať (resp. bude vracať prázdnu hodnotu) pre tie riadky, ktoré chceme kontingenčke skryť. Túto techniku som už popisoval v staršom článku, a je to zároveň aj najľahšie riešenie. Jednoducho vzorec rozšírime tak, že “ak sa niečo vypočítalo v merítku Obrat, tak počítaj aj pôvodný vzorec pre toto merítko, inak vráť prázdnu hodnotu“. Predtým si však nezabudnite vypnúť zobrazovanie prázdnych riadkov v kontingenčke, ak ste si to medzičasom zapli (vypína sa rovnakým spôsobom ako ste to zapli). Inak to samozrejme nezafunguje.

Upravenú vzorec pre merítko “Obrat od začiatku roka” bude teda takýto:

Obrat od začiatku roka :=
IF (
    [Obrat] <> BLANK ();
    CALCULATE ( [Obrat]; DATESYTD ( 'Čas'[DateKey] ) );
    BLANK ()
)

A po jeho dosadení do kontingenčky bude výsledok vyzerať už presne tak, ako sme chceli – bez mesiacov, za ktoré nie je Obrat:

Dá sa to však aj inak. V niektorých prípadoch, ak potrebujete toto spraviť s 2 merítkami, ktoré sa vzájomne odkazujú na seba, by ste týmto spôsobom vytvorili cyklickú závislosť v dátovom modeli, a PowerPivot, resp. Power BI by vám to nedovolili spraviť. Typický prípad je “zobraz merítko X, iba ak je merítko Y neprázdne, a zobraz merítko Y, iba ak je merítko X neprázdne”.

To viete vyriešiť buď tak, že spravíte tretie merítko s vhodnou podmienkou, a nastavíte filtrovanie v kontingenčke podľa neho. Alebo, ak sa vám nechce robiť ďalšie merítko, tak to ide spraviť aj jednoduchou fintou – otestovaním počtu riadkov v kontexte výpočtu, ktoré sú v tabuľke, z ktorej sa počíta dané testované merítko. V našom prípade sa Obrat počíta z tabuľky Objednávky, a preto sa merítko “Obrat od začiatku roka” dá napísať aj takto:

Obrat od začiatku roka :=
IF (
    COUNTROWS('Objednávky') > 0;
    CALCULATE ( [Obrat]; DATESYTD ( 'Čas'[DateKey] ) );
    BLANK ()
)

A prípadne aj na X ďalších spôsobov. Kreativita v jazyku DAX nepozná hraníc, a je to len na vás, ktorý spôsob vám bude vyhovovať v danom prípade viac.

A merítka už zväčšujú kontingenčky

Každopádne, takto viete dôjsť na záhadu, prečo niektoré merítka zväčšujú počet riadkov v tabuľke. Nezväčšujú ich, len sa počítajú pre všetky kombinácie položiek z osí kontingenčky, a kontingenčka potom skrýva prázdne riadky. Preto ak viete, ako na to, tak sa viete podľa toho aj zariadiť, a vhodnou úpravou vzorca to dostať naspäť do požadovaného stavu. A nie je to žiadna mágia – stačí iba vedieť, ako to funguje, a riešenie potom nezaberie zvyčajne viac ako pár sekúnd 🙂