Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Impact of variables when Ranking

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

General Comment
Need Help
Tips and Tricks
  • az38's avatar
    az38
    6 years ago

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

     

1 Reply

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

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