Jazyk DAX má pokročilejšie funkcie, ktoré častokrát na prvý pohľad nie sú zrejmé, na čo by mohli byť dobré. Pritom sa častokrát používajú v pokročilej analytike, alebo len na skrátenie dlhších zápisov v jazyku DAX. A jednou z takýchto funkcií je funkcia TREATAS, ktorá sa používa okrem iného aj na prenos filtrov na iné tabuľky a stĺpce. A na niektoré z jej použití sa teraz pozrieme.

Popis funkcie TREATAS

Funkcia TREATAS má podľa oficiálnej dokumentácie nasledujúcu syntax:

TREATAS ( filtrovaciaTabuľka; stĺpec1 [; stĺpec2; …; stĺpecN ] )

Funkcia TREATAS zoberie N-stĺpcovú filtrovaciuTabuľku z prvého parametra, a aplikuje hodnoty z jej stĺpcov ako filtre na tie stĺpce, ktoré sú uvedené v ďalších parametroch funkcie TREATAS. Pričom počet stĺpcov v zadanej filtrovacejTabuľke musí byť rovnaký ako počet stĺpcov zadaný od 2. parametra ďalej. Čiže ak zadáte filtrovaciuTabuľku napr. s 3 stĺpcami, tak hodnoty týchto stĺpcov sa použijú ako filtre na stĺpce zadané v ďalších parametroch funkcie TREATAS. Hodnoty z prvého stĺpca filtrovacejTabuľky sa použijú ako filter nad stĺpcom1, hodnoty z druhého stĺpca filtrovacej tabuľky sa použijú ako filter nad stĺpcom2, atď.. Pri aplikovaní filtrov sa prejaví rovnaké správanie, ako pri indickom zápise filtrov v jazyku DAX.

Zjednodušene sa táto funkcia dá vysvetliť aj cez jej preklad z angličtiny. Slovné spojenie “treat as” znamená “zachádzať (s niečím) ako”. Čiže pomocou tejto funkcie povieme DAX-u, že zachádzaj s hodnotami v stĺpcoch vo filtrovacejTabuľke ako s filtrami nad stĺpcami 1-N. Alebo ešte jednoduchšie – použi hodnoty v stĺpcoch vo filtrovacejTabuľke ako filtre nad príslušnými stĺpcami 1-N.

Dátový typ stĺpcov z filtrovacejTabuľky nemusí byť rovnaký ako dátový typ stĺpcov, na ktorý sú tieto filtre aplikované. V takomto prípade prebehne automatická konverzia hodnôt. Funkcia je dostupná v DAX-e od roku 2016, takže ju okrem Power BI nájdete aj v PowerPivote pre Excel 2016 a novší, a aj v SSAS Tabulare 2016 a novšom.

Pozrime sa teraz na niekoľko príkladov, ako funkciu TREATAS použiť v jazyku DAX. Na demonštráciu opäť využijeme náš vzorový súbor PowerPivotu, resp. Power BI.

Spravíme si kontingenčku, kde:

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

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

Jednoduchý príklad pre funkciu TREATAS

Najjednoduchšie sa dá funkcia TREATAS demonštrovať ako alternatívny zápis k indickému zápisu filtrov. Povedzme, že by sme chceli vypočítať obrat za USA. Čiže s pohľadu DAX-u chceme vypočítať hodnotu merítka Obrat, pričom do kontextu výpočtu chceme nastaviť filter nad krajinou USA. Keby sme to chceli zapísať cez indický zápis filtrov, tak by sme takto nastavili filter nad príslušným stĺpcom v dátovom modeli:

Obrat za USA IZF := CALCULATE( [Obrat]; 'Regióny'[Krajina] = "United States" )

A výsledok po dosadení do kontingenčky bude takýto:

Rovnaký výsledok však vieme dosiahnuť aj pomocou funkcie TREATAS, s takýmto merítkom:

Obrat za USA TA := CALCULATE ( [Obrat]; TREATAS ( { "United States" } ; 'Regióny'[Krajina] ) )

…a po dosadení do kontingenčky dostaneme úplne identické čísla ako v predchádzajúcom prípade:

Vysvetlenie

V tomto prípade sme do prvého parametra dosadili hodnotu “United States”, zabalenú do tabuľkového konštruktora. To sú tie zložené zátvorky okolo tej hodnoty. Tabuľkový konštruktor vytvorí zo zadaných hodnôt v tých zátvorkách 1-stĺpcovú tabuľku s tými hodnotami. A takáto tabuľka sa dá potom použiť v DAX-e všade tam, kde sa očakáva tabuľka. Čiže napr. aj v prvom parametri funkcie TREATAS.

V tomto prípade sme teda vytvorili tabuľku s 1 stĺpcom (bez názvu stĺpca, lebo ho aj tak nepotrebujeme), a v tom stĺpci je jediná hodnota “United States”. Následne funkcia TREATAS použila hodnotu z tohto stĺpca ako filter nad stĺpcom ‘Regióny'[Krajina]. A v takomto kontexte výpočtu sme dali cez CALCULATE vypočítať Obrat. Čo nám vypočítalo Obrat odfiltrovaný na krajinu USA.

Ak by v tej tabuľke bolo zadaných viac hodnôt (a nie len 1 hodnota “United States”), tak by to všetky tieto hodnoty samozrejme použilo ako viac-hodnotový filter nad stĺpcom ‘Regióny'[Krajina].

Takýto jednoduchý príklad samozrejme nemá zmysel používať, a kratšie a pohodlnejšie je to zadať cez indický zápis filtrov. Je to však na demonštráciu, aby ste pochopili základné fungovanie tejto funkcie, predtým než sa pozrieme na zložitejšie zápisy.

Virtuálne a dynamické prepojenia pomocou funkcie TREATAS

Druhým príkladom na použitie funkcie TREATAS sú virtuálne a dynamické prepojenia medzi tabuľkami. V tomto prípade sa používa funkcia TREATAS buď na simuláciu neexistujúcich prepojení v dátovom modeli, alebo pre dynamické prepojenia (čiže prepočet cez prepojenie X alebo Y napr. na základe podmienky). Z pohľadu výkonu je lepšie tie prepojenia vytvoriť ako klasické prepojenia, prípadne neaktívne prepojenia, a počítať to cez ne, ak to v danom prípade ide spraviť. Pretože emulované prepojenia cez DAX sú oveľa pomalšie ako klasické prepojenia v dátovom modeli. Sú však prípady, kedy si nemôžete dovoliť vytvoriť konkrétne prepojenia medzi tabuľkami, a v takomto prípade sa dá použiť táto technika.

Povedzme, že by sme si pridali do tabuľky Objednávky nový vypočítaný stĺpec, v ktorom bude dátum vybavenia objednávky:

Dátum vybavenia = 'Objednávky'[SaleDateKey] + 7

Teraz by sme chceli “akože” prepojiť tento stĺpec s dátumovým stĺpcom v tabuľke Čas, aby sme mohli robiť výpočty v čase podľa dátumu vybavenia objednávky. Techník na to je mnoho. V tomto prípade by bolo najľahšie vytvoriť neaktívne prepojenie medzi týmito tabuľkami a použiť kontextovú funkciu USERELATIONSHIP na vykonanie prepočtu cez toto neaktívne prepojenie. My si to však teraz spravíme bez vytvoreného prepojenia, cez funkciu TREATAS.

Ak by sme teda chceli vytvoriť merítko, ktoré vypočíta počet objednávok v danom období, pričom výpočet pôjde podľa dátumu vybavenia, a zobraziť ho v kontingenčke vyššie (ktorá robí výpočty cez existujúce prepojenie medzi tabuľkami, podľa dátumu objednania), tak to môžeme spraviť napr. pomocou takéhoto vzorca:

Pocet vybavenych objednavok := CALCULATE ( COUNTROWS('Objednávky'); ALL('Čas'); TREATAS( VALUES('Čas'[DateKey]); 'Objednávky'[Dátum vybavenia] ) )

A po dosadení do kontingenčky dostaneme takýto výsledok:

Ako to funguje?

Vo funkcii CALCULATE sme teraz použili 2 funkcie ako modifikátory kontextu výpočtu. Funkciu ALL sme použili na zrušenie všetkých časových filtrov (v našom prípade filter nad stĺpcom ‘Čas'[CalendarYear]), aby tie filtre “neprebublali” cez prepojenie z tabuľky Čas do tabuľky Objednávky, a nefiltrovali výsledok podľa dátumu objednania. Ako druhú funkciu sme použili funkciu TREATAS, kde sme povedali, aby táto funkcia zobrala všetky použité unikátne hodnoty/dátumy zo stĺpca ‘Čas'[DateKey] v aktuálnom kontexte výpočtu (čiže všetky dátumy z aktuálne vyhodnocovaného roka v kontingenčke), a aplikovala ich ako viac-hodnotový filter na stĺpec ‘Objednávky'[Dátum vybavenia]. Čiže to, čo by spravil dátový model automaticky, keby medzi tými 2 stĺpcami existovalo prepojenie.

Tu si všimnite to, že funkcia ALL sa ešte neaplikovala, a funkcia TREATAS sa vyhodnocuje ešte v pôvodnom kontexte výpočtu, tak ako aj všetky ostatné funkcie použité v CALCULATE na zmenu kontextu výpočtu. Následne funkcia CALCULATE aplikovala funkcie ALL aj TREATAS súčasne na aktuálny kontext výpočtu, aby ho zmenila, a v takomto zmenenom kontexte výpočtu vypočítala počet objednávok. Čím sme dostali počet vybavených objednávok v tomto období. V tomto konkrétnom prípade v danom roku, ale fungovať to bude pre ľubovoľné časové obdobia v kontingenčke.

A takýmto spôsobom viete simulovať neexistujúce prepojenia medzi tabuľkami, aj cez viacero stĺpcov súčasne. Existujú na to aj viaceré ďalšie spôsoby, a niektoré z nich popisujem v mojej knihe o Power BI v časti o virtuálnych prepojeniach. Takisto sa táto technika dá použiť na simuláciu dynamických prepojení, keď si podmienene vo vzorci vyberáte, na základe podmienok XY, ktoré prepojenie použijete. Vo všetkých prípadoch by sa tieto techniky mali používať vtedy, keď si nemôžete dovoliť vytvoriť prepojenie medzi tabuľkami. Pretože emulované prepojenie cez DAX je niekoľko násobne až o niekoľko rádov pomalšie ako klasické prepojenie medzi tabuľkami.

Zložitejší príklad pre funkciu TREATAS

Funkcia TREATAS sa však používa aj v mnohých ďalších prípadoch, a aj v omnoho komplexnejších scenároch. My si jeden z nich ukážeme. Povedzme, že by sme v hore uvedenej kontingenčke chceli zobraziť Obrat za prvé objednávky z každého jedného dňa v danom období (v tomto prípade je obdobím 1 rok). To vieme “poľahky” vypočítať takýmto merítkom:

Obrat za prvé objednávky v každom dni :=
CALCULATE (
    [Obrat];
    TREATAS (
        ADDCOLUMNS (
            SUMMARIZE ( 'Objednávky'; 'Objednávky'[SaleDateKey] );
            "Prva objednavka"; CALCULATE ( MIN ( 'Objednávky'[SalesOrderNumber] ) )
        );
        'Objednávky'[SaleDateKey];
        'Objednávky'[SalesOrderNumber]
    )
)

A po dosadení do kontingenčky dostaneme takýto výsledok:

A ako to funguje?

Najprv si musíme uvedomiť kontext výpočtu, ktorým je v tomto prípade 1 konkrétny rok v kontingenčke. Potom si funkciou SUMMARIZE zoskupíme objednávky v aktuálnom kontexte výpočtu podľa dátumu objednania (podľa stĺpca SaleDateKey). To nám vráti 1-stĺpcovú tabuľku so všetkými dátumami objednania v danom roku. Potom funkciou ADDCOLUMNS pridáme k takejto tabuľke vypočítaný stĺpec s názvom “Prva objednavka”, do ktorého vypočítame číslo prvej objednávky v danom dni. Ako prvú objednávku vyberieme objednávku s najnižším číslom v stĺpci SalesOrderNumber, samozrejme v kontexte daného dňa.

Takúto 2-stĺpcovú tabuľku potom použijeme vo funkcii TREATAS, a hodnoty z týchto 2 stĺpcov aplikujeme ako filtre na stĺpce SaleDateKey a SalesOrderNumber v tabuľke Objednávky, ako keby indickým zápisom filtrov. V takomto kontexte výpočtu, odfiltrovanom na každý dátum objednania + prvú objednávku v ňom, dáme funkciou CALCULATE vypočítať merítko Obrat. Čo nám vráti obrat za prvé objednávky v každom dni, tak ako sme chceli.

Na záver

Takto teda funguje funkcia TREATAS v jazyku DAX. Nie je to zrovna jednoduchá funkcia. Ale keď sa naučíte rozmýšľať správne kontextovo v jazyku DAX, tak si pomocou nej viete skrátiť množstvo komplikovanejších zápisov. Či uľahčiť množstvo výpočtov. Občas sa síce aj tak nevyhnete dlhšiemu rozmýšľaniu nad vzorcom, ale nikto nie je dokonalý. Každopádne, keď už viete, na čo sa dá použiť funkcia TREATAS, a budete niekedy nabudúce potrebovať prenášať filtre z jedného miesta na druhé, alebo rôznym podobným spôsobom čarovať s filtrami, tak Vám funkcia TREATAS ešte určite príde vhod 🙂