Po toľkých rokoch, čo je PowerPivot a Power BI na trhu, niektorých ľudí ešte stále máta záhadná otázka: “Ako otočím šípku na prepojeniach medzi tabuľkami v PowerPivote/Power BI?”  Respektíve – “Ako otočiť smer prepojenia medzi tabuľkami v Power BI?”. Riešenie je jednoduché, a nebude to rýchly fix, ako sa to zvykne bežne riešiť, čiže v podobe obojsmerných prepojení. Tie nie je potrebné takmer nikdy zapínať, lebo po čase vyrobia viac problémov ako úžitku. Rýchla odpoveď je, že prepojenia medzi tabuľkami nejdú otočiť. Ukážeme si však, že to vôbec nevadí, a že to ide aj napriek tomu ľahko vyriešiť.

Medzi tabuľkami v dátovom modeli máte zvyčajne prepojenia, ako napríklad toto z nášho vzorového súboru PowerPivotu, resp. Power BI:

O čom hovorí smer prepojenia

Tieto dve tabuľky – Objednávky a Produkty, sú prepojené prepojením typu N:1, resp. Many-to-One, cez vyznačené stĺpce. Šípka na prepojení v strede určuje smer prepojenia, resp. smer prúdenia filtrov medzi tabuľkami v dátovom modeli v PowerPivote aj Power BI. Čiže ktorým smerom prúdia filtre z jednej tabuľky do druhej. V tomto prípade prúdia filtre v smere šípky, z tabuľky Produkty do tabuľky Objednávky. Čiže keď nastavíme filter nad tabuľkou Produkty, tak ten filter “prebuble” vďaka tomuto prepojeniu a jeho smeru aj do tabuľky Objednávky. Kde vyfiltruje príslušné riadky. Pričom filtrami sa rozumejú kontextové filtre, ktorých niektoré zdroje sú uvedené v tomto článku. Má to aj inú funkciu, ale tomu sa teraz nebudeme venovať.

Na prvý pohľad všetko v poriadku, takto čakáme, že to bude fungovať. Niekedy by ste však chceli otočiť smer prepojenia medzi týmito tabuľkami.

Skúste si napríklad spraviť takúto kontingenčku:

1. do oblasti hodnôt dáme merítko Obrat z tabuľky Objednávky,
2. do oblasti riadkov dáme stĺpce CalendarYear z tabuľky Čas.

Výsledná kontingenčka bude vyzerať takto:

Potiaľto všetko fajn. Teraz by sme však chceli vedieť, koľko produktov sa predalo v jednotlivých rokoch. Vytvoríme teda takéto merítko:

Počet produktov := COUNTROWS(Produkty)

Po dosadení do kontingenčky to nebude vyzerať zrovna vábne:

Problém je v tom, že každý riadok kontingenčky vyrába kontextové filtre. V tomto prípade filter nad konkrétnym rokom v stĺpci CalendarYear v tabuľke Čas. Tento filter prejde do tabuľky Objednávky, pretože máme vytvorené prepojenie z tabuľky Čas do tabuľky Objednávky so šípkou v tomto smere. Keď sa však z tabuľky Objednávky chceme dostať do tabuľky Produkty, tak tie síce prepojené sú, ale už v opačnom smere, aký by sme potrebovali – viď. obrázok vyššie.

Rýchle riešenie, pôvodný smer prepojenia

Išlo by to spraviť v tomto prípade cez funkciu DISTINCTCOUNT nad tabuľkou Objednávky a stĺpcom ProduktIDfixnute. Takéto merítko by to síce nevyriešilo, ale by to obišlo:

Počet produktov := DISTINCTCOUNT(‘Objednávky'[ProduktIDfixnute])

…a aj výsledok by bol správny:

Problém je však v tom, keby sme chceli ďalšie štatistiky pomocou tých stĺpcov v tabuľke Produkty. Napríklad keby sme chceli vedieť, koľko unikátnych farieb produktov sa predalo v daných obdobiach (stĺpec Color v tabuľke Produkty). Tam už veľmi rýchlo narazíme. Preto sa vrátime k pôvodnému vzorcu s COUNTROWS(Produkty).

Obojsmerné prepojenia

Ďalším rýchlym riešením by bolo zapnúť obojsmerné prepojenie medzi týmito dvoma tabuľkami. Pretože šípka na prepojení medzi tabuľkami nejde otočiť. Viete ju však vo väčšine prípadov zobojsmerniť – čiže aby išla v oboch smeroch. Avšak iba v Power BI, SSAS Tabulare 2016 a novšom, a Azure Analysis Services. Ak ste v excelovskom PowerPivote, tak máte smolu. Tam sa síce interne od Excelu 2016 nachádzajú, ale Microsoft tam tú funkcionalitu nesprístupnil.

Ak ste teda v Power BI, tak rozkliknite dvojklikom tú šípku na prepojení. Otvorí sa okno, kde vpravo dole, v roletke “Smer krížového filtrovania” (opäť super slovenský doslovný preklad), vyberte možnosť “Obe”, a stlačte tlačítko OK:

To nám nastaví prepojenie medzi tabuľkami ako obojsmerné:

To nám problém narýchlo vyrieši, a po návrate do kontingenčky výsledky budú vyzerať takto:

Skoro rovnako, ako v predchádzajúcom riešení, až na to, že počet v “celkovom súčte” nesedí. Vypisuje tam počet všetkých produktov – 606 – a nie len tých 158, ktoré sa v histórii predali, a ktoré by tam malo zobrazovať po správnosti.

Problémy s obojsmernými prepojeniami

Pri obojsmerných prepojeniach si potichu zarábate na ďalšie problémy. Obojsmerné prepojenia majú hneď viacero nevýhod:

• umožňujú vytvoriť duplicitné filtrovacie cesty v dátovom modeli a znejednoznačniť výpočty v ňom,
• zapnuté na “správnom” mieste znefunkčnia niektoré funkcie Time Intelligence,
• vytvoria bugy a nesprávne výsledky vo výpočtoch v niektorých funkciách, ktoré dokonca s tými prepojeniami ani nemusia súvisieť,
• spomalia výpočty v dátovom modeli,
• znemožnia prepojiť iné tabuľky, ktoré by pri neexistencii tohto obojsmerného prepojenia išli prepojiť,
• a mnoho ďalších problémov.

Vysvetlenie obojsmerných prepojení, bugov a náhradných riešení by zabralo na sériu článkov. Preto sa tým momentálne nebudeme zaoberať, a vrátime sa naspäť ku klasickým, jednosmerným prepojeniam. Obojsmerné prepojenia síce nie sú zlé, ak len vtedy, ak viete ako ich správne používať. Dovtedy sa im však snažte vyhnúť, ak ich nevyhnutne nepotrebujete. Napríklad tak, ako si to teraz ukážeme.

Správne riešenie

Nastavíme prepojenie medzi tými dvoma tabuľkami teda naspäť do pôvodného stavu, ako jednosmerné prepojenie:

Riešením je v tomto prípade použiť jazyk DAX a v ňom jednu z jeho najpokročilejších funkcionalít – rozšírené tabuľky. Ktoré učím na mojom Jedi Master kurze Power BI a PowerPivotu. Vysvetlenie tejto funkcionality by opäť zabralo dosť času, lebo ani zďaleka nejde o triviálnu funkcionalitu. Takže si to “vysvetlíme” expresne, tak aby ste to vedeli hneď použiť.

Keď meníte kontext výpočtu pomocou funkcie CALCULATE, tak v ňom viete použiť rôzne filtre, antifiltre, indický zápis filtrov, funkcie Time Intelligence či iné tabuľkové funkcie. Viete tam však okrem iného použiť aj fyzické tabuľky, čím v skutočnosti použijete funkcionalitu rozšírených tabuliek. A používa sa napríklad vtedy, keď chcete niečo vypočítať “v protismere” prepojenia.

V našom prípade upravíme naše merítko takto:

Počet produktov := CALCULATE(COUNTROWS(Produkty); ‘Objednávky’)

A ajhľa, začne to hneď dávať správne výsledky, dokonca aj v celkovom súčte:

V skratke, tento vzorec vypočíta počet produktov, v kontexte výpočtu obmedzenom na objednávky z pôvodného kontextu výpočtu. Alebo, ak sa vám o tom nechce rozmýšľať, tak si to zapamätajte tak, že ak chcete niečo vypočítať v protismere prepojenia tabuliek, tak zabaľte pôvodný vzorec do funkcie CALCULATE, a do druhého parametra zadajte názov tabuľky pred tým protismerom prepojenia. To na bežné prípady spoľahlivo zaberie.

Ďalšie výhody

Toto riešenie má však oproti všetkým riešeniam vyššie ešte aj ďalšiu výhodu. Umožní nám správne počítať aj ďalšie štatistiky pomocou tých stĺpcov v tabuľke Produkty. Bez dopadu na výkon, narozdiel od obojsmerných prepojení. Napríklad keby sme chceli vedieť, koľko unikátnych farieb produktov sa predalo v daných obdobiach (stĺpec Color v tabuľke Produkty), tak to bude tentokrát hračka:

Počet unikátnych farieb := CALCULATE(DISTINCTCOUNT(Produkty[Color]); ‘Objednávky’)

A aj výsledok poteší:

Ak by ste to riešili ako čistý DISTINCTCOUNT bez CALCULATE a s obojsmernými prepojeniami, tak by ste v celkovom súčte dostali 10. Čo by bolo znova nesprávne.

Takto sa to teda robí správne. Veľa ľudí nepoteším, keď im poviem, že smer prepojenia nejde otočiť, resp. ani tá šípka na prepojeniach nejde otočiť. Dá sa zobojsmerniť, ale to je len rýchly fix, a každý veterán vás od toho bude odradzovať. Dôvody máte vyššie. Namiesto toho môžeme v tomto prípade použiť DAX a jeho funkcionalitu rozšírených tabuliek, a počítať naše ukazovatele v protismere prepojenia. Odmenou nám za to bude rýchly výkon dátového modelu, a kopec ušetrených starostí do budúcnosti. Pretože takto to robia naozajstní profíci 😉