Keď ste niekedy skúšali použiť funkciu ALL v Power BI na zrušenie filtra nad jedným zo stĺpcov, tak vás pravdepodobne prekvapilo, že to niekedy funguje, a niekedy nie. Na väčšine stĺpcov táto funkcia proste funguje, ale pri niektorých ako keby bola zakliata, a neruší filter tak ako by mala. Spôsobuje to nesprávne fungovanie funkcie ALL v Power BI. Ideme sa teda pozrieť na to, ako to je, a hlavne aké je na to náhradné riešenie.

Riešenie si ukážeme na našom vzorovom súbore PowerPivotu, a môžete si ho vyskúšať aj na vzorovom súbore Power BI – oba majú rovnakú štruktúru.

Vyskladajme si teda najprv takúto kontingenčku z PowerPivotu v Exceli:

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

Výsledná kontingenčka bude zobrazovať obrat po rokoch a mesiacoch:

Teraz by sme chceli vypočítať, koľkými percentami sa podieľa obrat v danom mesiaci na celoročnom obrate. Podobne ako v článku o antifiltroch napíšeme merítko pomocou funkcie ALL, kde zrušíme filter nad stĺpcom ‘Čas'[MonthName], a naformátujeme ho ako percentá:

% obratu za rok := [Obrat] / CALCULATE([Obrat]; ALL(‘Čas'[MonthName]))

…a potom ho pridáme do kontingenčky, kde to samozrejme zobrazí správny výsledok:

Keď ale skúsime spraviť to isté v Power BI Desktope, či už nad vzorovým súborom Power BI, alebo naimportovaním tohto súboru do Power BI, tak dostaneme takýto výsledok:

Čo je samozrejme nesprávny výsledok. Keď pozrieme do dokumentácie k funkcii ALL, tak sa tam nič nespomína o tom, prečo to v Exceli funguje správne a v Power BI nie. Koniec koncov, Power BI používa rovnaký PowerPivot ako Excel, len v oveľa novšej verzii, takže by to malo fungovať rovnako.

Keď som pozeral dôvody po webe, prečo je to tak, tak som narazil na rôzne výhovorky, prečo je to takto v poriadku. Oficiálna verzia tímu Power BI je taká, že to takto má fungovať. Blogujú ako o dušu, ale aktualizácia dokumentácie nikde. Takže bolo potrebné hľadať inde.

Dôvod je nakoniec v tom, že tento stĺpec má nastavenú funkciu “Zoradiť podľa stĺpca“, podľa stĺpca MonthNumberOfYear v tej istej tabuľke. Preto sa aj názvy mesiacov zobrazujú v správnom poradí, a nie abecedne. Problém je však v tom, že vraj táto funkcia nie je implementovaná v PowerPivote, ale ju musí implementovať klient. Konkrétne, v Exceli sa o zoradenie položiek v kontingenčke vraj stará Excel pri zobrazovaní dát, a v Power BI sa o zoradenie stará Power View, resp. reportovacia vrstva. Čo však nie je úplne pravda, lebo v SSAS Tabulare je presne ten istý PowerPivot, a pri jeho použití ako dátového zdroja to klienti nemusia implementovať a jednoducho to funguje správne.

Problém je v tom, že pri použití stĺpca, ktorý má nastavenú funkciu “Zoradiť podľa stĺpca“, s ním Power BI pracuje nesprávne, resp. nezavolá úplne správny dotaz na dátový model. Zavolá taký dotaz v jazyku DAX, kde si vyžiada neusporiadané dáta, a na konci ich triedi podľa toho dotknutého stĺpca aj jeho triediaceho stĺpca súčasne – pretože DAX umožňuje triediť výsledky len klauzulou ORDER BY a len podľa hodnôt z klauzuly EVALUATE, a nie podľa toho ako je to nastavené v dátovom modeli. Ale najmä zavolá takto štruktúrovaný dotaz, ktorý v marcovej verzii roku 2018 vyzerá takto:

V Exceli sa to nestáva, lebo ten sa dotazuje na dátový model pomocou jazyka MDX použitím funkcie Hierarchize, vďaka ktorej dostane správne usporiadané položky.

V Power BI to každopádne funguje nesprávne, a keďže to tím Power BI oficiálne prehlásil za “správne” – podľa hesla “it’s not a bug, it’s a feature” – tak sa tomu musíme prispôsobiť.

Náhradné riešenie spočíva v tom, že keď v Power BI odstraňujete filter pomocou funkcie ALL nad stĺpcom, ktorý má zapnuté zoradenie podľa iného stĺpca, tak je potrebné dať do funkcie ALL oba stĺpce – t.j. pôvodný aj triediaci stĺpec:

% obratu za rok = [Obrat] / CALCULATE([Obrat]; ALL(‘Čas'[MonthName]; ‘Čas'[MonthNumberOfYear]))

To vďaka spôsobu, akým Power BI konštruuje dotazy na dátový model, spôsobí, že už konečne dostaneme správne výsledky, rovnaké ako v PowerPivote v Exceli:

Takže pozor na funkciu ALL, keď ju používate v Power BI v spojení so zoraďovaním stĺpcov podľa iných stĺpcov. Je to dlhodobá chyba, a momentálne to nevyzerá, že by sa ju chystali tak skoro odstrániť. Preto v takomto prípade nezabúdajte, že v Power BI je potrebné dať do funkcie ALL oba stĺpce – pôvodný aj triediaci. To vám problém s nesprávnym fungovaním funkcie ALL v Power BI spoľahlivo vyrieši.