Jazyk DAX (Data Analysis Expressions) v doplnku Power Pivot

Jazyk DAX (Data Analysis Expressions) znie spočiatku trochu odstrašujúco, ale nenechajte sa zmiasť názvom. Základom jazyka DAX je veľmi jednoduché porozumieť. Najskôr to najdôležitejšie - DAX NIE JE programovací jazyk. DAX je jazyk vzorcov. Jazyk DAX môžete použiť na definovanie vlastných výpočtov pre vypočítané stĺpce a miery (známe aj ako vypočítavané polia). Jazyk DAX zahŕňa niektoré funkcie používané vo vzorcoch Excelu a ďalšie funkcie určené na prácu s relačnými údajmi a vykonávanie dynamickej agregácie.

Vysvetlenie vzorcov jazyka DAX

Vzorce jazyka DAX sú veľmi podobné vzorcom Excelu. Ak ho chcete vytvoriť, zadajte znak rovnosti, za ním názov funkcie alebo výraz a všetky požadované hodnoty alebo argumenty. Podobne ako Excel, aj DAX poskytuje množstvo funkcií, ktoré môžete použiť na prácu s reťazcami, vykonávanie výpočtov pomocou dátumov a časov alebo vytváranie podmienených hodnôt.

Vzorce jazyka DAX sa však líšia v nasledujúcich dôležitých ohľadoch:

• Ak chcete prispôsobiť výpočty po jednotlivých riadkoch, jazyk DAX obsahuje funkcie, ktoré umožňujú použiť aktuálnu hodnotu riadka alebo súvisiacu hodnotu na výpočty, ktoré sa líšia v závislosti od kontextu.
• Jazyk DAX zahŕňa typ funkcie, ktorá vráti ako výsledok tabuľku, a nie jednu hodnotu. Tieto funkcie je možné použiť na poskytnutie vstupných údajov pre iné funkcie.
• Funkcie časovej inteligenciev jazyku DAX umožňujú výpočty pomocou rozsahov dátumov a porovnávanie výsledkov v paralelných obdobiach.

Kde sa používajú vzorce jazyka DAX

V doplnku Power Pivot môžete vytvoriť vzorce vo vypočítavaných stĺpcoch alebo vo vypočítavaných poliach.

Vypočítané stĺpce

Vypočítaný stĺpec je stĺpec, ktorý pridáte do existujúcej tabuľky doplnku Power Pivot. Namiesto prilepenia alebo importovania hodnôt do stĺpca vytvoríte vzorec jazyka DAX, ktorý definuje hodnoty stĺpca. Ak zahrniete tabuľku Power Pivot do kontingenčnej tabuľky (alebo kontingenčného grafu), vypočítaný stĺpec možno použiť ako akýkoľvek iný stĺpec s údajmi.

Vzorce vo vypočítaných stĺpcoch sú veľmi podobné vzorcom, ktoré vytvoríte v Exceli. Na rozdiel od Excelu však nemôžete vytvoriť odlišný vzorec pre rôzne riadky v tabuľke. namiesto toho sa vzorec jazyka DAX automaticky použije na celý stĺpec.

Ak stĺpec obsahuje vzorec, hodnota sa vypočíta pre každý riadok. Výsledky sa pre stĺpec vypočítajú hneď po vytvorení vzorca. Hodnoty stĺpca sa prepočítavajú iba pri obnovení príslušných údajov alebo pri použití manuálneho prepočtu.

Môžete vytvoriť vypočítané stĺpce založené na mierach a ďalšie vypočítané stĺpce. Nepoužívajte však rovnaký názov pre vypočítaný stĺpec a mieru, pretože to môže viesť k mätúcim výsledkom. Pri odkaze na stĺpec je najlepšie použiť úplný odkaz na stĺpec, aby ste sa vyhli náhodnému vyvolaniu miery.

Podrobnejšie informácie nájdete v článku Vypočítavané stĺpce v doplnku Power Pivot.

Miery

Ukazovateľ je vzorec vytvorený špeciálne na použitie v kontingenčnej tabuľke (alebo kontingenčnom grafe) používajúcom údaje doplnku Power Pivot. Miery môžu byť založené na štandardných agregačných funkciách, ako je COUNT alebo SUM, alebo môžete definovať vlastný vzorec pomocou jazyka DAX. Ukazovateľ sa používa v oblasti Hodnoty v kontingenčnej tabuľke. Ak chcete vypočítané výsledky umiestniť do inej oblasti kontingenčnej tabuľky, použite namiesto toho vypočítaný stĺpec.

Keď definujete vzorec pre explicitný ukazovateľ, nič sa nestane, kým ukazovateľ nepridáte do kontingenčnej tabuľky. Po pridaní ukazovateľa sa vzorec vyhodnotí pre každú bunku v oblasti Hodnoty v kontingenčnej tabuľke. Keďže výsledok sa vytvorí pre každú kombináciu hlavičiek riadkov a stĺpcov, výsledok pre ukazovateľ môže byť v každej bunke odlišný.

Definícia miery, ktorú vytvoríte, sa uloží spolu s tabuľkou zdrojových údajov. Zobrazí sa v zozname polí kontingenčnej tabuľky a je k dispozícii pre všetkých používateľov zošita.

Podrobnejšie informácie nájdete v téme Miery v doplnku Power Pivot.

Vytváranie vzorcov pomocou riadka vzorcov

Power Pivot, podobne ako Excel, obsahuje riadok vzorcov na jednoduchšie vytváranie a úpravu vzorcov a funkciu automatického dokončovania, ktorá minimalizuje chyby zadávania a syntaxe.

Zadanie názvu tabuľky Začnite písať názov tabuľky. Automatické dokončovanie vzorca poskytuje rozbaľovací zoznam obsahujúci platné názvy, ktoré sa začínajú určitými písmenami.

Zadanie názvu stĺpca Zadajte zátvorku a potom vyberte stĺpec zo zoznamu stĺpcov v aktuálnej tabuľke. V prípade stĺpca z inej tabuľky začnite písať prvé písmená názvu tabuľky a potom vyberte stĺpec z rozbaľovacieho zoznamu automatického dokončovania.

Ďalšie podrobnosti a návod na vytvorenie vzorcov nájdete v téme Vytváranie vzorcov pre výpočty v doplnku Power Pivot.

Tipy na používanie funkcie Automatické dokončovanie

Funkciu Automatické dokončovanie vzorca môžete použiť v prostriedku existujúceho vzorca s vnorenými funkciami. Text bezprostredne pred kurzorom sa používa na zobrazenie hodnôt v rozbaľovacom zozname a celý text za kurzorom zostane nezmenený.

Definované názvy, ktoré ste vytvorili pre konštanty, sa v rozbaľovacom zozname automatického dokončovania nezobrazia, ale môžete ich zadať.

Power Pivot nepridá pravú zátvorku funkcií ani automaticky nepriradí pravé zátvorky. Skontrolujte, či sú všetky funkcie syntakticky správne, inak vzorec nebude možné uložiť alebo použiť. 

Použitie viacerých funkcií vo vzorci

Funkcie môžete vnoriť, čo znamená, že výsledky jednej funkcie použijete ako argument inej funkcie. Vo vypočítavaných stĺpcoch môžete vnoriť až 64 úrovní funkcií. Vnorenie však môže sťažiť vytváranie vzorcov alebo riešenie problémov s nimi.

Mnohé funkcie jazyka DAX sú navrhnuté na používanie výhradne ako vnorené funkcie. Tieto funkcie vrátia tabuľku, v dôsledku ktorej sa nedá priamo uložiť. Mal by sa poskytovať ako vstup pre funkciu tabuľky. Napríklad funkcie SUMX, AVERAGEX a MINX vyžadujú tabuľku ako prvý argument.

Porovnanie funkcií jazyka DAX a funkcií Excelu

Knižnica funkcií jazyka DAX je založená na knižnici funkcií Excelu, tieto knižnice však majú veľa rozdielov. Táto časť obsahuje súhrn rozdielov a podobností medzi funkciami Excelu a funkciami jazyka DAX.

• Mnohé funkcie jazyka DAX majú rovnaký názov a všeobecné správanie ako excelové funkcie, boli však upravené tak, aby prijímali rôzne typy vstupov a v niektorých prípadoch môžu vrátiť odlišný údajový typ. Vo všeobecnosti nemožno použiť funkcie jazyka DAX vo vzorcoch Excelu ani excelové vzorce v doplnku Power Pivot bez určitých úprav.
• Funkcie jazyka DAX nikdy nepoužívajú odkaz na bunku alebo rozsah, no namiesto toho funkcie jazyka DAX používajú ako odkaz stĺpec alebo tabuľku.
• Funkcie dátumu a času jazyka DAX vrátia typ údajov datetime. Naproti tomu funkcie dátumu a času v Exceli vrátia celé číslo, ktoré predstavuje dátum ako poradové číslo.
• Mnohé nové funkcie jazyka DAX buď vrátia tabuľku hodnôt, alebo vykonávajú výpočty založené na tabuľke hodnôt ako vstup. V porovnaní s tým Excel neobsahuje žiadne funkcie, ktoré by vrátili tabuľku, ale niektoré funkcie dokážu pracovať s poľami. Možnosť jednoduchých odkazov na celé tabuľky a stĺpce je novou funkciou v doplnku Power Pivot.
• Jazyk DAX poskytuje nové vyhľadávacie funkcie, ktoré sú podobné vyhľadávacím funkciám polí a vektorových funkcií v Exceli. Funkcie jazyka DAX však vyžadujú vytvorenie vzťahu medzi tabuľkami.
• Očakáva sa, že údaje v stĺpci budú mať vždy rovnaký typ údajov. Ak nejde o rovnaký typ údajov, jazyk DAX zmení celý stĺpec na typ údajov, ktorý najlepšie vyhovuje všetkým hodnotám.

Typy údajov jazyka DAX

Údaje môžete do dátového modelu doplnku Power Pivot importovať z mnohých rôznych zdrojov údajov, ktoré môžu podporovať rôzne typy údajov. Keď importujete alebo načítavate údaje a potom ich použijete vo výpočtoch alebo v kontingenčných tabuľkách, údaje sa skonvertujú na jeden z typov údajov doplnku Power Pivot. Zoznam typov údajov nájdete v téme Typy údajov v dátových modeloch.

Typ údajov tabuľka je nový typ údajov v jazyku DAX, ktorý sa používa ako vstup alebo výstup mnohých nových funkcií. Funkcia FILTER napríklad použije tabuľku ako vstup a výstup inej tabuľky, ktorá obsahuje iba riadky spĺňajúce podmienky filtra. Kombináciou funkcií tabuľky s agregačnými funkciami môžete vykonávať zložité výpočty v dynamicky definovaných množinách údajov. Ďalšie informácie nájdete v článku Agregácie v doplnku Power Pivot.

Vzorce a relačný model

Okno doplnku Power Pivot je oblasť, kde môžete pracovať s viacerými tabuľkami údajov a prepojiť tieto tabuľky v relačnom modeli. V rámci tohto dátového modelu sú tabuľky navzájom prepojené vzťahmi, čo vám umožňuje vytvárať korelácie so stĺpcami v iných tabuľkách a vytvárať zaujímavejšie výpočty. Môžete napríklad vytvoriť vzorce, ktoré sčítavajú hodnoty súvisiacej tabuľky a potom uložiť túto hodnotu do jednej bunky. Riadky zo súvisiacej tabuľky môžete ovládať aj použitím filtrov tabuliek a stĺpcov. Ďalšie informácie nájdete v téme Vzťahy medzi tabuľkami v modeli údajov.

Keďže tabuľky môžete prepojiť pomocou vzťahov, kontingenčné tabuľky môžu zahŕňať aj údaje z viacerých stĺpcov z rôznych tabuliek.

Keďže však vzorce môžu pracovať s celými tabuľkami a stĺpcami, musíte navrhovať výpočty inak ako v Exceli.

• Vo všeobecnosti sa vzorec jazyka DAX v stĺpci vždy použije na celú množinu hodnôt v stĺpci (nikdy nie na iba niekoľko riadkov alebo buniek).
• Tabuľky v doplnku Power Pivot musia mať vždy rovnaký počet stĺpcov v každom riadku a všetky riadky v stĺpci musia obsahovať rovnaký typ údajov.
• Keď sú tabuľky prepojené vzťahom, očakáva sa, že dva stĺpce použité ako kľúče majú vo väčšine prípadov zhodné hodnoty. Keďže Power Pivot nezabezpečuje referenčnú integritu, je možné, že v stĺpci kľúča sa budú nezhodovať hodnoty, a napriek tomu sa vytvorí vzťah. Prítomnosť prázdnych alebo nezhodujúcich sa hodnôt však môže ovplyvniť výsledky vzorcov a vzhľad kontingenčných tabuliek. Ďalšie informácie nájdete v téme Vyhľadávania vo vzorcoch doplnku Power Pivot.
• Prepojením tabuliek pomocou vzťahov sa zväčší rozsah alebo kontext, v ktorom sa vzorce vyhodnocujú. Napríklad vzorce v kontingenčnej tabuľke môžu byť ovplyvnené ľubovoľnými filtrami alebo záhlaviami stĺpcov a riadkov v kontingenčnej tabuľke. Môžete písať vzorce, ktoré manipulujú s kontextom, kontext však môže tiež spôsobiť, že sa výsledky zmenia spôsobom, ktorý nemusíte predvídať. Ďalšie informácie nájdete v kontexte vo vzorcoch jazyka DAX.

Aktualizácia výsledkov vzorcov

Obnovenie a prepočítanie údajov sú dve samostatné, ale súvisiace operácie, ktorým by ste mali porozumieť pri navrhovaní dátového modelu obsahujúceho zložité vzorce, veľké množstvo údajov alebo údaje získané z externých zdrojov údajov.

Obnovenie údajov je proces aktualizácie údajov v zošite novými údajmi z externého zdroja údajov. Údaje môžete obnovovať manuálne v intervaloch, ktoré určíte. Alebo ak ste zošit publikovali na lokalite SharePoint, môžete naplánovať automatické obnovenie z externých zdrojov.

Prepočítanie je proces aktualizácie výsledkov vzorcov tak, aby odrážali všetky zmeny samotných vzorcov a aby tieto zmeny odrážali aj v základných údajoch. Prepočítanie môže ovplyvniť výkon nasledovnými spôsobmi:

• Pri vypočítavanom stĺpci by sa mal výsledok vzorca vždy prepočítať pre celý stĺpec vždy, keď zmeníte vzorec.
• V prípade ukazovateľa sa výsledky vzorca vypočítavajú až po umiestnení ukazovateľa do kontextu kontingenčnej tabuľky alebo kontingenčného grafu. Vzorec sa prepočíta aj vtedy, keď zmeníte ľubovoľnú hlavičku riadka alebo stĺpca, ktorá ovplyvňuje filtre údajov, alebo keď manuálne obnovíte kontingenčnú tabuľku.

Riešenie problémov so vzorcami

Chyby pri písaní vzorcov

Ak sa pri definovaní vzorca vyskytne chyba, vzorec môže obsahovať syntaktickú chybu, sémantickú chybu alebo chybu výpočtu.

Syntaktické chyby sa riešia najjednoduchšie. Zvyčajne obsahujú chýbajúcu zátvorku alebo čiarku. Pomoc so syntaxou jednotlivých funkcií nájdete v téme Prehľad funkcií jazyka DAX.

Druhý typ chyby sa vyskytne, keď je syntax správna, ale hodnota alebo stĺpec, na ktorý sa odkazuje, nedáva zmysel v kontexte vzorca. Tieto sémantické chyby a chyby vo výpočtoch môžu byť spôsobené ktorýmkoľvek z nasledujúcich problémov:

• Vzorec odkazuje na neexistujúci stĺpec, tabuľku alebo funkciu.
• Vzorec sa zdá byť správny, no keď údajový nástroj načíta údaje, zistí nezhodu typov a spôsobí chybu.
• Vzorec funkcii odovzdá nesprávny počet alebo typ parametrov.
• Vzorec odkazuje na iný stĺpec, ktorý obsahuje chybu, a preto sú jeho hodnoty neplatné.
• Vzorec odkazuje na stĺpec, ktorý nebol spracovaný, čo znamená, že obsahuje metaúdaje, ale neobsahuje skutočné údaje, ktoré by sa dali použiť na výpočty.

V prvých štyroch prípadoch jazyk DAX označí príznakom celý stĺpec, ktorý obsahuje neplatný vzorec. V poslednom prípade jazyk DAX sivou farbou stĺpca signalizuje, že stĺpec je v nespracovanom stave.

Nesprávne alebo nezvyčajné výsledky pri zoraďovaní alebo zoradení hodnôt stĺpcov

Pri zoraďovaní alebo zoraďovaní stĺpca, ktorý obsahuje hodnotu NaN (nie číslo), sa môžu vyskytnúť nesprávne alebo neočakávané výsledky. Ak sa napríklad pri výpočte vydelí číslo 0 číslom 0, vráti sa výsledok NaN.

Je to preto, že mechanizmus vzorcov vykonáva zoradenie a poradie porovnaním číselných hodnôt. Hodnotu NaN však nemožno porovnávať s inými číslami v stĺpci.

Ak chcete zabezpečiť správne výsledky, môžete použiť podmienené príkazy používajúce funkciu IF, ktoré skontrolujú hodnoty NaN a vrátia číselnú hodnotu 0.

Kompatibilita s tabuľkovými modelmi služby Analysis Services a režimom DirectQuery

Vo všeobecnosti platí, že vzorce jazyka DAX, ktoré vytvoríte v doplnku Power Pivot, sú plne kompatibilné s tabuľkovými modelmi služby Analysis Services. Ak však migrujete model doplnku Power Pivot do inštancie služby Analysis Services a potom ho nasadíte v režime DirectQuery, existujú určité obmedzenia.

• Niektoré vzorce jazyka DAX môžu vrátiť odlišné výsledky, ak nasadíte model v režime DirectQuery.
• Niektoré vzorce môžu spôsobiť chyby overenia pri nasadení modelu do režimu DirectQuery, pretože vzorec obsahuje funkciu DAX, ktorá nie je podporovaná pre relačný zdroj údajov.

Ďalšie informácie nájdete v dokumentácii k tabuľkovému modelovaniu služby Analysis Services v službe SQL Server 2012 BooksOnline.