Rozšířené tabulky v LuckyTemplates

V tomto tutoriálu se podíváme na velmi specifický scénář, se kterým jste se jistě všichni již setkali při práci s jazykem DAX – rozšířenou tabulku . Rozšířené tabulky vám mohou pokazit výpočet, pokud nevíte, jak vlastně fungují. Celé video tohoto tutoriálu si můžete prohlédnout ve spodní části tohoto blogu.

V tomto příkladu používáme databázi Contoso, která obsahuje tabulku prodeje , tabulku zákazníků , tabulku data , tabulku produktů , tabulku kategorií produktů a tabulku podkategorií produktů .

Na levé straně máme vztah jedna k mnoha mezi produkty, kategorií, podkategorií a tabulkami prodeje. Máme také vztah one-to-many mezi zákazníkem a prodejem, stejně jako data a prodeje.

Obsah

• Příklad #1
• Příklad č. 2
• Červený prodej #1
• Červený prodej #2
• Červená prodej #3
• Ověřování rozbalených tabulek
• Definování rozšířených tabulek
• Rozdělení sloupce kategorie a použití rozbalených tabulek
• Vysvětlení červeného prodeje 2
• Vysvětlení červeného prodeje 3
• Použití DAX Studio k ověření rozšířených tabulek
• Závěr

Příklad #1

Řekněme, že chceme vytvořit sestavu z tohoto sloupce kategorie v tabulce kategorií a použít měřítko, které vypočítá, kolik zákazníků je v tabulce zákazníků. Chceme rozdělit číslo pro tabulku zákazníka podle kategorie.

Pokud dám filtr na tabulku kategorií, tento filtr přejde do tabulky podkategorií, pak se dostane do tabulky produktů a nakonec se dostane a vyfiltruje tabulku prodejů. Tento filtr však nebude schopen filtrovat tabulku zákazníka, pokud nezapneme obousměrné filtrování.

Vraťme se k přehledu, abychom tento výpočet opravili. Tento výpočet můžeme zabalit do funkce CALCULATE a poté napsat Prodej. Vidíme, že tentokrát neopakujeme stejné číslo pro každou buňku vizuálu.

Abychom ověřili, zda je tento výpočet správný, můžeme zavést zákaznický klíč z tabulky prodejů k agregaci a poté nad tím provést Count (Distinct).

Můžete vidět, že pro každý řádek vracíme stejnou hodnotu. Použitím prodejní tabulky uvnitř funkce CALCULATE se nám podařilo výpočet opravit.

Příklad č. 2

Přejděme k dalšímu příkladu. U tohoto chceme zjistit objem prodeje za červené produkty v roce 2007, 2008 nebo 2009. V závislosti na výběru kráječe se chci také vrátit o rok zpět.

Pokud například vyberu rok 2009, chci vykázat červené tržby za rok 2008. Pokud vyberu rok 2008, chci také vykázat červené tržby za rok 2007.

Červený prodej #1

Výpočet našich celkových tržeb je v podstatě součtem prodejních dat. V kontextu řádku vynásobíme množství čistou cenou.

Vytvořme novou míru a pojmenujeme ji jako Red Sales. Napíšeme VYPOČÍTAT a poté Celkové tržby. Jdeme na tabulku Prodeje, abychom ukázali, že barva produktů je rovna červené.

Poté data přepíšeme do sloupce datum.

Když tento výpočet přeneseme do vizuálu karty, dostaneme mezeru.

Červený prodej #2

Co se tady děje? Zkusme ten výpočet přepsat a uvidíme, jestli jsme schopni získat výsledek. Vytvoříme novou míru a pojmenujeme ji jako Red Sales 2 a použijeme první část výpočtu pro Red Sales 1.

Zahájíme další CALCULATE přes první CALCULATE.

Přenesme toto měření do matice a uvidíme výsledky. Pokud vybereme rok 2008, dostaneme 51 947. Pokud vybereme rok 2009, dostaneme 24 343. Nakonec, pokud vybereme rok 2010, dostaneme 39 724.

Červená prodej #3

Existuje další způsob, jak napsat tento výpočet. Napíšeme novou míru a nazveme ji Red Sales 3, poté použijeme funkci CALCULATE.

Vypočítáme celkové tržby a napíšeme, že u produktů se barva rovná červené. Na data pak použijte funkci SAMEPERIODLASTYEAR.

Pokud přetáhneme opatření č. 3 do vizuálu karty, můžete vidět, že tyto dvě hodnoty karet vracejí stejnou hodnotu, což je správné.

Ale v našem prvním výpočtu je něco špatně, protože vracíme mezeru a ne správnou hodnotu.

Pojďme se podívat, co se tady vlastně děje. Nyní, když jsme viděli několik výpočtů na rozšířených tabulkách, pojďme pochopit teorii, která se za nimi skrývá.

Než pochopíte, co je to rozšířená tabulka, musíte pochopit, že všechny tabulky, které zde máme, se nazývají základní tabulky .

Kdy se tedy tyto tabulky stanou rozšířenými tabulkami? Jakmile vytvoříte vztah mnoho ku jedné mezi jednou tabulkou a jinou tabulkou, základní tabulka se stane rozšířenou tabulkou.

Ověřování rozbalených tabulek

Jak ale můžeme ověřit, že k rozšíření tabulky skutečně dochází? Související klíčové slovo můžete použít na libovolné tabulce. Pokud budete mít přístup ke sloupci z jedné strany, budete vědět, že může dojít k rozšíření tabulky.

Přejdeme do tabulky prodejů a vytvoříme nový počítaný sloupec.

Řekněme, že chceme načíst barvu produktu z tabulky produktů pro tento konkrétní produktový klíč. Použijeme RELATED, který poskytne pouze seznam sloupců v IntelliSense, které lze skutečně rozbalit z tabulky prodejů.

Můžeme vidět, že tabulka zákazníků a tabulka prodejů mají vztah mnoho ku jedné. Můžeme také vidět seznam sloupců tabulky zákazníků, sloupce tabulky data a sloupce produktu.

Když vybereme Products[Color], můžeme pomocí klíčového slova RELATED vytvořit nový sloupec v tabulce prodejů. SOUVISEJÍCÍ nám poskytuje přístup pouze ke sloupcům tabulky, do kterých se může základní tabulka skutečně rozbalit.

Pokud změníme povahu tohoto vztahu z many-to-one na many-to-many, tento výpočet přestane fungovat.

Změňme povahu tohoto vztahu na mnoho-k-mnoho. Ve spodní části vidíme, že dostáváme varovný symbol.

Když se vrátíme zpět k tabulce prodejů, zobrazí se chybová zpráva, která říká, že sloupec Products[Color] buď neexistuje, nebo nemá vztah k žádné tabulce.

V současném kontextu není varování, které dostáváme, příliš uživatelsky přívětivé. V zásadě to znamená, že tabulka prodejů se nemůže rozšířit na tabulku produktů, protože nemáme přístup pouze k jedné jediné hodnotě pro tuto konkrétní položku řádku.

A protože používáme vztah many-to-many, nedojde k rozšíření tabulky a RELATED nefunguje.

Vraťme se k zobrazení diagramu a opravíme tento výpočet. Změníme povahu vztahu na mnoho ku jedné a aktivujeme tento vztah, aby výpočet fungoval.

Definování rozšířených tabulek

Než se pustíme do výpočtů, které jsme již provedli ve zprávě, zopakujme si definici rozšířené tabulky.

Pokud máte datový model s hvězdicovým schématem , tabulka faktů se rozšíří na všechny tabulky v datovém modelu, pokud mezi dimenzemi a tabulkou faktů existuje vztah mnoho ku jedné.

Pokud máte schéma sněhové vločky , pak se tabulka podkategorií produktu a tabulka kategorií rozšíří na základní tabulku, kterou je v tomto případě tabulka produktů. Prodejní stůl je základním stolem, který se rozšiřuje na všechny ostatní stoly.

Za scénou bude prodejní tabulka obsahovat všechny sloupce v jedné jediné tabulce. Všimněte si, že rozšíření tabulky je pouze logický koncept, takže se nebude rozšiřovat a zvětšovat velikost vašeho datového modelu.

Rozbalená tabulka se objeví pouze tehdy, když odkazujete na tabulku (což je základní tabulka) a když máte vztah mnoho ku jedné k jiným tabulkám.

Rozdělení sloupce kategorie a použití rozbalených tabulek

Pokusme se opravit výpočty, které jsme již provedli v zobrazení sestavy. V tomto příkladu provádíme řezy podle sloupce kategorie z tabulky kategorií produktů a snažíme se spočítat, kolik zákazníků existuje.

Tak co tu vlastně děláme? Jako referenci používáme rozšířenou prodejní tabulku. Jakmile kontext filtru obsahuje hodnotu z tabulky kategorií, tento filtr se dostane do tabulky prodeje z produktů podkategorie a přímo do prodeje.

Vzhledem k tomu, že prodej je rozšířená tabulka a tento odkaz používáme ve funkci CALCULATE, bude tabulka prodeje obsahovat také sloupec tabulky zákazníků. Když aplikujeme filtr na tabulku prodejů, nepřímo filtrujeme také tabulku zákazníků.

Vraťme se k našim červeným výpočtům prodejů a pokusme se pochopit, co se vlastně děje. Začneme prázdným výpočtem. Pokud tuto míru vybereme, můžete vidět, že píšeme vnořený kód. Máme VYPOČÍTAT, pak Celkové tržby, FILTROVAT přes tržby a SAMEPERIODLASTYEAR.

Rozeberme tento výpočet krok za krokem. Nejprve musíme identifikovat kontext externího filtru, který existuje mimo výpočet.

V kráječi Číslo kalendářního roku jsme vybrali rok 2008.

Z tohoto kontextu filtru bude vyhodnocena tabulka prodejů. Prodejní tabulka bude obsahovat pouze řádek pro číslo kalendářního roku 2008 a barva produktu bude červená. Máme zde dva filtry: jeden, který je vytvořen kontextem filtru , a jeden, který se vytváří pomocí filtru .

SAMEPERIODLASTYEAR se vyhodnocuje v kontextu filtru, kde je rok 2008. Obdrží seznam dat v roce 2008 a posune tato data v roce 2008 na rok 2007. Tabulka, která bude tímto vrácena, bude obsahovat pouze termíny pro rok 2007.

Jakmile jsou tyto dvě operace dokončeny, funkce CALCULATE připraví kontext filtru a aplikuje tyto dva filtry na kontext filtru. Když to použijeme, máme kontext filtru, kdy se barva produktu rovná červené, a ve sloupci rok máme filtr pro roky 2007 a 2008.

Pro tento datový model tedy neexistuje jediná transakce, která by existovala ve dvou různých letech. Když se CALCULATE pokusí sloučit tyto dva filtry ve stavu a , řekne, že rok by měl být 2008 a 2007 a že barva produktu by měla být červená.

Když filtrujeme tabulku prodeje s kontextem filtru roku 2008, nepřímo filtrujeme také tabulku data. Prodejní stáj má k datové stáji vztah mnoho ku jedné.

Vytvořme nový výpočet, abychom zjistili, kolik řádků je v tabulce dat pro související data. Napíšeme CALCULATE, pak tabulky dat. Poté FILTRUJEME VŠECHNY prodejní tabulky a řekneme, že SOUVISEJÍCÍ data v čísle kalendářního roku by se měla rovnat roku 2008.

Uvnitř CALCULATE jsme neodkazovali na žádný filtr přes tabulku dat. Jednoduše kontrolujeme, že data v čísle kalendářního roku by měla být z roku 2008. V ideálním případě by tento filtr neměl být schopen filtrovat tabulku dat. Používáme také funkci, která bude ignorovat kontext filtru, který přijde z průřezu.

Pro tento výpočet vytvoříme novou kartu. můžete vidět, že vracíme 348 řádků.

Jak je tedy možné, že z datové tabulky o 2500 řádcích vracíme pouze 348 řádků? Pokud budeme používat rozšířenou tabulku, pak budeme nepřímo filtrovat i druhou tabulku, která je propojena vztahem many-to-one.

I když pro aktuální rok nemáme žádný filtr, stále omezujeme počet řádků, které jsou viditelné pro tabulku dimenzí, kterou máme na jedné straně.

Když vracíme tabulku prodeje, vracíme také filtrovanou verzi tabulky dat, tabulky zákazníků, tabulky produktů, tabulky kategorií produktů a tabulky podkategorií produktů.

Vysvětlení červeného prodeje 2

Přejděme k dalšímu výpočtu, kterým je Red Sales 2. Začneme s vnějším CALCULATE, protože pokud tuto funkci vnořujeme do libovolného scénáře, vnější CALCULATE by měl připravit kontext filtru pro vnitřní CALCULATE.

Na sliceru vybereme kalendářní rok 2008. Když funkce SAMEPERIODLASTYEAR získá datum pro rok 2008, posune tato data v roce 2007 a bude to kontext FILTER pro vnitřní výpočet. Tento vnitřní CALCULATE vyhodnotí rok 2007.

Funkce FILTER bude filtrovat tabulku prodejů a tabulka prodejů bude omezena pouze na řádek, kde je rok 2007. Jakmile máme řádky za rok 2007, zkontrolujeme, zda jsou související produkty rovny červené.

Na rozdíl od prvního výpočtu nevracíme dvě různé úrovně. Tyto dvě různé úrovně nejsou v kontextu FILTER aplikovány, takže tabulka vrácená funkcí FILTER bude obsahovat celý řádek prodejní tabulky.

Po použití tohoto filtru získáme částku prodeje za rok 2007 a také produkty, které jsou rovny červené, zatímco v prvním výpočtu jsme vraceli rok 2008 a 2007.

Tentokrát jsme připravili kontext filtru pro prodejní tabulku pomocí funkce SAMEPERIODLASTYEAR a vnořením funkce CALCULATE do jiného CALCULATE.

Vysvětlení červeného prodeje 3

Ve třetím příkladu máme velmi jednoduchou funkci CALCULATE se dvěma příkazy: jedním je barva produktu se rovná červené a pak SAMEPERIODLASTYEAR na datech.

Funkce SAMEPERIODLASTYEAR bude vyhodnocena v kontextu filtru, který bude obsahovat pouze data roku 2007. Barva produktů použije červený kontext filtru na tabulku produktů, což bude následně filtrovat tabulku prodeje a bude obsahovat pouze řádky pro červené produkty.

Když jsou tyto dvě hodnoty použity v kontextu filtru, budou filtrovat tabulku prodeje, ale ne rozšířenou tabulku prodeje. můžete vidět, že nikde v kódu jsme neodkazovali na rozšířenou tabulku prodeje.

Toto je ideální scénář, který byste měli použít, pokud se pokoušíte odkazovat na rozšířenou tabulku. Při použití rozšířené tabulky mohou být výpočty skutečně spletité a někdy nejste schopni identifikovat, proč vaše výpočty vracejí nesprávný výsledek.

Pokud nerozumíte konceptu rozšířených tabulek, můžete tento konkrétní kód vyvinout jako modelové opatření a poté jej nasadit do produkce. Jiní uživatelé mohou začít vytvářet další míry nad touto mírou a nemusí být schopni zjistit, proč výpočty nefungují, protože nemají pouze takovou úroveň porozumění jazyku DAX.

Pokud pracujete s jazykem DAX, vždy se snažte umístit filtr přes jeden sloupec, protože když použijete jeden sloupec, koncept rozšířených tabulek se nepoužije. Když například použijete PRODUKTY [Barva] = „Červená“ , filtr dosáhne tabulky prodeje, ale tento filtr nemůže dosáhnout tabulky zákazníka, protože nepoužíváme rozšířenou tabulku prodeje.

Při použití jednoho sloupce neodkazujeme na koncept rozšířených tabulek. Jednoduše aplikujeme základní filtr na prodejní tabulku, která se nepřenese do ostatních tabulek.

Použití DAX Studio k ověření rozšířených tabulek

Než skončíme, použijeme DAX studio k ověření našeho kódu pro Red Sales 3. Přejděte na kartu Zobrazit, klikněte na Analyzátor výkonu a poté spusťte nahrávání. Obnovíme tento vizuál a vybereme prázdnou kartu.

Z externích nástrojů spustíme DAX studio

Vidíme, že na obrazovce výsledků ve spodní části se zobrazí prázdné místo.

Zapneme časování serveru, abychom porozuměli dotazům, které jsou generovány v zákulisí. Přejdeme na kartu Časování serveru a rozbalíme ji, abychom viděli vše.

Provádíme JOIN na prodejním stole na levé straně. Provádíme také JOIN na produktovém klíči z produktové tabulky.

V doložce WHERE se říká, že data v čísle kalendářního roku by se měla rovnat roku 2008 a barva produktu by měla být červená.

Pak máme další podmínku, která říká Dates[Date], poté následuje rozsah dat. Jaká jsou tedy tato data? V ideálním případě musí tato data patřit do roku 2008, ale jsou z roku 2007, kdy změníme formátování těchto čísel v Excelu.

Proto vracíme prázdnou hodnotu. Říkáme, že kalendářním rokem by měl být rok 2008 a za použití podmínky AND, že data by měla být v roce 2007. Tento rok 2007 je způsoben funkcí SAMEPERIODLASTYEAR, kterou jsme použili při výpočtu.

Změňme míru a odkaz na Red Sales 2. Jakmile spustíme kód, můžete vidět, že získáváme data z tabulky dat, poté filtrujeme sloupec data pro číslo kalendářního roku 2008.

Tentokrát jsme schopni vrátit výsledek, protože nepoužíváme filtr na klauzuli WHERE a říkáme, že data v čísle kalendářního roku by se měla rovnat roku 2008.

Závěr

Doufejme, že tento tutoriál vám pomohl pochopit, co je rozšířená tabulka a jak může zkazit vaše výpočty. Rozšířené tabulky nejsou opravdu intuitivní a nejsou ani snadno pochopitelné.

Pro více informací o rozšířených stolech se nezapomeňte přihlásit k odběru televizního kanálu LuckyTemplates. Neustále vychází obrovské množství obsahu ode mne a řady tvůrců obsahu, všichni se věnují zlepšování způsobu, jakým používáte LuckyTemplates a Power Platform.