DAX funkce LOOKUPVALUE() je velmi podobná funkci SVYHLEDAT() v Excelu. Funkce LOOKUPVALUE() se nejčastěji používá k přidání hodnot z jedné tabulky do druhé tabulky, kdy mezi těmito tabulkami neexistuje relace a nemůžeme tak použít funkci RELATED(). V tomto příspěvku si na jednoduchých příkladech vysvětlíme jak se funkce LOOKUPVALUE() používá.
K tomuto tématu je k dispozici také video:
DAX funkce LOOKUPVALUE() vrací aktuální hodnotu ze sloupce z prohledávané tabulky, která odpovídá podmínce ve druhém a třetím argumentu. Pokud potřebujeme, můžeme volitelně přidat více podmínek v dalších argumentech, vždy v páru prohledávaný sloupec a hledaná hodnota. Syntaxe funkce LOOKUPVALUE() vypadá následovně.
Syntaxe funkce LOOKUPVALUE:
Prvním argumentem funkce je název sloupce, ze kterého chceme získat výslednou hodnotu. Výsledná hodnota bude ze stejného řádku prohledávané tabulky, ve kterém bude hodnota z prohledávaného sloupce ve druhém argumentu funkce odpovídat hledané hodnotě ve třetím argumentu funkce. Hledaná hodnota ve třetím argumentu funkce může být načtena ze sloupe na základě aktuálního kontextu řádku, ve kterém je funkce vyvolána. Dále můžeme ve třetím argumentu funkce použít konstantu nebo jinou funkci vracející v aktuálním kontextu skalární hodnotu.
První tři argumenty jsou povinné. Volitelně můžeme přidat další podmínky, případně můžeme definovat alternativní výsledek. Výsledkem funkce LOOKUPVALUE() bude alternativní výsledek v situaci, kdy definovaným podmínkám odpovídá více rozdílných hodnot ve sloupci s výsledkem anebo v případě, kdy podmínkám nebude odpovídat žádná hodnota ve sloupci v prvním argumentu funkce.
Pokud nepoužijeme možnost alternativního výsledku a podmínkám definovaným ve funkci neodpovídá žádná hodnota ve sloupci v prvním argumentu funkce, výsledkem bude prázdná hodnota BLANK. Na druhou stranu, pokud bude podmínkám odpovídat více rozdílných hodnot, výsledkem funkce bude chybová hláška ERROR. V případě vynechání argumentu s alternativním výsledkem tak musíme mít jistotu, že zadaným podmínkám odpovídá vždy jedna výsledná hodnota.
LOOKUPVALUE() můžeme použít pro vytvoření nového počítaného sloupce nebo v iteračních funkcích uvnitř měřítek. Pokud funkci používáme při vytváření měřítek, LOOKUPVALUE() ignoruje případný kontext filtru v prohledávané tabulce.
V následující části příspěvku si ukážeme jednoduché příklady použití funkce LOOKUPVALUE() v počítaném sloupci a v měřítku. Všechny příklady jsou vytvořeny ve cvičném Power BI souboru Adventure Works DW 2020.pbix. Soubor s řešením je k dispozici ke stažení níže pod tímto příspěvkem.
Příklady použití funkce LOOKUPVALUE v Power BI
Funkce LOOKUPVALUE() se nejčastěji používá v případě chybějící relace mezi tabulkami. Uvažujme například model, ve kterém mezi tabulkou 'Sales' a tabulkou 'Product' není vytvořena relace.
Tabulka 'Sales' obsahuje informace o prodejích produktů, kdy každý produkt se přirozeně mohl prodat vícekrát. Jaký produkt se aktuálně prodal je v tabulce 'Sales' uvedeno ve sloupci 'Sales'[ProductKey].
Tabulka 'Product' pak obsahuje popisné informace o jednotlivých produktech, a každý produkt má v této dimenzní tabulce jeden záznam, včetně sloupce 'Product'[ProductKey], což je jedinečný identifikátor každého produktu.
Pokud bychom chtěli v takto vytvořeném modelu přidat nějaký atribut z tabulky 'Product' do tabulky 'Sales', můžeme použít funkci LOOKUPVALUE() následujícím způsobem.
Počítaný sloupec v tabulce Sales:
Prvním argumentem funkce LOOKUPVALUE() je název sloupce, ze kterého chceme získat výslednou hodnotu - v našem příkladu sloupec s barvami produktů. Druhým argumentem je název sloupce z prohledávané tabulky, ve kterém chceme najít hodnotu ze třetího argumentu funkce.
Jinými slovy funkce LOOKUPVALUE() zafiltruje tabulku 'Product' prostřednictvím sloupce 'Product'[ProductKey] podle aktuální hodnoty ve sloupci 'Sales'[ProductKey], a následně v takto zafiltrované tabulce načte aktuální hodnotu ze sloupce 'Product'[Color].
K výše uvedenému příkladu je třeba doplnit dvě důležité poznámky.
Druhým argumentem funkce LOOKUPVALUE() je sloupec 'Product'[ProductKey], což je primární klíč tabulky 'Product'. Protože prohledáváme sloupec, který je současně primární klíč v prohledávané tabulce, máme v tomto příkladu jistotu, že výsledkem funkce LOOKUPVALUE() bude pouze jedna hodnota.
Tím se posouváme k druhé důležité informaci, a to k faktu že mezi tabulkou 'Sales' a tabulkou 'Product' můžeme vytvořit relaci 1:M, právě na základě sloupců 'Product'[ProductKey] a 'Sales'[ProductKey].
Jakmile máme vytvořenou relaci, je daleko jednodušší a efektivnější použít pro načtení hodnot z navázané tabulky funkci RELATED().
Počítaný sloupec:
Pokud existují mezi tabulkami relace, můžeme se vyhnout použití funkce LOOKUPVALUE() a požadované hodnoty získat i jednodušeji a efektivněji z pohledu výkonu pomocí funkce RELATED().
Tento úvodní jednoduchý příklad tedy sloužil pouze pro vysvětlení toho, jak funkce LOOKUPVALUE() funguje.
Funkce LOOKUPVALUE() se ale nejčastěji používá v situacích, kdy nemůžeme nebo nechceme mezi dvěma tabulkami vytvořit relaci, což si ukážeme v následujícím příkladu.
Funkce LOOKUPVALUE() je dále velmi užitečná při materializaci relace mezi jednou a tou samou tabulkou. Příklad použití funkce LOOKUPVALUE() pro vytvoření tzv. self-relace je popsán v samostatném příspěvku pod tímto odkazem.
Vyhledání hodnoty na základě více podmínek pomocí LOOKUPVALUE
Typickým příkladem ve kterém je užitečná funkce LOOKUPVALUE() je konverze měn. V tabulce 'Sales' jsou uloženy prodeje v USD měně. Zjednodušená tabulka pro převod USD do EUR nebo CZK, se kterou budeme v příkladu pracovat, vypadá následovně.
Tabulka s kurzy obsahuje kurz pro každou měnu a rok, ve kterém došlo k prodejům produktů. Jedná se tedy o zjednodušenou kurzovou tabulku. V reálném modelu bychom zřejmě pracovali s kurzy na denní úrovni, nicméně na principu použití funkce LOOKUPVALUE() se nic nemění.
Důležité pro nás je že tabulka s kurzy neobsahuje sloupec s jedinečnými hodnotami, který bychom mohli použít pro vytvoření relace s tabulkou 'Sales'. To je přesně situace, kdy může být užitečná funkce LOOKUPVALUE().
Naším úkolem tedy bude vytvořit v tabulce 'Sales' dva nové počítané sloupce, kdy první bude obsahovat prodeje převedené do měny CZK, a druhý prodeje převedené do EUR. Abychom dokázali načíst správný kurz pro převod částky, budeme potřebovat ve funkci LOOKUPVALUE() definovat dvě podmínky, jednu pro vybranou měnu a druhou pro rok, ve kterém došlo k prodejům produktů.
V tabulce 'Sales' ale nemáme k dispozici sloupec s roky a ani sloupec s měnami. Rok prodejů si můžeme načíst z tabulky 'Date' pomocí funkce RELATED(). Měnu, do které chceme převést aktuální prodeje pak můžeme definovat jako konstantu.
Počítaný sloupec v tabulce Sales:
Výsledkem funkce LOOKUPVALUE() je v každém řádku tabulky kurz v EUR měně, který odpovídá roku, ve kterém došlo k uskutečnění objednávky. Tímto kurzem v každém řádku nového počítaného sloupce vynásobíme původní hodnotu prodejů v USD, která je v tabulce 'Sales' ve sloupci 'Sales'[Sales Amount].
Pokud bychom chtěli vytvořit jiný počítaný sloupec, který bude obsahovat hodnoty prodejů v přepočtu na CZK, výpočet bude velmi podobný, pouze s jinou zkratkou měny v proměnné Mena.
Počítaný sloupec v tabulce Sales:
Stejně jako u CZK měny, i nyní musíme ve funkci LOOKUPVALUE() použít dvě podmínky, abychom ze sloupce 'Kurzovní lístek'[Kurz] získali jednu výslednou hodnotu s kurzem v aktuálním roce. První podmínkou je aktuální rok vzniku objednávky, druhou podmínkou je zvolená měna.
Výsledkem jsou dva nové počítané sloupce, které obsahují hodnotu prodejů v aktuálním řádku přepočítanou kurzem vybrané měny ke konci aktuálního roku, ve kterém došlo k prodeji.
Při práci s funkcí LOOKUPVALUE() je důležité vytvořit takové podmínky, které zajistí že za těchto podmínek bude sloupec v prvním argumentu obsahovat pouze jednu hodnotu.
Pokud se vrátíme k naší tabulce s kurzy, tak při práci s touto tabulkou nám jsme pro získání jedné hodnoty ze sloupce 'Kurzovní lístek'[Kurz] definovat dvě podmínky.
Pokud bychom pracovali ve funkci LOOKUPVALUE() například pouze s roky, dostali bychom v každém roce dva kurzy, jeden pro každou měnu.
Na druhou stranu, pokud bychom pracovali pouze se zkratkou měn, dostali bychom pro každou měnu čtyři kurzy.
Pokud nemáme jistotu, že pro zadané podmínky existuje v prohledávané tabulce pouze jedna hodnota ve výsledném sloupci, můžeme v posledním argumentu této funkce vyplnit alternativní výsledek. Alternativní výsledek bude funkce LOOKUPVALUE() vracet v případě, kdy zadaným podmínkám neodpovídá žádná hodnota nebo v případě kdy zadaným podmínkám odpovídá více hodnot.
Pokud zadáváme alternativní výsledek ve funkci LOOKUPVALUE() v počítaném sloupci, musíme si dát pozor na to, aby alternativní výsledek měl stejný datový typ jako sloupec v prvním argumentu funkce LOOKUPVALUE(), protože každý sloupec musí mít v každém řádku tabulky hodnotu ve stejném formátu.
Jak můžeme vidět na obrázku výše, ve funkci LOOKUPVALUE() prohledáváme tabulku 'Kurzovní lístek' a hledáme aktuální kurz pouze na základě měny. Protože máme v tabulce 'Kurzovní lístek' čtyři kurzy pro měnu CZK, jeden kurz pro každý rok, funkce LOOKUPVALUE() nemůže vrátit jednu hodnotu, a proto je výsledkem alternativní výsledek zadaný ve čtvrtém argumentu.
Pokud bychom nezadali alternativní výsledek, dostaneme v případě nalezení více hodnot ve sloupci 'Kurzovní lístek'[Kurz] chybu.
Poslední situace, která může nastat s funkcí LOOKUPVALUE(), je situace kdy zadaným podmínkám neodpovídá žádná hodnota v prohledávané tabulce. Pokud zadaným podmínkám neodpovídá žádná hodnota a nezadáme alternativní výsledek, výsledkem funkce LOOKUPVALUE() bude prázdná hodnota BLANK.
Funkci LOOKUPVALUE() můžeme používat také v měřítku, typicky v rámci iteračních funkcí. V následující části příspěvku si proto ukážeme příklad, jak vytvořit měřítko s prodeji ve vybrané měně bez nutnosti tvořit počítaný sloupec v tabulce 'Sales'.
Příklad použití funkce LOOKUPVALUE v měřítku
Pokud bychom chtěli v použitém modelu převést prodeje z originální USD měny například do CZK měny, nemusíme tvořit pro tento účel nový počítaný sloupec, ale celou logiku můžeme přenést přímo do měřítka, například následujícím způsobem.
Měřítko:
V měřítku [Prodeje (CZK)] je aplikována stejná logika, kterou jsme použili v počítaném sloupci pro převod částek prodejů do CZK měny. Tato logika je ale aplikována ve druhém argumentu funkce SUMX(), kde pro každý řádek v tabulce 'Sales' načítáme aktuální kurz z tabulky 'Kurzovní lístek', a tímto kurzem následně násobíme originální částku prodejů v USD uloženou ve sloupci 'Sales'[Sales Amount].
Nové měřítko bude vracet stejné výsledky, jako měřítko se sumou hodnot z dříve vytvořeného počítaného sloupce 'Sales'[Prodeje (CZK)].
Funkce LOOKUPVALUE je syntaktická zkratka
Pro úplnost je třeba doplnit, že funkce LOOKUPVALUE() je syntaktická zkratka pro složitější zápis pomocí funkce CALCULATE().
Měřítko [Prodeje (CZK)] by na pozadí mohlo být pomocí funkce CALCULATE() interpretováno následovně.
Měřítko:
Pokud bychom ve funkci LOOKUPVALUE() zadali alternativní výsledek, například konstantu 25, zápis s použitím funkce CALCULATE() by mohl vypadat následovně.
Měřítko:
Použití dvou znamének rovná se ve funkci FILTER() znázorňuje, že funkce LOOKUPVALUE() rozlišuje mezi prázdnou STRING hodnotou a hodnotou BLANK, stejně jako mezi číslem 0 a hodnotou BLANK. Použití funkce ALLNOBLANKROW() namísto funkce ALL() je pak z důvodu vyhnutí se cyklické závislosti v počítaný sloupcích.
Všechna tři měřítka budou v našem příkladu vracet stejné výsledky.
Shrnutí
Funkce LOOKUPVALUE() je užitečná funkce, která může nahradit chybějící relace v modelu. Na druhou stranu vždy, když existuje mezi prohledávanou tabulkou a tabulkou, do které chceme hodnoty přidat odpovídající relace, můžeme namísto funkce LOOKUPVALUE() použít funkci RELATED().
Funkci LOOKUPVALUE() můžeme využít v případech, kdy hledaná hodnota odpovídá více podmínkám a nemůžeme nebo nechceme vytvářet mezi tabulkami relaci na základě složených klíčů. Dále může být funkce LOOKUPVALUE() užitečná v situacích, kdy je hledaná hodnota ve stejné tabulce, do které chceme výsledek funkce přidat. Příkladem takovéhoto vztahu může být Parent-Child hierarchie.
Při použití funkce LOOKUPVALUE() bychom měli mít jistotu, že zadaným podmínkám bude v prohledávané tabulce odpovídat pouze jedna hodnota. V opačném případě může funkce vracet chybu, kterou můžeme nahradit alternativním výsledkem, který vkládáme do posledního argumentu funkce. V případě, že hledaným podmínkám neodpovídá žádná hodnota v prohledávané tabulce, výsledkem funkce bude hodnota BLANK, případně hodnota z alternativního výsledku, pokud tuto hodnotu zadáme.
Komentáře
Okomentovat