Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |