Výrazy jazyka DAX (Data Analysis Expressions) v Power Pivotu

Principy vzorců jazyka DAX

Vzorce jazyka DAX jsou velmi podobné vzorcům aplikace Excel. Chcete-li vzorec vytvořit, zadejte symbol rovná se a za ním název funkce nebo výraz a libovolné vyžadované hodnoty nebo argumenty. Stejně jako aplikace Excel poskytuje i jazyk DAX různé funkce, které lze použít k práci s řetězci, k provádění výpočtů pomocí kalendářních dat a časů nebo k vytváření podmíněných hodnot.

Vzorce jazyka DAX se však významně liší následujícími způsoby:

Chcete-li přizpůsobit výpočty pro jednotlivé řádky, jazyk DAX obsahuje funkce, které umožní použít hodnotu aktuálního řádku nebo související hodnotu k provedení výpočtů lišících se kontextem.
Jazyk DAX zahrnuje typ funkce, která vrací jako svůj výsledek tabulku namísto jedné hodnoty. Tyto funkce lze použít jako vstup pro další funkce.
Funkce časového měřítkav jazyce DAX umožňují výpočty s využitím rozsahů kalendářních dat a porovnávají výsledky napříč paralelními obdobími.

Používání vzorců jazyka DAX

Vzorce jde v doplňku Power Pivot vytvořit dvěma způsoby: v počítaných sloupcích nebo v počítaných polích.

Počítané sloupce

Počítaný sloupec je sloupec, který přidáte do stávající tabulky doplňku Power Pivot. Namísto vložení nebo importu hodnot do sloupce vytvoříte vzorec jazyka DAX, který definuje hodnoty sloupce. Pokud zahrnete tabulku doplňku Power Pivot do kontingenční tabulky (nebo kontingenčního grafu), může se počítaný sloupec použít stejným způsobem jako kterýkoli jiný sloupec dat.

Vzorce v počítaných sloupcích se hodně podobají vzorcům, které vytvoříte v Excelu. Na rozdíl od aplikace Excel však nelze vytvořit jiný vzorec pro různé řádky v tabulce; Místo toho se vzorec DAX automaticky použije na celý sloupec.

Pokud sloupec obsahuje vzorec, hodnota bude vypočítána pro každý řádek. Výsledky ve sloupci budou vypočítány ihned po vytvoření vzorce. Hodnoty ve sloupci budou přepočítány pouze při aktualizaci podkladových dat nebo při použití ručního přepočtu.

Můžete vytvořit počítané sloupce, které jsou založené na mírách a dalších počítaných sloupcích. Nepoužívejte ale pro počítaný sloupec a míru stejný název, protože to může vést k matoucím výsledkům. Při odkazování na sloupec je nejlepší použít plně kvalifikovaný odkaz na sloupec, abyste se vyhnuli náhodnému vyvolání míry.

Další podrobné informace najdete v tématu Počítané sloupce v Power Pivotu.

Míry

Míra je vzorec vytvořený přímo za účelem použití v kontingenční tabulce (nebo kontingenčním grafu), která používá data doplňku Power Pivot. Míry můžou být založené na standardních agregačních funkcích, jako je třeba POČET nebo SUMA, popřípadě můžete definovat vlastní vzorec pomocí jazyka DAX. Míra se používá v oblasti Hodnoty kontingenční tabulky. Pokud chcete počítané výsledky umístit do jiné oblasti kontingenční tabulky, použijte místo míry počítaný sloupec.

Definovaný vzorec explicitní míry se nevyhodnotí, dokud míru nepřetáhnete do kontingenční tabulky. Po přidání míry se vzorec vyhodnotí pro každou buňku v oblasti Hodnoty kontingenční tabulky. Protože je výsledek vytvořený pro každou kombinaci záhlaví řádků a sloupců, můžou se výsledky míry pro jednotlivé buňky lišit.

Definice vytvořené míry je uložená společně s její tabulkou zdrojových dat. Dá se zobrazit v seznamu polí kontingenční tabulky, kde je dostupná pro všechny uživatele sešitu.

Podrobnější informace najdete v tématu Míry v Power Pivotu.

Vytváření vzorců pomocí řádku vzorců

V doplňku Power Pivot, stejně jako v Excelu, řádek vzorců usnadňuje vytváření a upravování vzorců a funkce Automatické dokončování minimalizuje výskyt překlepů a chyb syntaxe.

Zadání názvu tabulky Začněte psát název tabulky. Funkce Automatické dokončování vzorců poskytne rozevírací seznam obsahující platné názvy začínající na tato písmena.

Zadání názvu sloupce Zadejte závorku a potom ze seznamu sloupců v aktuální tabulce zvolte sloupec. V případě sloupce z jiné tabulky začněte psát první písmena názvu tabulky a potom sloupec zvolte v rozevíracím seznamu funkce Automatické dokončování.

Další informace a návod k vytváření vzorců najdete v tématu Vytvoření vzorců pro výpočty v Power Pivotu.

Tipy k použití funkce Automatické dokončování

Funkci Automatické dokončování vzorců lze použít uprostřed existujícího vzorce s vnořenými funkcemi. Text bezprostředně před místem vkládání je použit k zobrazení hodnot v rozevíracím seznamu a veškerý text následující za místem vkládání zůstane nezměněn.

Definované názvy vytvořené pro konstanty se nezobrazují v rozevíracím seznamu funkce Automatické dokončování, ale můžete je zapsat ručně.

Doplněk Power Pivot nepřidává pravé závorky funkcí ani je automaticky nepáruje. Měli byste proto zkontrolovat, jestli je každá funkce syntakticky správně, jinak vzorec nejde uložit ani použít.

Použití více funkcí ve vzorci

Funkce lze vnořit, což znamená, že lze použít výsledky z jedné funkce jako argument jiné funkce. V počítaných sloupcích lze použít až 64 úrovní vnoření funkcí. Vnoření však může ztížit vytvoření vzorců a odstraňování potíží s nimi.

Mnoho funkcí jazyka DAX je navržených tak, aby se používaly výhradně jako vnořené funkce. Tyto funkce vrací tabulku, kterou nelze uložit přímo jako výsledek; měla by být poskytována jako vstup do funkce tabulky. Například funkce SUMX, AVERAGEX a MINX vyžadují jako první argument tabulku.

Poznámka: Aby nedocházelo k ovlivnění výkonu velkým množstvím výpočtů, které vyžadují závislosti mezi sloupci, je vnoření funkcí u měr omezeno.

Srovnání funkcí jazyka DAX a funkcí aplikace Excel

Knihovna funkcí jazyka DAX je založena na knihovně funkcí aplikace Excel, ale je v mnoha ohledech odlišná. Tato část shrnuje rozdíly a podobnosti mezi funkcemi aplikace Excel a funkcemi jazyka DAX.

Hodně funkcí jazyka DAX má stejné názvy a obecné chování jako funkce Excelu, ale byly pozměněné tak, aby mohly zpracovávat jiné typy vstupních dat, a v některých případech můžou vrátit rozdílné datové typy. Obvykle nejde bez úpravy používat funkce jazyka DAX ve vzorci Excelu nebo používat vzorce Excelu v doplňku Power Pivot.
Funkce jazyka DAX nikdy nepracují s odkazem na buňku ani s rozsahem jako s odkazem, namísto toho používají jako odkaz sloupec nebo tabulku.
Funkce data a času jazyka DAX vracejí datový typ datetime. Na druhou stranu, funkce data a času aplikace Excel vracejí celé číslo, které představuje datum v podobě pořadového čísla.
Mnoho nových funkcí jazyka DAX vrací tabulku hodnot nebo tabulku hodnot používá jako vstupní data pro výpočet. Excel žádné funkce, které vracejí tabulku, nemá, některé funkce ale pracují s poli. Možnost snadno odkazovat na celé tabulky a sloupce je novou funkcí doplňku Power Pivot.
Jazyk DAX poskytuje nové funkce vyhledávání, které jsou podobné funkcím vyhledávání polí a vektorů aplikace Excel. Funkce jazyka DAX však vyžadují, aby byla mezi tabulkami vytvořena relace.
Očekává se, že data ve sloupci vždy obsahují stejný datový typ. Pokud nejsou data stejného datového typu, jazyk DAX změní celý sloupec na datový typ, který nejlépe vyhovuje všem hodnotám.

Datové typy jazyka DAX

Data můžete do datového modelu doplňku Power Pivot importovat z mnoha různých zdrojů dat, které můžou podporovat různé datové typy. Pokud data do sešitu importujete nebo načtete a potom je použijete ve výpočtech nebo v kontingenčních tabulkách, převedou se na jeden z datových typů doplňku Power Pivot. Seznam datových typů najdete v tématu Datové typy v datových modelech.

Tabulka je nový datový typ jazyka DAX, který se používá jako vstup nebo výstup mnoha nových funkcí. Funkce FILTER například používá jako vstupní data tabulku, jejím výstupem je pak jiná tabulka, která obsahuje jen řádky odpovídající podmínkám filtru. Kombinací tabulkových a agregačních funkcí můžete provádět složité výpočty za použití dynamicky definovaných datových sad. Další informace najdete v tématu Agregace v doplňku Power Pivot.

Vzorce a relační model

Okno doplňku Power Pivot je prostor, který umožňuje pracovat s několika tabulkami dat a spojovat tyto tabulky v relačním modelu. V tomto datovém modelu jsou tabulky vzájemně propojené v relacích, které dovolují vytváření korelací mezi sloupci různých tabulek, a umožňují tak provádět zajímavější výpočty. Můžete například vytvořit vzorce, které sečtou hodnoty pro související tabulku, a následně tuto hodnotu uložit do jediné buňky. Nebo, pokud chcete ovládat řádky ze související tabulky, můžete použít filtry na tabulky a sloupce. Další informace najdete v tématu Relace mezi tabulkami v datovém modelu.

Protože tabulky lze propojit pomocí relací, kontingenční tabulky mohou také obsahovat data z více sloupců z různých tabulek.

Protože však mohou vzorce pracovat s celými tabulkami a sloupci, je třeba výpočty navrhnout jiným způsobem než v aplikaci Excel.

Vzorec jazyka DAX ve sloupci bude obecně použit pro celou sadu hodnot ve sloupci (nejen pro několik řádků nebo buněk).
Tabulky v doplňku Power Pivot musí vždy mít stejný počet sloupců pro každý řádek a všechny řádky ve sloupci musí obsahovat stejný datový typ.
Pokud tabulky propojené relací, měly by oba sloupce použité jako klíče obsahovat převážně hodnoty, které se shodují. Protože doplněk Power Pivot nevynucuje referenční integritu, jde vytvořit relaci i v případě, kdy jsou v klíčovém sloupci uvedené hodnoty, které se neshodují. Pokud však existují prázdné hodnoty nebo hodnoty, které se neshodují, můžou být ovlivněny výsledky vzorců a podoba kontingenčních tabulek. Další informace najdete v tématu Vyhledávání ve vzorcích doplňku Power Pivot.
Propojením tabulek pomocí relací dojde ke zvětšení oboru, neboli kontextu, ve kterém se vzorce vyhodnocují. Na vzorce v kontingenční tabulce můžou mít vliv třeba i filtry nebo záhlaví sloupců nebo řádků v dané kontingenční tabulce. Můžete vytvářet vzorce, které manipulují s kontextem, kontext ale může způsobit změnu výsledků nepředvídatelným způsobem. Další informace najdete v tématu Kontext ve vzorcích jazyka DAX.

Aktualizace výsledků vzorců

Aktualizace dat a přepočet jsou dvě samostatné, ale související operace, kterým byste měli porozumět při navrhování datového modelu obsahujícího složité vzorce, velké objemy dat nebo data získaná z externích zdrojů dat.

Aktualizace dat je proces aktualizace dat sešitu novými daty z původního externího zdroje dat. Můžete také data obnovit ručně v zadaných intervalech. Pokud jste již publikovali sešit na web služby SharePoint, můžete naplánovat automatickou aktualizaci dat z externích zdrojů.

Přepočet je proces aktualizace výsledků vzorců, který odráží všechny změny v samotných vzorcích a také změny v podkladových datech. Přepočet může mít vliv na výkon následujícími způsoby:

Pro počítaný sloupec musí být při každé změně vzorce výsledek vzorce přepočítán pro celý sloupec.
Pro míru se výsledky vzorce nevypočítají, dokud se míra neumístí do kontextu kontingenční tabulky nebo kontingenčního grafu. Vzorec se přepočítá i při změně jakéhokoli záhlaví řádku nebo sloupce, které má vliv na filtry dat, nebo při ruční aktualizaci kontingenční tabulky.

Řešení potíží se vzorci

Chyby při psaní vzorců

Pokud se při definování vzorce zobrazí chyba, může vzorec obsahovat syntaktickou chybu, sémantickou chybu nebo chybu výpočtu.

Syntaktické chyby se řeší nejjednodušeji. Obvykle jde o chybějící závorku nebo čárku. Pokud hledáte pomoc se syntaxí jednotlivých funkcí, podívejte se do přehledu funkcí DAX.

S druhým typem chyby se setkáváme, když je syntaxe správná, ale hodnota odkazovaného sloupce nedává v kontextu vzorce smysl. Takové sémantické chyby a chyby výpočtů můžou mít následující příčiny:

Vzorec odkazuje na neexistující sloupec, tabulku nebo funkci.
Vypadá to, že je vzorec správně, ale když datový modul načte data, zjistí neshodu typu a vyvolá chybu.
Vzorec předává funkci nesprávný počet nebo typ parametrů.
Vzorec odkazuje na jiný sloupec, ve kterém je chyba, a proto jsou jeho hodnoty neplatné.
Vzorec odkazuje na sloupec, který není zpracovaný. To znamená, že obsahuje metadata, ale žádná skutečná data k použití ve výpočtech.

V prvních čtyřech případech DAX označí příznakem celý sloupec obsahující neplatný vzorec. V posledním případě DAX sloupec vyšedne a označí tak, že je sloupec v nezpracovaném stavu.

Nesprávné nebo neobvyklé výsledky při řazení nebo uspořádávání hodnot sloupce

Při řazení nebo uspořádávání sloupce, který obsahuje hodnotu Není číslo (NaN), se můžou zobrazit nesprávné nebo nečekané výsledky. Když například výpočet dělí hodnotu 0 hodnotou 0, vrátí se výsledek Není číslo.

Důvodem je, že modul vzorců provádí uspořádání a řazení porovnáním číselných hodnot, ale hodnota Není číslo se nedá porovnat s ostatními čísly ve sloupci.

Dosažení správných výsledků můžete zajistit použitím podmíněných příkazů využívajících funkci KDYŽ k testování hodnot Není číslo a vrácení číselné hodnoty 0.

Kompatibilita s tabulkovými modely služby Analysis Services a režimem DirectQuery

Obecně jsou vzorce jazyka DAX vytvořené v doplňku Power Pivot zcela kompatibilní s tabulkovými modely služby Analysis Services. Pokud ale provedete migraci modelu doplňku Power Pivot do instance služby Analysis Services a potom nasadíte tento model v režimu DirectQuery, projeví se určitá omezení.

Některé vzorce jazyka DAX mohou vrátit odlišné výsledky, pokud model nasadíte v režimu DirectQuery.
Při nasazení modelu v režimu DirectQuery mohou některé vzorce způsobit chyby ověření, protože vzorec obsahuje funkci jazyka DAX, která není podporována v relačním zdroji dat.

Další informace naleznete v dokumentaci k modelování tabulek ve službě Analysis Services na webu Knihy online pro SQL Server 2012.