V tomto článku se podíváme na základy vytváření vzorců pro počítané sloupce i míry v Power Pivotu. Pokud s jazykem DAX teprve začínáte, určitě si přečtěte článek Rychlý start: Naučte se základy jazyka DAX za 30 minut.
Základy vzorců
Power Pivot nabízí výrazy DAX (Data Analysis Expressions) pro vytváření vlastních výpočtů v tabulkách Power Pivot a v kontingenčních tabulkách Excelu. Jazyk DAX obsahuje některé funkce, které se používají v excelových vzorcích, a další funkce, které jsou navrženy pro práci s relačními daty a provádění dynamických agregací.
Tady je několik základních vzorců, které se dají použít ve sloupci s výpočty:
| Vzorec | Popis |
|---|---|
| =DNES(): | Vloží do každého řádku sloupce dnešní datum. |
| =3 | Vloží hodnotu 3 do každého řádku sloupce. |
| =[Sloupec1] + [Sloupec2] | Sečte hodnoty ve stejném řádku polí [Sloupec1] a [Sloupec2] a výsledky vloží do stejného řádku počítaného sloupce. |
Vzorce PowerPivotu pro počítané sloupce můžete vytvářet podobně jako vzorce v Microsoft Excelu.
Při vytváření vzorců postupujte takto:
- Každý vzorec musí začínat symbolem rovná se.
- Můžete zadat nebo vybrat název funkce nebo zadat výraz.
- Začněte zadávat několik prvních písmen požadované funkce nebo názvu a automatické dokončování zobrazí seznam dostupných funkcí, tabulek a sloupců. Stisknutím klávesy TAB přidáte do vzorce položku ze seznamu automatického dokončování.
- Kliknutím na tlačítko Fx zobrazíte seznam dostupných funkcí. Chcete-li vybrat funkci z rozevíracího seznamu, pomocí kláves se šipkami zvýrazněte položku a poté klikněte na tlačítko OK přidat funkci do vzorce.
- Zadejte argumenty funkce tak, že je vyberete z rozevíracího seznamu možných tabulek a sloupců, nebo zadáte hodnoty nebo jinou funkci.
- Zkontrolujte syntaktické chyby: zkontrolujte, jestli jsou všechny závorky zavřené a jestli jsou správně odkazované sloupce, tabulky a hodnoty.
- Potvrďte vzorec stisknutím klávesy ENTER.
Poznámka
Jakmile vzorec v počítaném sloupci přijmete, sloupec se zaplní hodnotami. Stisknutím klávesy ENTER v míře uložíte definici míry.
Vytvoření jednoduchého vzorce
| Vytvoření počítaného sloupce pomocí jednoduchého vzorce Datum prodejePodkategorieVýrobekProdejMnožství1/5/2009PříslušenstvíPřenosný kufřík254995681/5/2009PříslušenstvíMini nabíječka baterií1099.56441/5/2009DigitálníTenký digitální6512441/6/2009PříslušenstvíTeleobjektiv na konverzi1662.5181/6/2009PříslušenstvíStativ938.34181/6/2009PříslušenstvíUSB kabel1230.2526
|
|---|
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.
- Power Pivot nepřidává pravé závorky funkcí ani je automaticky nepáruje. Je třeba zkontrolovat, zda je každá funkce syntakticky správně, jinak vzorec nelze uložit ani použít. Power Pivot zvýrazňuje závorky, aby bylo snadnější zkontrolovat, jestli jsou správně zavřené.
Práce s tabulkami a sloupci
Tabulky Power Pivot vypadají podobně jako tabulky Excelu, ale liší se ve způsobu práce s daty a se vzorci:
- Vzorce v Power Pivotu fungují jenom s tabulkami a sloupci, ne s jednotlivými buňkami, odkazy na oblasti nebo poli.
- Vzorce mohou pomocí relací získat hodnoty ze souvisejících tabulek. Načítané hodnoty se vždy vztahují k hodnotě aktuálního řádku.
- Vzorce Power Pivotu nejdou vložit do excelového listu a naopak.
- Nemůžete mít nepravidelná nebo chaotická data jako v excelovém listu. Každý řádek v tabulce musí obsahovat stejný počet sloupců. V některých sloupcích ale mohou být prázdné hodnoty. Excelové tabulky dat a Power Pivot nejsou vzájemně zaměnitelné, ale excelové tabulky se dají propojit z PowerPivotu a vložit do něj excelová data. Další informace najdete v tématech Přidání dat listu do datového modelu pomocí propojené tabulky a Kopírování a vkládání řádků do datového modelu v Power Pivotu.
Odkazování na tabulky a sloupce ve vzorcích a výrazech
Na libovolnou tabulku nebo sloupec můžete odkazovat pomocí jeho názvu. Následující vzorec například ukazuje, jak odkazovat na sloupce ze dvou tabulek pomocí plně kvalifikovaného názvu:
=SUMA('Nové prodeje'[Částka]) + SUMA('Minulé prodeje'[Částka])
Při vyhodnocování vzorce Power Pivot nejprve zkontroluje obecnou syntaxi a potom porovná zadané názvy sloupců a tabulek s možnými sloupci a tabulkami v aktuálním kontextu. Pokud je název nejednoznačný nebo pokud se sloupec nebo tabulka nedá najít, zobrazí se chyba ve vzorci (v buňkách, ve kterých k chybě dochází #ERROR řetězec, místo datové hodnoty). Další informace o požadavcích na pojmenování tabulek, sloupců a dalších objektů najdete v tématu Požadavky na pojmenování v jazyce DAX – specifikace syntaxe pro Power Pivot.
Poznámka
Kontext je důležitou funkcí datových modelů Power Pivotu, která umožňuje vytvářet dynamické vzorce. Kontext je určen tabulkami v datovém modelu, relacemi mezi tabulkami a případnými použitými filtry. Další informace najdete v tématu Kontext ve vzorcích jazyka DAX.
Relace tabulek
Tabulky lze propojit s jinými tabulkami. Vytvořením relací získáte možnost vyhledávat data v jiné tabulce a provádět složité výpočty pomocí souvisejících hodnot. Počítaný sloupec můžete například použít k vyhledání všech záznamů o dopravě souvisejících s aktuálním prodejcem a následnému součtu nákladů na dopravu pro každý z nich. Výsledek je podobný parametrizovanému dotazu: pro každý řádek v aktuální tabulce můžete vypočítat jiný součet.
Mnoho funkcí jazyka DAX vyžaduje existenci relace mezi tabulkami nebo mezi několika tabulkami, aby bylo možné najít odkazované sloupce a vrátit smysluplné výsledky. Jiné funkce se pokusí tento vztah identifikovat. V zájmu dosažení nejlepších výsledků byste však měli vždy vytvořit relaci tam, kde je to možné.
Při práci s kontingenčními tabulkami je obzvláště důležité propojit všechny tabulky, které se v kontingenční tabulce používají, aby bylo možné souhrnná data vypočítat správně. Další informace naleznete v tématu Práce s relacemi v kontingenčních tabulkách.
Odstraňování chyb ve vzorcích
Pokud se při definování počítaného sloupce zobrazí chyba, může vzorec obsahovat syntaktickou nebo sémantickou chybu.
Syntaktické chyby se řeší nejjednodušeji. Obvykle jde o chybějící závorku nebo čárku. Nápovědu k syntaxi jednotlivých funkcí naleznete v tématu Informace o funkcích jazyka 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 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ž Power Pivot 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ý nebyl zpracován. K tomu může dojít, pokud jste změnili sešit na ruční režim, provedli změny a pak nikdy neaktualizovali data nebo výpočty.
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.