Bežní excelisti sú zvyknutí na funkciu SUMIF. Keď však začnú pracovať s PowerPivotom alebo Power BI, tak sú prekvapení, že jazyk DAX nemá funkciu SUMIF. Má funkciu SUM aj IF, ale dokopy jaksi nejdú. Preto si teraz pozrieme, ako vyzerá náhrada SUMIF v jazyku DAX.

Aktualizácia 8.10.2020: Všeobecnejší popis X-kových funkcií aj ďalšie techniky náhrady funkcií SUMIF, SUMIFS či COUNTIF v jazyku DAX nájdete v tomto článku.

Keby ste skúšali zloženie funkcií SUM a IF dokopy, napr. ako SUM(IF(podmienka; …)), tak narazíte veľmi rýchlo na problém, že funkcia SUM berie za parameter iba stĺpec z dátového modelu. Takže na to budeme musieť ísť inak.

Budeme vychádzať z nášho vzorového súboru PowerPivotu, a budem nadväzovať na príklad z tohto článku, ktorý počítal obrat za každý produkt, ako vypočítaný stĺpec v tabuľke Produkty. Používal pri tom funkcie SUMX a RELATEDTABLE.

Povedzme, že máme vypočítaný stĺpec Obrat za produkt, so vzorcom:

=SUMX(RELATEDTABLE(‚Objednávky‘); ‚Objednávky'[Cena objednávky])

Teraz by sme chceli vypočítať obrat za produkt iba v USA. Využijeme to, že syntax funkcie SUMX je nasledovná:

SUMX(tabuľka; vzorec)

Táto funkcia funguje tak, že zoberie tabuľku z prvého parametra, a pre každý riadok tejto tabuľky vykoná vzorec z druhého parametra. Výsledok tohto vzorca uloží do dočasného vypočítaného stĺpca v pamäti, a z neho potom spočíta sumu rovnako ako funkcia SUM. To využijeme, a potom obrat za produkt iba v USA, pre daný produkt, vypočítame týmto vzorcom do nového vypočítaného stĺpca s názvom „Obrat za produkt v USA„:

=SUMX(RELATEDTABLE(‚Objednávky‘); IF(RELATED(‚Regióny'[Krajina])=“United States“; ‚Objednávky'[Cena objednávky]; 0))

Alebo naformátované takto:

Keď si porovnáme hodnoty pôvodného a nového stĺpca, tak ten nový stĺpec bude mať o niečo nižšie hodnoty, ktoré budú rovnaké, ako keby ste si zobrazili v kontingečke obraty za produkty odfiltrované len na USA:

A prečo je v tom vzorci funkcia RELATED? Pretože, vzorec v druhom parametri funkcie SUMX sa vyhodnocuje v kontexte aktuálne počítaného riadka z tabuľky z prvého parametra SUMX, v tomto prípade v kontexte 1 objednávky. A podľa pravidiel cestovania medzi tabuľkami musíme zavolať funkciu RELATED, aby sme si dotiahli príslušnú hodnotu z číselníka Regióny. Zvyšok je už len o finte, ktorou s funkciou IF vrátime hodnotu objednávky, keď je objednávka z USA, a inak nulu, aby na konci zosumovaním výsledného stĺpca vznikla suma len s číslami za USA.

Alternatívne riešenie pre Jedi mastrov je takýto vzorec:

=SUMX(CALCULATETABLE(‚Objednávky‘; FILTER(‚Regióny‘; ‚Regióny'[Krajina]=“United States“)); ‚Objednávky'[Cena objednávky])

Alebo naformátované takto:

V skratke – funkcia CALCULATETABLE zavolá funkciu RELATEDTABLE aplikovanú na prvý parameter funkcie (tu tabuľka Objednávky) s filtrami zadanými v druhom až n-tom parametri funkcie (tu funkcia FILTER). A vráti tabuľku, ktorú zosumujeme funkciou SUMX. Bližšie vysvetlenie Vám nechám za domácu úlohu 🙂

Takže, náhrada SUMIF z Excelu nie je zasa až tak komplikovaná – stačí vedieť pár funkcií jazyka DAX, a hneď nájdete niekoľko riešení, ako sa to dá spraviť. Tu máte 2 riešenia, a ak naozaj poznáte jazyk DAX, tak nie je problém po čase nájsť aj viacero riešení. A ak to beží rýchlo, tak každé riešenie, čo to vypočíta, je správne. Takže potom Vás nezaskočí ani náhrada SUMIF, ani žiadnej inej funkcie z Excelu 🙂