Niekedy potrebujete v kontingenčke zobraziť iba vyfiltrované dáta. Na čom samozrejme v bežnom prípade stačia zabudované filtre. Čo však, keď chcete zobraziť napr. len také riadky, kde len jedna z hodnôt v nich spĺňa nejakú podmienku? Vtedy si už musíme dopomôcť jazykom DAX.

Na demonštráciu problému a následného riešenia znova využijeme náš vzorový súbor PowerPivotu, resp. Power BI. Vytvoríme kontingenčku, kde:

• do oblasti riadkov dáme z tabuľky Regióny stĺpec Krajina,
• do oblasti stĺpcov dáme z tabuľky Čas stĺpec CalendarYear,
• do oblasti hodnôt dáme merítko Obrat.

Výsledkom budú obraty v krajinách vo všetkých rokoch:

Potom si označíme tabuľku Čas ako časovú tabuľku, a pridáme si ďalšie merítko, ktoré vypočíta medziročnú zmenu:

Medziročná zmena = [Obrat] – CALCULATE([Obrat]; SAMEPERIODLASTYEAR(‘Čas'[DateKey]))

To si tiež pridáme do kontingenčky, a odstránime z nej merítko Obrat:

Tam vidíte, že niektoré krajiny celý čas iba rástli, ale niektoré aj rástli aj klesali.

A teraz by sme chceli zobraziť iba tie riadky v kontingenčke, pri ktorých je aspoň jedna hodnota v riadku záporná. Čiže chceme zobraziť len tie krajiny, kde bol aspoň v jednom roku pokles Obratu. A to už samotné filtre v kontingenčke nezvládnu.

Spravíme si teda nové pomocné merítko, ktorým si budeme počítať na úrovni celého daného riadku, či sa má zobraziť alebo nie. Bude mať tento vzorec:

Zobraziť =
IF (
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE (
                    CALCULATETABLE ( 'Objednávky'; ALL ( 'Čas'[CalendarYear] ) );
                    'Čas'[CalendarYear]
                );
                "Zmena"; [Medziročná zmena]
            );
            [Zmena] < 0 ) ) > 0;
    "áno";
    "nie"
)

…alebo vypeknené cez DAX Formatter:

Tento vzorec robí nasledovné:

1. najprv z riadkov tabuľky Objednávky, prislúchajúcich do aktuálneho kontextu výpočtu, odstránime filter nad stĺpcom ‘Čas'[CalendarYear], aby sme dosiahli na objednávky za všetky roky, s rešpektovaním všetkých ostatných filtrov (napr. krajiny),
2. potom tieto objednávky zosumarizujeme funkciu SUMMARIZE na úrovni rokov, čiže podľa onoho stĺpca ‘Čas'[CalendarYear],
3. potom k výslednej medzitabuľke pridáme funkciou ADDCOLUMNS nový stĺpec “Zmena”, do ktorého vypočítame medziročnú zmenu pre daný rok, merítkom “Medziročná zmena”. Tým dostaneme akože celý “riadok” tej kontingenčky (v skutočnosti je to ale tabuľka zmien po rokoch),
4. následne z výsledku odfiltrujeme len tie riadky, ktoré spĺňajú našu podmienku – a teda že Zmena je menšia ako nula – pretože hľadáme riadky, kde bola medziročná zmena menšia ako 0 aspoň v jednom z rokov,
5. na konci spočítame počet takýchto riadkov funkciou COUNTROWS, a ak je ich viac ako 0 – čiže ak sme našli aspoň 1 hodnotu podľa zadania – tak vrátime hodnotu “áno”, inak “nie”.

Výsledok po pridaní merítka do kontingenčky bude vyzerať takto (zobrazená iba časť výsledkov):

Tam vidíte, že merítko vracia hodnotu “áno” alebo “nie” pre celý riadok kontingenčky, podľa toho, či to spĺňa naše zadanie alebo nie.

Potom stačí už len nastaviť filter v kontingenčke na výsledok tohto merítka tak, aby zobrazovala iba tie riadky, kde hodnota merítka Zobraziť je rovná hodnote “áno”. A odstrániť toto pomocné merítko z oblasti hodnôt, lebo ho už nepotrebujeme zobrazovať, iba filtrovať podľa neho.

Výsledok bude takýto:

A to je presne to, čo sme chceli. Rýchle, ľahké, elegantné. Využili sme pri tom iba základné vlastnosti kontextu výpočtu a funkcie pre prácu s ním, a klasické funkcie na agregovanie a filtrovanie hodnôt. Na výpočet vhodného pomocného merítka, ktorým odfiltrujeme výstup presne tak, ako požadujeme. A tým sa dostaneme o krok ďalej k ovládnutiu sveta. Čo viac si ešte môžeme želať? 🙂