Presne pred 3 rokmi som v článku o daxových filtroch písal o tom, ako aplikovať viacero filtrov súčasne na kontext výpočtu merítka. Čiže ako napr. vypočítať ukazovateľ Obrat po aplikovaní filtra na krajinu a farbu produktu. Čo samozrejme funguje správne. Čo však, keď chcete aplikovať filtre z viacerých tabuliek súčasne tak, aby medzi nimi bola filtrovacia podmienka ALEBO, resp. OR?

Začneme znova našim vzorovým súborom PowerPivotu, resp. Power BI. Fungovať to bude samozrejme úplne identicky aj v excelovskom PowerPivote.

Vyskladáme si znova takúto kontingenčku:

1. do oblasti hodnôt dáme merítko Obrat z tabuľky Objednávky,
2. do oblasti riadkov dáme stĺpec CalendarYear z tabuľky Čas.

Bude to vyzerať takto:

A teraz by sme chceli vypočítať nejaký filtrovaný ukazovateľ, napr. obrat iba za USA. To spravíme jednoducho cez CALCULATE a FILTER a nové merítko takto:

Obrat za USA = CALCULATE( [Obrat]; FILTER(‘Regióny’; ‘Regióny'[Krajina]=”United States”))

A teraz keď chceme vypočítať obrat za modré produkty predané v USA, tak spravíme klasiku – pridáme ďalší filter do funkcie CALCULATE:

Obrat za USA za modre produkty = CALCULATE( [Obrat]; FILTER(‘Regióny’; ‘Regióny'[Krajina] = “United States”); FILTER(Produkty; Produkty[Color] = “Blue”))

Výsledok po pridaní oboch merítok bude vyzerať takto:

A čo keď teraz chceme zobraziť Obrat za produkty buď predané v USA, alebo za produkty s modrou farbou? Vtedy to už začne byť tak trochu problém.

Prvý problém je v tom, že nemôžeme to priamo dať do filtrovacej podmienky do jednej funkcie FILTER. Pretože táto funkcia pri základnom použití vyžaduje, aby boli všetky stĺpce z filtrovacej podmienky z tej istej tabuľky, zadanej v jej prvom parametri. Preto nemôžeme spraviť niečo takéto:

Obrat za USA alebo modre produkty = CALCULATE( [Obrat]; FILTER(‘Regióny’; ‘Regióny'[Krajina] = “United States” || Produkty[Color] = “Blue”))

…čo sa potvrdí aj v tomto chybovom hlásení:

Takže na prvý pohľad tadeto cesta nevedie. Ani cez skrátený zápis filtrov, pretože ten je ešte prísnejší na svoje podmienky.

Využijeme však štruktúru dátového modelu a fakt, že vieme cestovať medzi tabuľkami pomocou funkcie RELATED. A že z centrálnej tabuľky – Objednávky – sú cez ňu dosiahnuteľné všetky stĺpce z ostatných tabuliek, resp. číselníkov. A že tieto stĺpce si nutne nemusíme dotiahnuť do tejto tabuľky.

Finta je v tom, že použijeme funkciu FILTER nie priamo na tie číselníky, z ktorých potrebujeme spraviť tú podmienku, ale na tú centrálnu tabuľku Objednávky. A v podmienke sa cez funkciu RELATED odkážeme na stĺpce v ostatných tabuľkách, ktoré potrebujeme zadať do svojej podmienky. Tak uspokojíme funkciu FILTER, a súčasne tým pôjde spraviť filtrovacia podmienka OR/ALEBO cez viacero tabuliek.

Hore uvedený pokus o merítko teda upravíme takto:

Obrat za USA alebo modre produkty = CALCULATE( [Obrat]; FILTER(‘Objednávky’; RELATED(‘Regióny'[Krajina]) = “United States” || RELATED(Produkty[Color]) = “Blue”))

…alebo vypeknené cez DAX Formatter:

Po dosadení do kontingečky to vyzerá takto:

A skúška správnosti?

Spravíme to tak, že si vypočítame ešte ďalšie merítko, tentokrát Obrat iba za modré produkty:

Obrat za modre produkty = CALCULATE( [Obrat]; FILTER(Produkty; Produkty[Color] = “Blue”))

Po dosadení do kontinenčky to bude vyzerať takto:

Teraz z logiky vecí vyplýva, že ak máme v osobitných merítkach obrat za USA, a aj obrat za modré produkty, tak obrat za modré produkty alebo USA bude rovný tomuto: (Obrat za USA + Obrat za modré produkty – Obrat za USA za modré produkty). To odpočítanie je tam preto, aby sme odstránili duplicitne zarátané objednávky, ktoré sú už zarátané v obrate za USA aj v obrate za modré produkty. Keď si to overíte na kalkulačke, alebo ďalším merítkom s týmto vzorcom v kontingenčke, tak by vám to malo sedieť.

Takáto filtrovacia podmienka môže byť pomalšia na veľmi veľkých tabuľkách. V tom prípade si filtrovacie stĺpce dotiahnite do centrálnej tabuľky – v tomto prípade Objednávky – ako vypočítané stĺpce cez funkciu RELATED. Tak sa na ne môžete pri filtrovaní odkazovať priamo z jednej tabuľky, a ušetria sa 2 opakované joiny na číselníky. Vo väčšine prípadov to ale netreba, takže sa tým nemusíte trápiť.

Takto jednoducho teda ide spraviť filtrovacia podmienka ALEBO/OR cez viacero tabuliek. A po troche zamyslenia to ani veľmi nebolelo 🙂