Jak na Excel Obsah cvičení DAX - RELATED vs RELATEDTABLE a LOOKUPVALUE Pavel Lasák "Lektor, expert na Microsoft Excel, držitel prestižního ocenění Microsoftu MVP v České republice" 5002722 Další informace ke cvičení: https://office.lasakovi.com/excel/PowerPivot/Uvod-do-jazyka-DAX-Excel-Power-Pivot/ "Copyright, Pavel Lasák 2018 rev 2019" ##### Sheet/List 2 ##### "DAX Výpočty RELATED, RELATEDTABLE, LOOKUPVALUE" https://office.lasakovi.com RELATED Tip Vrátí přislušnou hodnotu z jiné tabulky LOOKUPVALUE RELATED() "column - název sloupce z tabulky, z kterého chcete přiřadit hodnotu" Tip: Podobá se trochu funkci SVYHLEDAT v Excel Pozor! Musí být mazi tabulkami vazba! RELATED - pro vazbu 1 to 1 Funguje i v Power BI Nad sloupcem =RELATED(Vyrobky_[Cena]) =RELATED(Vyrobky_[Jméno výrobek]) RELATEDTABLE Vyhodnotí výraz tabulky v kontextu modifikovaném danými filtry. RELATEDTABLE() tableName - Název existující tabulky pomocí standardní syntaxe DAX. Nemůže to být výraz. RELATEDTABLE - pro vazbu 1 to * Ve spojení s dalšími funkcemi třeba SUMX Pozor! Musí být mazi tabulkami vazba! Nad sloupcem =SUMX(RELATEDTABLE(Prodeje_);Prodeje_[Počet prodejů]) LOOKUPVALUE "LOOKUPVALUE( , , [, , ]…[, ]) " "result_columnName - Sloupce, který obsahuje hodnotu, kterou chci vrátit. Sloupec musí být pojmenován pomocí standardní syntaxe DAX, obvykle plně kvalifikované. Nemůže to být výraz." "search_columnName Sloupec, ve stejné tabulcenad níž se provádí vyhledávání. " "search_value Skalární výra (sloupec), který neodkazuje na žádný sloupec - je ve stejné prohledávané" "alternateResult (Volitelné) Hodnota vrácená, pokud nenalezeno nebo filtrováno na nulovou hodnotu nebo chybu a když je více než jedna odlišná hodnota. Pokud není zadána, vrátí funkce BLANK ()," Poznámka - připomíná funkci SVYHLEDAT s IFFEROR z Excel Nad sloupcem v taulce Prodeje =LOOKUPVALUE(Vyrobky_[Cena];Vyrobky_[ID];[ID]) =LOOKUPVALUE(Vyrobky[Cena];Vyrobky[ID];[ID]) "tip: Nemusí být vazba, jen tabulka musí být v Power Pivot" Lze mít i jako míru V tabulce Vyrobky Počet prodejů suma:=SUMX(Prodeje_;LOOKUPVALUE(Vyrobky_[Cena];Vyrobky_[ID];[ID])*[Počet prodejů]) "LOOKUPVALUE( Tabulka se sloupce který vrací, Tabulka s prohledavanym sloupcem , k hodntě z kterého slouce se přiřadí) " ##### Sheet/List 3 ##### DAX Výpočty https://office.lasakovi.com Zdrojová data Prodeje Výrobky Kategorie Sklad Roky ID Kód roku Počet prodejů Pomoc cena Obrat_pomoc ID Jméno výrobek Kategorie Hmotnost Schválení Cena Kategorie Odpovědnost Sklad Sklad Stát Město Ulice Čas Kód roku Rok 1 R_2019 5 10 50 1 Název 1 K_A 100 4/17/2019 10 K_A Eva P P ČR Praha Krátká 5:00 R_2019 2019 2 R_2019 1 20 20 2 Název 2 K_A 100 4/18/2019 20 K_B Iva B B ČR Brno Dlouhá 10:00 R_2020 2020 1 R_2020 2 10 20 3 Název 3 K_B 110 1/1/2020 10 K_C Jan B K SR Košice Hlavní 7:00 R_2021 2021 2 R_2020 5 20 100 4 Název 4 K_B 150 1/1/2018 20 K_D Ivo V T SR Trnava Vedlejší 10:00 R_2022 2022 3 R_2020 2 10 20 5 Název 5 K_A 100 5/5/2020 30 4 R_2019 1 20 20 6 Název 6 K_B 110 5/17/2019 40 5 R_2020 1 30 30 7 Název 7 K_C 150 4/17/2020 50 Úkol "Potřebuji prodejní cenu výrobků do tabulky prodeje. Nebo-li vím, že k cizímu klíči ve sloupci Id v tabulce prodeje, potřebuji dotáhnout cenu z tabulky Výrobky přes primární klíč Id." Poznámka: Pro kontrolu je v pomocném sloupci. "Potřebuji počet prodejů do tabulky Výrobky. Zde ale vím, že prodejů každého výrobků může být více. Proto budu muset použít funkci RELATEDTABLE." ID Kód roku Počet prodejů ID Jméno výrobek Kategorie Hmotnost Schválení Cena Počet prodejů Počet prodejů celkem 1 R_2019 5 1 Název 1 K_A 100 4/17/2019 10 5 7 2 R_2019 1 1 2 1 R_2020 2 2 Název 2 K_A 100 4/18/2019 20 1 6 2 R_2020 5 2 5 3 R_2020 2 3 Název 3 K_B 110 1/1/2020 10 2 2 4 R_2019 1 4 Název 4 K_B 150 1/1/2018 20 1 1 5 R_2020 1 5 Název 5 K_A 100 5/5/2020 30 1 1 6 Název 6 K_B 110 5/17/2019 40 nenalezen 0 7 Název 7 K_C 150 4/17/2020 50 nenalezen 0 ##### Sheet/List 4 ##### ID Jméno výrobek Kategorie Hmotnost Cena Prodeje ID Kód roku Počet prodejů Součet prodeje ID Jméno výrobek Kategorie Hmotnost Cena ID Kód roku Počet prodejů cena 1 Název 1 K_A 100 10 7 1 R_2019 5 7 1 Název 1 K_A 100 10 1 R_2019 5 10 1 R_2020 2 2 Název 2 K_A 100 20 2 R_2019 1 20 2 Název 2 K_A 100 20 6 2 R_2019 1 6 3 Název 3 K_B 110 10 1 R_2020 2 10 2 R_2020 5 4 Název 4 K_B 150 20 2 R_2020 5 20 3 Název 3 K_B 110 10 2 3 R_2020 2 2 5 Název 5 K_A 100 30 3 R_2020 2 10 4 Název 4 K_B 150 20 1 4 R_2019 1 1 6 Název 6 K_B 110 40 4 R_2019 1 20 5 Název 5 K_A 100 30 1 5 R_2020 1 1 7 Název 7 K_C 150 50 5 R_2020 1 30 6 Název 6 K_B 110 40 7 Název 7 K_C 150 50 ##### Sheet/List 5 ##### ID Kód roku Počet prodejů Pomoc cena Obrat_pomoc ID Jméno výrobek Kategorie Hmotnost Schválení Cena 1 R_2019 5 10 50 1 Název 1 K_A 100 4/17/2019 10 2 R_2019 1 20 20 2 Název 2 K_A 100 4/18/2019 20 1 R_2020 2 10 20 3 Název 3 K_B 110 1/1/2020 10 2 R_2020 5 20 100 4 Název 4 K_B 150 1/1/2018 20 3 R_2020 2 10 20 5 Název 5 K_A 100 5/5/2020 30 4 R_2019 1 20 20 6 Název 6 K_B 110 5/17/2019 40 5 R_2020 1 30 30 7 Název 7 K_C 150 4/17/2020 50 Poznámky tabulky v řešení jsou pojmenovány Vyrobky_ a Prodeje_ A jsou v Power Pivot propojeny Tip můžete přidat i další tabulku pro otestování LOOKUPVALUE V tabulce výrobky =RELATED(Vyrobky_[Cena]) =RELATED(Vyrobky_[Jméno výrobek]) '=LOOKUPVALUE(Vyrobky_[Cena];Vyrobky_[ID];[ID]) V tabulce prodeje '=SUMX(RELATEDTABLE(Prodeje_);Prodeje_[Počet prodejů]) ##### Sheet/List 6 ##### ID Jméno výrobek Kategorie Hmotnost Cena Měsíc Datum 1 Název 1 K_A 100 10 Březen 3/15/2020 2 Název 2 K_A 100 20 Březen 3/16/2020 3 Název 3 K_B 110 10 Březen 3/17/2020 4 Název 4 K_B 150 20 Březen 3/18/2020 5 Název 5 K_A 100 30 Březen 3/19/2020 6 Název 6 K_B 110 40 Březen 3/20/2020 7 Název 7 K_C 150 50 Březen 3/21/2020 ##### Sheet/List 7 ##### ID Jméno výrobek Kategorie Hmotnost Cena Měsíc Datum 1 Název 1 K_A 100 10 Duben 4/15/2020 2 Název 2 K_A 100 20 Duben 4/16/2020 3 Název 3 K_B 110 10 Duben 4/17/2020 4 Název 4 K_B 150 20 Duben 4/18/2020 5 Název 5 K_A 100 30 Duben 4/19/2020 6 Název 6 K_B 110 40 Duben 4/20/2020 7 Název 7 K_C 150 50 Duben 4/21/2020 ##### Sheet/List 8 ##### Hledám: "Množství prodejů (tabulka Prodeje, sloupec Počet prodejů) v kategorií A K_A (v tabulce Kategorie sloupec Kategorie)" Výsledek: Roky Kategorie Sklad Kód roku Rok Kategorie Odpovědnost Sklad Sklad Město R_2019 2019 K_A Eva P * 1 P Praha R_2020 2020 K_B Iva B B Brno 1 K_C Jan B Prodeje 1 ID Kód roku Počet prodejů Výrobky 1 R_2019 5 ID Jméno výrobek Kategorie Hmotnost Cena 2 R_2020 1 1 Název 1 K_A 100 10 1 R_2019 2 * 2 Název 2 K_A 100 20 2 R_2020 5 3 Název 3 K_B 110 10 3 R_2020 2 * 4 Název 4 K_B 150 20 4 R_2019 1 5 Název 5 K_A 100 30 5 R_2020 1 6 Název 6 K_B 110 40 * 1 7 Název 7 K_C 150 50