Forum Discussion
Post PatronHow 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())
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
v-juanli-msftCommunity Support
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
Cobra77Hi Maggie ;-)
it's a bit like the problem http://community.powerbi.com/t5/Desktop/Show-different-measures-with-Hierarchie-filtered-with-combo/m-p/448079#M207347
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/td-p/443066
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.