cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Cobra77 Member
Member

How to use filter with multiple values dynamically in DAX ?

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())

 

Sample_hierarchy.jpg

 

 

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

2 REPLIES 2
Community Support Team
Community Support Team

Re: How to use filter with multiple values dynamically in DAX ?

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 Member
Member

Re: How to use filter with multiple values dynamically in DAX ?

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.