DAX funkce CALCULATE s CROSSFILTER

V Tabulárním modelu, který je součástí Power BI, Power Pivot v Excelu a dalších analytických nástrojů, jsou mezi tabulkami v modelu obvykle nastaveny relace. Filtr aplikovaný na jeden sloupec nebo tabulku filtruje automaticky také tabulky, které jsou s filtrovanou tabulkou propojeny relacemi. Směr filtrace mezi tabulkami je znázorněn v diagramu modelu malými šipkami uprostřed relace. Právě způsob propagace filtrů mezi tabulkami můžeme ve výpočtech programově měnit pomocí funkce CROSSFILTER(). Funkce CROSSFILTER() umožňuje programově vypnout filtrování, nastavit obousměrné filtrování mezi tabulkami nebo nastavit filtrování v jednom nebo druhém směru, pokud to vztah mezi tabulkami umožňuje, a to bez nutnosti měnit vytvořený model nebo upravovat relace v modelu. V tomto příspěvku jsou uvedeny vybrané příklady použití funkce CROSSFILTER().

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

Příklad CROSSFILTER

Funkci CROSSFILTER() můžeme používat pouze ve vybraných DAX funkcích. Kompletní seznam funkcí, které přijímají jako argument funkci CROSSFILTER(), můžeme najít v oficiální dokumentaci. V tomto příspěvku bude funkce CROSSFILTER() použita jako argument funkce CALCULATE(), protože právě funkce CALCULATE() je funkce, ve které se CROSSFILTER() používá nejčastěji. Funkce CROSSFILTER() sama o sobě nevrací žádnou hodnotu, ale používá se jako argument ostatních funkcí s cílem změnit způsob propagace filtrů mezi tabulkami v době vyhodnocení výpočtu. Protože budeme v příkladech manipulovat s relacemi, podívejme se nejdříve na tabulky v použitém cvičném modelu a na vztahy mezi nimi. Začněme zjednodušeným modelem, který bude obsahovat pouze tabulky 'Product', 'Sales' a 'Customer'.

 

Tabulka 'Product' a tabulka 'Customer' jsou obě s tabulkou 'Sales' ve vztahu ONE-TO-MANY na straně ONE. Ve vztahu ONE-TO-MANY je směr filtrace, znázorněný šipkou ve středu relace, v základním nastavení vždy ve směru od tabulky na straně ONE k tabulce na straně MANY. Filtr aplikovaný na sloupce z tabulky 'Product' proto automaticky filtruje tabulku 'Sales'. Stejná situace je i v případě vztahu mezi tabulkami 'Customer' a 'Sales', kdy 'Customer' filtruje 'Sales'.

Nyní si můžeme ukázat příklad propagace filtrů přes relace. Následují měřítko vrací počet řádků v tabulce 'Sales', v aktuálním kontextu vyhodnocení. 

Měřítko:

Počet objednávek = COUNTROWS(Sales)

Pokud měřítko [Počet objednávek] vložíme do vizuálu Tabulka, spolu s hodnotami ze sloupce 'Product'[Color], výsledek může vypadat následovně.

V prvním sloupci na obrázku výše jsou barvy produktů. Každá barva pak před vyhodnocením měřítka [Počet objednávek] automaticky filtruje tabulku 'Sales'. Výsledkem je proto v každém řádku počet objednávek produktů v konkrétní barvě, která je uvedena v prvním sloupci vizuálu. Nyní se ještě vraťme k diagramu použitého modelu.

V použitém modelu existuje také vztah mezi tabulkou 'Product' a tabulkou 'Customer'. Každý zákazník si mohl zakoupit jeden nebo více produktů uvedených v tabulce 'Product'. Současně, každý produkt mohl být prodán jednomu nebo více zákazníkům, kteří jsou uvedeni v tabulce 'Customer'. Mezi tabulkou 'Product' a 'Customer' tedy existuje vztah MANY-TO-MANY, ačkoliv mezi těmito tabulkami není přímá relace. Nyní si vytvoříme měřítko, které bude počítat počet zákazníků v tabulce 'Customer'.

Měřítko:

Počet zákazníků = COUNTROWS(Customer)

Nové měřítko vložíme do původního vizuálu s barvami produktů v řádcích, tedy hodnotami z tabulky 'Product'.

Měřítko [Počet zákazníků] vrací v každém řádku vizuálu stejnou hodnotu, která odpovídá počtu všech zákazníků v tabulce 'Customer'. Je tedy zřejmé, že tabulka 'Product' filtruje tabulku 'Sales' (měřítko [Počet objednávek]), ale nefiltruje tabulku 'Customer' (měřítko  [Počet zákazníků]). Aby měřítko [Počet zákazníku] vracelo ve výše uvedeném vizuálu smysluplné hodnoty, je třeba nejdříve aktivovat obousměrnou filtraci mezi tabulkami 'Sales' a 'Customer' tak, aby filtr začínající v tabulce 'Product' filtroval 'Sales', a z tabulky 'Sales' pokračoval k tabulce 'Customer'. To můžeme udělat buď změnou relace v modelu, nebo programově pomocí funkce CROSSFILTER(). 

Pozn.: Obecně se nedoporučuje nastavovat obousměrné filtrování přímo v modelu, pokud to není nezbytně nutné.

Funkce CROSSFILTER() má tři povinné argumenty. První dva argumenty jsou názvy sloupců, které tvoří existující relaci mezi tabulkami, u které chceme upravovat způsob propagace filtrů. Třetím argumentem je způsob filtrace, který chceme v době vyhodnocení výpočtu nastavit pro tuto relaci. Ve třetím argumentu funkce CROSSFILTER() můžeme vybírat z pěti možností:

 - None – žádná filtrace,
 - Both – obousměrná filtrace,
 - OneWay – jednosměrná filtrace (ze strany ONE k MANY).

Zbývající dvě možnosti, které můžeme uvést ve třetím argumentu funkce CROSSFILTER(), je možné používat pouze pro relace typu MANY-TO-MANY:

 - OneWay_LeftFiltersRight - tabulka se sloupcem zadaným v  prvním argumentu filtruje tabulku se sloupcem zadaným ve druhém argumentu,
 - OneWay_RightFiltersLeft - tabulka se sloupcem zadaným ve druhém argumentu filtruje tabulku se sloupcem zadaným v prvním argumentu.

Jednotlivé možnosti si ukážeme v samostatných příkladech. Nyní se proto vrátíme k počtu zákazníků, kteří zakoupili produkt v dané barvě. Původní měřítko [Počet zákazníků] vracelo všechny zákazníky z tabulky 'Customer', bez ohledu na filtry z tabulky 'Product', protože filtr nastavený na sloupce z tabulky 'Product' sice filtruje tabulku 'Sales', ale tabulka 'Sales' již nefiltruje tabulku 'Customer'. Propagaci filtrů mezi tabulkou 'Sales' a tabulkou 'Customer'  ale můžeme v době vyhodnocení výpočtu nastavit programově právě pomocí funkce CROSSFILTER(). Následující měřítko již bude vracet správné hodnoty.

Měřítko:

Počet zákazníků (Obousměrná filtrace) =

CALCULATE

(

    COUNTROWS(Customer),

    CROSSFILTER(Customer[CustomerKey], Sales[CustomerKey], Both)

)

V tomto případě nezáleží na pořadí prvních dvou argumentů ve funkci CROSSFILTER(). Na pořadí prvních dvou argumentů záleží pouze pokud pracujeme ve třetím argumentu s možnostmi OneWay_LeftFiltersRight a OneWay_RightFiltersLeft. V těchto dvou případech pak první argument ve funkci CROSSFILTER() bude představovat levou tabulku, a druhý argument pravou tabulku.

Jak můžeme vidět na následujícím obrázku, měřítko [Počet zákazníků (Obousměrná filtrace)] již nyní vrací správné výsledky.

Na výše uvedeném obrázku tak můžeme vidět, kolik zákazníků si zakoupilo produkty v barvě, která je uvedena v prvním sloupci vizuálu Tabulky. Barva v každém řádku tabulky filtruje při vyhodnocení měřítka [Počet zákazníků (Obousměrná filtrace)] tabulku 'Sales', a tato tabulka pak díky nastavenému obousměrnému filtrování ve funkci CROSSFILTER() filtruje také tabulku 'Customer'.

Pomocí funkce CROSSFILTER() pak můžeme také zneplatnit směr filtrace nastavený mezi tabulkami v modelu, pokud vybereme ve třetím argumentu funkce CROSSFILTER() možnost NONE.

Měřítko:

Počet objednávek (Bez filtrace) =

CALCULATE

(

    COUNTROWS('Sales'),

    CROSSFILTER('Product'[ProductKey], 'Sales'[ProductKey], None)

)

Pokud nové měřítko opět vložíme do stejného vizuálu s barvami produktů v řádcích, výsledek bude vypadat následovně.

Původní měřítko [Počet objednávek] je vyhodnoceno v kontextu filtru každé barvy z tabulky 'Product', díky relaci nastavené v modelu mezi tabulkami 'Product' a 'Sales'. Nové měřítko [Počet objednávek (Bez filtrace)] pak díky funkci CROSSFILTER() a argumentu None propagaci filtrů mezi těmito tabulkami dočasně zneplatní. Měřítko [Počet objednávek (Bez filtrace)] proto vrací v každém řádku stejnou hodnotu, která představuje počet řádků v tabulce 'Sales', bez ohledu na barvy v řádcích vizuálu. 

Jak bylo možné vidět na přechozím příkladu, ačkoli je v modelu nastavená relace mezi tabulkami 'Product' a 'Sales' , pomocí funkce CROSSFILTER() můžeme propagaci filtrů mezi tabulkami v době vyhodnocení výpočtu dočasně zneplatnit. V dalším příkladu si můžeme ukázat jak upravovat směr filtrace mezi tabulkami ve vztahu MANY-TO-MANY. V použitém cvičném modelu není žádná relace MANY-TO-MANY, nicméně tuto relaci můžeme nastavit i mezi tabulkami, které jsou ve vztahu ONE-TO-MANY a nasimulovat si tak chování funkce CROSSFILTER() při práci s relacemi typu MANY-TO-MANY. Uvažujme například tabulky 'Reseller' a 'Sales'.

Mezi těmito tabulkami je nyní nastavena relace MANY-TO-MANY. Změnit nastavení relace můžeme jednoduše dvojitým kliknutím na relaci ve výše zobrazeném diagramu a výběrem volby "Many to many (*.*)" v dialogovém okně relace. V přikladu se opět můžeme podívat na počet objednávek z tabulky 'Sales', nicméně v řádcích tabulky použijeme sloupec s tabulky 'Reseller', konkrétně 'Reseller'[Country-Region].

Jelikož je mezi tabulkami 'Reseller' a 'Sales' nastavena obousměrná filtrace, filtr aplikovaný na sloupce v tabulce 'Reseller' filtruje tabulku 'Sales', a stejně tak filtry aplikované na sloupce z tabulky 'Sales' budou filtrovat tabulku 'Reseller'. Nyní si do modelu můžeme přidat následující dvě měřítka.

Měřítka:

Počet objednávek (Reseller filtruje Sales) =

-- Mezi tabulkou Reseller a Sales

-- je pro tento příklad uměle

-- nastavena relace MANY-TO-MANY

-- a obousměrná filtrace

CALCULATE

(

    COUNTROWS(Sales),

    CROSSFILTER(Reseller[ResellerKey], Sales[ResellerKey], OneWay_LeftFiltersRight)

)

Počet objednávek (Sales filtruje Reseller) =

-- Mezi tabulkou Reseller a Sales

-- je pro tento příklad uměle

-- nastavena relace MANY-TO-MANY

-- a obousměrná filtrace

CALCULATE

(

    COUNTROWS(Sales),

    CROSSFILTER(Reseller[ResellerKey], Sales[ResellerKey], OneWay_RightFiltersLeft)

)

Pokud obě nová měřítka vložíme do původního vizuálu, výsledek bude vypadat následovně.

Jak můžeme vidět na obrázku výše, měřítko [Počet objednávek (Reseller filtruje Sales)] vrací stejné hodnoty, jako měřítko [Počet objednávek], ve kterém nijak s relacemi nemanipulujeme. V měřítku [Počet objednávek (Reseller filtruje Sales)] jsme uměle nastavili směr filtrace směrem z tabulky 'Resseler' k tabulce 'Sales'. Pokud tedy hodnoty ze sloupce 'Reseller'[Country-Region] v řádcích tabulky filtrují při vyhodnocení měřítek model, v době vyhodnocení měřítka [Počet objednávek (Reseller filtruje Sales)] filtrují také tabulku 'Sales'. 

U měřítka [Počet objednávek (Sales filtruje Reseller)] je pak situace jiná. V tomto měřítku pomocí funkce CROSSFILTER() nastavujeme směr filtrace z tabulky 'Sales' k tabulce 'Reseller'. V použitém vizuálu ale tvoří filtr hodnoty z tabulky 'Reseller'. Tabulka 'Reseller' ale v době vyhodnocení měřítka [Počet objednávek (Sales filtruje Reseller)] nefiltruje tabulku 'Sales', i když je v modelu nastavena obousměrná filtrace, protože funkce CROSSFILTER() dočasně nastaví vlastní směr filtrace ve směru od tabulky 'Sales' k tabulce 'Reseller', a obousměrnou relaci nastavenou v modelu dočasně přepíše.

Shrnutí

Funkce CROSSFILTER() je užitečná zejména v situacích, kdy potřebujeme upravovat směr filtrace nastavený v modelu mezi tabulkami. Funkce CROSSFILTER() se nejčastěji používá k dočasnému nastavení obousměrné filtrace mezi tabulkami (možnost Both ve třetím argumentu funkce). Pokud potřebujeme pro některé výpočty nastavit mezi tabulkami obousměrné filtrování, nemusíme upravovat relace v modelu, ale stačí přidat do výpočtu ve funkci CALCULATE() funkci CROSSFILTER(), pomocí které můžeme nastavit vlastní způsob propagace filtrů pouze pro konkrétní výpočet. V Power Pivot v Excelu je funkce CROSSFILTER() jedinou možností, jak můžeme nastavit obousměrné filtrování mezi tabulkami v době vyhodnocení výpočtu.

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