Operátor IN ve funkci CALCULATE

Logický operátor IN můžeme mimo jiné použít pro sestavení filtru ve funkci CALCULATE(). Na levé straně logického operátoru se nachází sloupce, ve kterých chceme najít hodnoty z tabulky uvedené na pravé straně operátoru. Hodnot v tabulce na pravé straně může být více, což je hlavní rozdíl oproti standartním operátorům typu rovná se, nerovná se a atd., kde nastavujeme filtr vždy pouze na jednu hodnotu.

Příklad použití operátoru IN ve funkci CALCULATE

Příklady v tomto příspěvku jsou vytvořeny ve cvičném Power BI souboru Adventure Works DW 2020.pbix, který je volně dostupný ke stažení na internetu. Odkaz na stažení souboru s řešením můžete najít níže pod tímto příspěvkem.

Ve cvičném souboru si můžeme nejdříve vytvořit měřítko celkových prodejů, které bude sčítat hodnoty ze sloupce 'Sales'[Sales Amount], podle aktuálního kontextu vyhodnocení.

Měřítko:

Prodeje = SUM(Sales[Sales Amount])

Následují příklady použití operátoru IN pro vytvoření filtrů ve funkci CALCULATE().

Filtrování jednoho sloupce pomocí IN operátoru

Jednoduchý příklad měřítka s použitím operátoru IN pak může vypadat následovně.

Měřítko:

Prodeje černých a modrých produktů =

CALCULATE

(

SUM(Sales[Sales Amount]),

'Product'[Color] IN {"Black", "Blue"}

)

Na levé straně filtru je název sloupce obsahující barvy produktů, ve kterém hledáme hodnoty z tabulky uvedené na pravé straně operátoru IN. Tabulka na pravé straně operátoru IN je vytvořena pomocí konstruktoru tabulky. Konstruktor tabulky se v jazyku DAX sestavuje pomocí složených závorek {}. Obě nová měřítka můžeme vložit do tabulky s kategoriemi produktů v řádcích. 

Měřítko [Prodeje] vrací v každém řádku tabulky sumu prodejů za produkty v dané kategorii. Měřítko [Prodeje černých a modrých produktů] vrací v každém řádku tabulky hodnotu představující prodeje produktů v dané kategorii, ale pouze produktů v černé a modré barvě.

Stejně jako u ostatních logických filtru ve funkci CALCULATE(), také filtry vytvořené pomocí operátoru IN přepisují vnější filtry nastavené na stejný sloupec.

Měřítko [Prodeje černých a modrých produktů] nyní ignoruje vnější filtry z řádků tabulky a vrací vždy sumu prodejů za modré a černé produkty. Hodnota měřítka je v každém řádku tabulky stejná, protože logický filtr nastavený uvnitř měřítka [Prodeje černých a modrých produktů] přepíše všechny vnější filtry ze sloupce použitého pro logický filtr, které jsou aktivní v době vyhodnocení měřítka.

Operátor IN s funkcí KEEPFILTERS

Pokud bychom chtěli vyhodnotit filtr uvnitř měřítka v logickém AND vztahu s vnějšími filtry nastavenými na stejný sloupec, můžeme stejně jako u ostatních logických filtrů obalit výraz do funkce KEEPFILTERS().

Měřítko:

Prodeje černých a modrých produktů KEEPFILTERS =

CALCULATE

(

SUM(Sales[Sales Amount]),

KEEPFILTERS('Product'[Color] IN {"Black", "Blue"})

)

Nyní bude měřítko vyhodnoceno v logickém AND vztahu se všemi vnějšími filtry, včetně filtrů nastavených na stejný sloupec, který je součástí logického filtru explicitně definovaného uvnitř funkce CALCULATE().

Nové měřítko [Prodeje černých a modrých produktů KEEPFILTERS] vrací v prvním řádku tabulky sumu za prodeje černých produktů. Ve druhém řádku tabulky vrací měřítko sumu za prodeje modrých produktů. Pro ostatní barvy se filtry působící z řádků tabulky a filtr nastavený uvnitř měřítka vylučují, a výsledkem měřítka je hodnota BLANK. V řádku souhrnů výsledek měřítka představuje sumu prodejů pro modré a černé produkty.

Negace operátoru IN

Výsledek filtru můžeme také negovat pomocí operátoru NOT. Následující měřítko bude vracet sumu prodejů za všechny produkty v aktuálním kontextu vyhodnocení, kromě produktů v černé a modré barvě.

Měřítko:

Prodeje kromě černých a modrých produktů =

CALCULATE

(

SUM(Sales[Sales Amount]),

NOT 'Product'[Color] IN {"Black", "Blue"}

)

Pokud vložíme nové měřítko [Prodeje kromě černých a modrých produktů] do původní tabulky s kategoriemi produktů v řádcích, můžeme vidět, že hodnota měřítka [Prodeje] odpovídá součtu měřítek [Prodeje černých a modrých produktů]  a [Prodeje kromě černých a modrých produktů].

Operátor IN můžeme využít také pro vytvoření filtrů, které se skládají z více sloupců. Hledané hodnoty můžeme opět vkládat do konstruktoru tabulky, nicméně v konstruktoru tabulky budeme používat ještě konstruktor pro řádky tabulky.

Filtrování více sloupců pomocí IN operátoru

Pokud chceme prohledat hodnoty ve více sloupcích, tyto sloupce uvedeme do kulatých závorek na levou stranu operátoru IN. Na pravou stranu za operátor IN vložíme hodnoty, které budeme hledat v uvedených sloupcích. V případě konstruktoru tabulky musíme pro oddělení řádků použít konstruktor řádku, který se tvoří pomocí kulatých závorek uvnitř konstruktoru tabulky. Měřítko, které bude počítat sumu prodejů za modré produkty z kategorie kol a kategorie oblečení a sumu prodejů  černých produktů z kategorie oblečení a z kategorie kol v aktuálním kontextu vyhodnocení, může vypadat následovně.

Měřítko:

Prodeje produktů (černé, modré, kola, oblečení) =

CALCULATE

(

SUM(Sales[Sales Amount]),

('Product'[Color], 'Product'[Category])

IN

{

("Black" , "Bikes" ),

("Blue" , "Bikes" ),

("Black" , "Clothing"),

("Blue" , "Clothing")

}

)

Nové měřítko můžeme vložit do vizuálu tabulky s fiskálními roky v řádcích a s dalším měřítkem [Prodeje], které vrací sumu prodejů v aktuálním kontextu vyhodnocení, bez explicitně definovaných filtrů uvnitř měřítka.

Pokud v průřezu s kategoriemi produktů vybereme kategorie "Bikes" a "Clothing", a v průřezu s barvami vybereme barvy "Black" a "Blue", výsledek obou měřítek bude stejný.

Filtry v průřezech ovlivňují pouze výsledek měřítka [Prodeje]. Měřítko [Prodeje produktů (černé, modré, kola, oblečení)] tyto filtry ignoruje, protože se jedná o filtry nastavené na stejné sloupce, které jsou použity v logickém filtru uvnitř měřítka. Výsledky obou měřítek se shodují, protože filtry nastavené v průřezech jsou stejné, jako filtry nastavené uvnitř měřítka [Prodeje produktů (černé, modré, kola, oblečení)].

Pokud v průřezech nastavíme filtry například na kategorii "Clothing" a barvu "Blue", výsledek měřítka [Prodeje] bude představovat v každém řádku tabulky prodeje modrých produktů z kategorie oblečení, v kontextu aktuálního fiskálního roku. Měřítko [Prodeje produktů (černé ,modré, kola, oblečení)] opět tyto filtry z průřezů ignoruje.

Stejně jako u všech ostatních filtrů ve funkci CALCULATE(), i v tomto případě můžeme změnit chování filtrů pomocí funkce KEEPFILTERS() tak, aby nedocházelo k přepsání vnějších filtrů nastavených na stejné sloupce, ale k jejich průniku.

Filtrování pomocí operátoru IN a dynamických tabulek

Tabulku na pravé straně za operátorem IN můžeme sestavit dynamicky pomocí funkcí které vracejí tabulky. Díky tomu můžeme například vytvořit měřítko, které bude vracet hodnoty prodejů produktů ve všech ostatních barvách, než které uživatel vybere v průřezu. Pokud uživatel vybere například modrou a černou barvu, výsledek měřítka bude suma prodejů za všechny produkty, kromě produktů v modré a černé barvě.

Měřítko:

Prodeje ostatní barvy =

CALCULATE

(

SUM(Sales[Sales Amount]),

'Product'[Color] IN

EXCEPT

(

ALL('Product'[Color]),

VALUES('Product'[Color])

)

)

Filtr v měřítku [Prodeje ostatní barvy] bude vracet všechny barvy, kromě těch, které uživatel vybere v průřezu.

Měřítko [Prodeje] vrací v každém řádku tabulky sumu prodejů produktů v dané kategorii a v barvách "Black" a "Blue". Měřítko [Prodeje ostatní barvy] vrací sumu prodejů produktů v dané kategorii podle aktuálního řádku tabulky ve všech barvách, kromě černé a modré barvy vybrané v průřezu.

Pozn. Poslední příklad s měřítkem vracejícím prodeje za všechny ostatní barvy, než které vybere uživatel v průřezu, může být vytvořen i jednodušeji bez operátoru IN, příklad je pouze ilustrativní s cílem zobrazit různé možnosti použití funkce IN.

Shrnutí

Díky operátoru IN můžeme vytvořit logický filtr, který obsahuje více hodnot. Hodnoty na pravé straně operátoru IN musí být v tabulce. K vytvoření tabulky je v jazyku DAX k dispozici konstruktor tabulek. Tabulka na pravé straně operátoru může být vytvořena také dynamicky, pomocí funkcí vracející tabulky. Logický filtr sestavený uvnitř funkce CALCULATE() pomocí operátoru IN přepíše všechny vnější filtry nastavené v době vyhodnocení funkce na stejné sloupce, které jsou použity jako filtry uvnitř funkce CALCULATE(). Stejně jako u všech ostatních logických filtrů můžeme toto chování změnit pomocí funkce KEEPFILTERS(). Logické filtry vložené do funkce KEEPFILTERS() jsou vyhodnoceny v logickém AND vztahu se všemi vnějšími filtry. 

Stáhnout soubor s řešením.
č. 30