Už žádné spamování CALCULATE: Iterace přes kategorie v DAXu

Vojtěch Šíma
5 days ago
7 min read

tl;dr: Už jsi někdy potřeboval napsat stejnou logiku pro několik kategorií, ale netušil jsi, že existují iterátory? Takže jsi šel na klasiku: pětkrát CALCULATE(), pokaždé pro jednu kategorii. A pak to třeba všechno sečetl nebo zprůměroval? Yep. Všichni známe. Dneska ti ukážu, jak to zvládneš udělat i jinak, aniž bys po chvíli musel hledat lano. Pomocí SUMX, AVERAGEX nebo jiného X-factoru vyrobíš to samé – jen čistěji, chytřeji a hlavně efektivněji.

CALCULATE přece spraví všechno, ne?

Když poprvé objevíš CALCULATE, připadáš si jako stejný frajer, jako první člověk, co vynalezl oheň. Najednou všechno dává smysl a všechno s ním vyřešíš. A tak ho začneš cpát všude, protože proč ne, že. Jenže dřív nebo později zjistíš, že když to přeženeš, nezhoří ti jen výpočet, ale klidně i celý model.

Zakázaná syntaxe

Takže, kdy je toho teda až moc?

Představ si následující: máš výpočet, co potřebuješ uplatnit na úrovni kategorie – třeba efektivitu oddělení. A pokud jsi v DAXu nový, nebo jsi až moc dlouho žil v Excelu, první myšlenka bývá: Napiš to pro každé oddělení zvlášť. Pak to všechno sečteš, vydělíš počtem oddělení… a je hotovo.

Jakože ano, teoreticky to funguje, ale jak dynamické či škálovatelné to je?

A jestli myslíš, že si z tebe dělám srandu, nebo tě shazuju, tak neboj. Popisuji, jak jsem začínal já sám.

CALCULATE(
    [efficency],
    dim_department[departmentName] = "Finance"
)

CALCULATE(
    [efficency],
    dim_department[departmentName] = "IT"
)

CALCULATE(
    [efficency],
    dim_department[departmentName] = "Legal"
)

( [efficency Finance] + [efficency IT] + [efficency Legal] ) / 3

Řekněme, že toto je tvoje kalkulace. Možná pokud jsi trošku liška mazaná, mohl bys mi namítnout: "Však je to dobré, akorát použít DISTINCTCOUNT/COUNTROWS místo psaní 3 natvrdo, udělat dělení přes DIVIDE() místo /, a je to vončo."

No… jako jo, běží to. A možná fakt chceš mít pro každé oddělení vlastní Card vizuál a teoreticky mít kalkulaci pro každé oddělení zvlášt je v pohodě. Ale pokud chceš tuto logiku nějak rozumně agregovat do jednoho výsledku? Spíš ne. Tohle je špatně už v základu. DAX ti na tenhle typ úloh dává přesné nástroje. A tohle mezi ně nepatří.

X-factor

Chceš projet jednotlivá oddělení – neboli iterovat – a pro každé něco spočítat, pak to třeba zprůměrovat nebo sečíst? Tak přesně na to jsou tu iterátory.

Tyhle funkce dělají přesně to, co od nich čekáš – vezmou tabulku, jdou řádek po řádku, vyhodnotí výraz a pak s výsledkama něco udělají. Přičtou, zprůměrujou, co je zrovna potřeba. Vsadím se, že jsi o nich už slyšel – SUMX nebo AVERAGEX ti určitě něco říká.

A možná jsi je už i použil. Třeba podobně jako snad ve všech tutoriálech:

SUMX('Table', [Quantity] * [UnitPrice])

Řádek po řádku, spočítat a sečíst. Dává to smysl, když jedeš přes fyzickou tabulku.

Ale tady je ten trik – nemusí to být jen faktová/fyzická tabulka. Když máš správně nastavenou dimenzi, nebo si rychle vytvoříš virtuální tabulku, funguje to úplně stejně. Oddělení jsou přece taky řádky. Jen neprocházíš transakce, ale kategorie.

Table vs Table Expression

IntelliSense (a taky oficiální dokumentace) ti tvrdí, že SUMX, AVERAGEX a další X-faktory chtějí tabulku a výraz. Ale co tím fakt myslí, je: výraz, který vrací tabulku – tedy tzv. table expression.

No a o tom to je. Už nejsi odkázaný jen na fyzické tabulky. Můžeš si začít tvořit vlastní, virtuální.

Malé varování bokem: nepřežeň to. Když budeš jako utržený ze řetězu, můžeš napáchat víc škody než s přespamovaným CALCULATEem.

Jedna z nejjednodušších cest, jak se dostat k virtuálním tabulkám, je funkce VALUES(). Můžeš jí dát celou tabulku nebo jen sloupec. A když jí dáš sloupec, vrátí ti jedinečné hodnoty.

Prakticky ti vrátí tabulku o jednom sloupci, kde název sloupce tabulky odpovídá názvu sloupce, co jsi zadal.

Jen jedna věc: když stavíš virtuální tabulky pro iteraci, drž je co nejmenší. Nicméně, pokud budeš používat VALUES(), měl bys být v pohodě.

Správná cesta

Příklad: Máme tři oddělení, pro každý chceme spočítat efektivitu a pak ty hodnoty zprůměrovat.

První věc, co potřebuješ, je samotný measure pro efektivitu – nebo ho klidně můžeš napsat rovnou dovnitř AVERAGEX. Já osobně ho mám většinou už bokem připravený, protože ho používám i jinde, nejen při rozdělení podle oddělení.

Druhá část je tabulka, přes kterou iterujeme. A protože v dim_department máme jen ta tři oddělení, klidně můžeme bez filtru vypočítat následující:

avg efficiency per department = 
AVERAGEX(
    VALUES( dim_department[departmentKey] ),
    [efficiency]
)

Je to celkem jednoduchý, ne? Iterujeme přes departmentKey a pro každý řádek spočítáme measure [efficiency]. Dostaneme průměr, který bere v potaz výkon všech oddělení.

Můžeš vidět, že pokud použiješ regulérní metriku [efficiency], nedostaneš průměr předem vypočítaného výkonu pro každé oddělení; dostaneš jenom jeden výpočet, který operuje bez kontextu filtru. Dokud jsi uvnitř kontextu oddělení, není tam, co "podělat", nicméně, jakmile bys chtěl Total nebo ukázat výsledek ve vizuálu Karty, hodnota už nebude tak přesná.

Naopak ta verze, která počítá efektivitu za každé oddělení zvlášť? Ta funguje vždycky. V tabulkovém vizuálu ti vrátí stejná čísla, ale i mimo rozpad podle oddělení správně zprůměruje hodnoty podle jednotlivých řádků zfiltrované tabulky oddělení.

Filtruj položky pro iteraci

Iterace se nijak nevymykají ostatním technikám – pořád můžeš (a často bys měl) používat CALCULATE pro filtrování. Dokonce bych to i doporučoval, protože čím míň řádků iteruješ, tím rychlejší výpočet.

Pokud máme tabulku s více odděleními a chtěli bychom počet snížit na naše tři, měli bychom je nějakým způsobem filtrovat. V našem případě je tabulka reprezentována pomocí funkce VALUES(). Pokud bych chtěl filtrovat tabulku, přes kterou iteruji, tvoje první myšlenka (a moje pravděpodobně taky) bude ji zaříznout přímo v prvním argumentu funkce X-faktoru (btw, tohle rozhodně není oficiální pojmenování) jako je SUMX nebo AVERAGEX.

Tímto způsobem pak můžeme napsat takový DAX:

avg efficiency per department filtered = 
AVERAGEX(
    CALCULATETABLE(
        VALUES( dim_department[departmentKey] ),
        dim_department[departmentName] IN {"Finance", "IT", "Legal"}
    ),
    [efficiency]
)

Nebo můžeš využít jeden trik. Jelikož {} je konstruktor tabulky (v tomhle případě ti vytvoří tabulku s jedním sloupcem nazvaným Value a třemi řádky s hodnotami), můžeš to napsat takhle:

avg efficiency per department filtered treatas = 
AVERAGEX(
    TREATAS(
        {"Finance", "IT", "Legal"},
        dim_department[departmentName]
    ),
    [efficiency]
)

Ale můžeme to udělat i jinak – 1) přirozeněji a 2) rychleji na zpracování. CALCULATE totiž nejdřív aplikuje všechny filtry a až potom provede samotný výpočet, takže položky, přes které iterujeme, můžeme omezit známou technikou právě pomocí CALCULATE:

CALCULATE (
    AVERAGEX (
        VALUES ( dim_department[departmentKey] ),
        [efficiency]
    ),
    KEEPFILTERS ( dim_department[departmentName]
        IN {
            "Finance",
            "IT",
            "Legal"
        }
	)
)

Obalíme výpočet do CALCULATE, abychom mohli přidat a přesunout filter právě tam. Taky jsme přidali KEEPFILTERS. Ten nám mimojiné zamezí přepsání kontextu filtru, když metriku například používáme ve vizuálu tabulky.

Podle mého testování, verze s CALCULATETABLE sama o sobě není nijak pomalejší. Nicměné hlavní performance boost nastane (ve prospěch CALCULATE /KEEPFILTERS), když metriku začneme používat například ve vizuálu tabulky. A o to primárně jde.

Vnořená iterace

Dřív nebo později narazíš na scénář, kdy ti připadá, že potřebuješ použít iterátor uvnitř jiného iterátoru. A ano, technicky to jde. Většinou je to však něco, čemu se chceš spíše vyhnout. Samozřejmě záleží na tom, jak máš postavený model, ale snadno můžeš skončit u tisíců a tisíců vnořených výpočtů, což není ideální.

Jak už jsem zmiňoval dřív: zlaté pravidlo je držet počet iterací co nejnižší. Ideálně nechceš iterovat přes celou fyzickou tabulku. Většinou úplně stačí pracovat s jednosloupcovou tabulkou pomocí VALUES(). To bývá nejčistší varianta.

Ale pokud potřebuješ víc než jeden sloupec, VALUES() už nestačí. V tom případě nastupuje SUMMARIZE(). Můžeš si v ní vypsat sloupce, které chceš, a sestavit si vlastní tabulku na míru pro iteraci. Jen bacha – SUMMARIZE() funguje dobře hlavně tehdy, když vybíráš sloupce z navzájem propojených tabulek.

Jak uvidíš za chvíli – pokud chceš do virtuální tabulky přidat nové "sloupce", nedávej výpočty přímo do SUMMARIZE. I když to technicky funguje, vždycky radši sáhni po ADDCOLUMNS, tady se dozvíš víc @SQLBI.

Pojďme podívat na jednu častou past. Představ si, že chceš nejdřív spočítat efektivitu pro každé oddělení a pak vzít průměr těchto hodnot za konkrétní den.

Mohlo by tě napadnout, že prostě iteruješ přes dvě dimenze najednou – třeba dim_department a calendar. Řekněme, že použiješ SUMMARIZE, abys z nich vytáhl unikátní kombinace. Bohužel... ne. Tyhle dvě tabulky spolu vůbec nemluví. Ve standardním star schematu nejsou dimenze mezi sebou propojené.

Potřebuješ "dohazovače". Něco, co mezi nimi vytvoří most. A tím "mostem" je v tomhle případě faktová tabulka.

A jen pro jistotu – nemluvím o klasickém bridge table jako u many-to-many vztahů. Tady tomu říkám bridge jen proto, že slouží jako lepidlo mezi dvěma jinak nepropojenými dimenzemi, aby spolu mohly v rámci iterace spolupracovat.

Takže pokud ti to model dovolí a metrika, se kterou pracuješ, není extrémně složitá, nejčistší řešení je iterovat přes faktovou tabulku. Obě dimenze už na ni napojené jsou, takže si z ní můžeš vytáhnout jejich klíče a získat platné kombinace, třeba takto:

efficiency iterative =
var _tbl =
    ADDCOLUMNS(
        SUMMARIZE(
            'fact',
            'calendar'[date],
            'dim_department'[departmentKey]
        ),
        "@eff", [efficiency]
    )
var result =
    AVERAGEX(
        _tbl,
        [@eff]
    )
return
    result

Tohle řešení funguje v pohodě, pokud je metrika jednoduchá a nečerpá z jiných faktových tabulek. Technicky vzato – různý obměny (jako třeba CROSSJOIN, VALUES v roli filtru apod.) můžou fungovat i u složitějších výpočtů, pokud zůstáváš v rámci filtr kontextu těch dimenzí, co tě zajímají.

Ale problém nastává, když chceš spočítat průměr i mimo vizuál – třeba v Total řádku v matrixu nebo v Card vizuálu. V tu chvíli si všimneš, že to prostě nesedí.

Nejpřímočařejší řešení pro složitější measure je tzv. dvojitá iterace (nebo vícenásobná, podle toho, kolik dimenzí potřebuješ). Není to zrovna nejkrásnější řešení, ale funguje. Jednoduše zanoříš jeden X-faktor do druhého, třeba takhle:

efficency iterative = 
AVERAGEX(
    VALUES(dim_date[date]),
    AVERAGEX(
        VALUES(dim_department[departmentKey]),
        [efficiency])
    )

Vezme všechny dostupné datumy v aktuálním kontextu a pro každé z nich projde všechna dostupná oddělení, spočítá průměr za jednotlivá oddělení a pak tyhle výsledky zprůměruje do jedné hodnoty. Pak se posune na další datum a celý cyklus zopakuje.

Co je fajn, tak i když později použiješ jiné sloupce ze stejných dimenzí, třeba týden, název oddělení nebo kategorii oddělení (zkrátka cokoliv v jiné nebo stejné úrovni detailu), výpočet by měl pořád sedět.

Závěr

Jestli jsi doteď řešil logiku na úrovni kategorií pomocí opakovaných CALCULATE, je čas posunout se o level výš. Iterátory ti dají čistší a flexibilnější metriky. A když k tomu přihodíš ještě znalost table expression funkcí jako VALUES nebo SUMMARIZE, můžeš se s opakováním CALCULATE jednou provždy rozloučit.