Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi experts,
I'm using this formula to Rank by country based on a measure:
Rank = VAR Temp = [ValueSum]
Return
IF(
Temp = 0,
BLANK(),
RANKX( ALL('Table'),Temp )
)
ValueSum = CALCULATE(SUM('Table'[Value]))
The problem is when I use the variable within the Rank function, the ranking is not working:
However, when I use the measure directly, it works:
Rank = VAR Temp = [ValueSum]
Return
IF(
Temp = 0,
BLANK(),
RANKX( ALL('Table'),[ValueSum] )
)
Is there any problem using variables within a Rank function? variables do not propagate filters?
Thanks
Solved! Go to Solution.
@Anonymous
with var you calculated your measure before the main calculation and this is lead you to almost static value.
let's say
Rank = VAR Temp = [ValueSum]
at this moment Temp is equal, for example 2. then your return will look like
Return
IF(
Temp = 0,
BLANK(),
RANKX( ALL('Table'), 2 )
)
that has no sense.
for this statement it calculates for all amount of data for each row (if this static value <> 0)
Return
IF(
Temp = 0,
BLANK(),
RANKX( ALL('Table'),[ValueSum] )
)
@Anonymous
with var you calculated your measure before the main calculation and this is lead you to almost static value.
let's say
Rank = VAR Temp = [ValueSum]
at this moment Temp is equal, for example 2. then your return will look like
Return
IF(
Temp = 0,
BLANK(),
RANKX( ALL('Table'), 2 )
)
that has no sense.
for this statement it calculates for all amount of data for each row (if this static value <> 0)
Return
IF(
Temp = 0,
BLANK(),
RANKX( ALL('Table'),[ValueSum] )
)