How to use filter with multiple values dynamically in DAX ?

[Cobra77]

Hi ,

How to use filter with multiple values dynamically in DAX ?

Its a PBI on SSAS Tabular 2016 live connection.

With a hierarchy , i pass for sample level1 , and i want calculate the mesure for level2

TF test:=

VAR lvl = IF(HASONEVALUE('98 - Axe filtre'[Level_flt]) && HASONEFILTER('98 - Axe filtre'[Level_flt]); VALUES('98 - Axe filtre'[Level_flt]) ; -2)

VAR Filt = FILTER('02 - Arborescence';SWITCH(lvl ; 1 ; "'02 - Arborescence'[Level2]" ;
2 ; "'02 - Arborescence'[Level3]" ;
3 ; "'02 - Arborescence'[Level4]" ;
4 ; "'02 - Arborescence'[Level5]" ;
5 ; "'02 - Arborescence'[Level6]" ;
6; "'02 - Arborescence'[Level7]" ;
7 ; "'02 - Arborescence'[Level8]") =
CALCULATETABLE ( VALUES('02 - Arborescence'[Name])
; '02 - Arborescence'[Level] = lvl + 1) )

RETURN IF ( lvl >= 0 &&
SWITCH(lvl ; 1 ; HASONEFILTER('02 - Arborescence'[Level1] ) ;
2 ; HASONEFILTER('02 - Arborescence'[Level2]) ;
3 ; HASONEFILTER('02 - Arborescence'[Level3]) ;
4 ; HASONEFILTER('02 - Arborescence'[Level4]) ;
5 ; HASONEFILTER('02 - Arborescence'[Level5]) ;
6 ; HASONEFILTER('02 - Arborescence'[Level6]) ;
7 ; HASONEFILTER('02 - Arborescence'[Level7]) ;
8 ; HASONEFILTER('02 - Arborescence'[Level8])) ;
CALCULATE([Nb] ; Filt) ; BLANK())

CALCULATETABLE ( VALUES('02 - Arborescence'[Name]) 
; '02 - Arborescence'[Level] = lvl + 1)  return well 7 values when i test with level1 for level2

I try with in instead of "=" after the switch  but error

Thanks for your help

[v-juanli-msft]

Hi @Cobra77

From your table, i can't find '98 - Axe filtre'[Level_flt], does it refer to "sample level1"?

"I try with in instead of "=" after the switch  but error"

Does it means operation in the following measure?

CALCULATETABLE ( VALUES('02 - Arborescence'[Name]) 
; '02 - Arborescence'[Level] = lvl + 1)  

Best Regards

Maggie

[Cobra77]

Hi @Maggie 😉

it's a bit like the problem  http://community.powerbi.com/t5/Desktop/Show-different-measures-with-Hierarchie-filtered-with-combo/...

I picked up the idea of the extra table to filter  ( '98 - Axe filtre'[Level_flt] , just one column with level (int )

We search a solution for https://community.powerbi.com/t5/Desktop/Change-the-level-of-the-legend-dynamically-on-bubble-charts...

with the example of hierarchy table provided , if i pass Level_flt = 1 ( table parameter   '98 - Axe filtre'[Level_flt] ) and level1 = CC

we want in the graph the measure for the level2 in legend , but the measure must be calculated with the children

in this example , the legend on the graph must be with values : TT, RR , WW

with CALCULATETABLE ( VALUES('02 - Arborescence'[Name]) 
; '02 - Arborescence'[Level] = lvl + 1)

, i find well TT, RR, WW  but how pass ?

If i pass Level_flt = 2 and level2 = TT we want on the graph  with legend values:  TY  and the measure the resul with his children

I only see the name column to put in the legend ? other solution ?

thanks.