Forum Discussion

ebecerra's avatar
ebecerra
Microsoft Employee
4 years ago
Solved

Using a variable to determine sort order inside Rankx

I'm using a slicer to select a "Sort type" for a visualization I have. Depending on what value is selected, I am planning to use that as the sort option for a RANKX function I have. For some reason when I use a variable to get the selected value (since I cannot see the table from inside the RANKX inner part) I get an error that says that it expects the ORDER argument to be 0/FALSE/DESC etc. The funny thing is that if I pass in a hardcoded value to my variable it works fine. Is there something I'm missing here in order to get it to work? Here is an example of the code I have:

 

VAR TopOrBottom = if(SELECTEDVALUE('Top or Bottom'[Option])=="Top",FALSE,TRUE) // THIS DOES NOT WORK
VAR TopOrBottom = TRUE //THIS MAKES RANKX happy.
...
...
... 
RANKX(
ALLSELECTED(MyTable[pageTitle]),
[Total PageViews],[Total PageViews], TopOrBottom // This is where it complains that it needs a 0/1/TRUE/FALSE/ASC/DESC
)
 
Thanks in advanced for your help.
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Another option might be to flip the sign of the measure depending on Top/Bottom.

    VariableRank = 
VAR Sgn = IF ( SELECTEDVALUE ( 'Top or Bottom'[Option] ) = "Top", 1, -1 )
RETURN
    RANKX ( ALLSELECTED ( MyTable[pageTitle] ), Sgn * [Total PageViews] )

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    Another option might be to flip the sign of the measure depending on Top/Bottom.

    VariableRank = 
VAR Sgn = IF ( SELECTEDVALUE ( 'Top or Bottom'[Option] ) = "Top", 1, -1 )
RETURN
    RANKX ( ALLSELECTED ( MyTable[pageTitle] ), Sgn * [Total PageViews] )
    • ebecerra's avatar
      ebecerra
      Microsoft Employee
      4 years ago

      Sweet! I liked this solution as it is simple enough to understand and for my particular scenario. Thanks a lot to everyone else for their soultions!

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    ebecerra , better create two rank Var or measure with Asc and desc rank and use that

     

    Example - All measures

    Rank1 = Rankx(Allselected(Sales), [Sales],,asc,dense)

    Rank2 = Rankx(Allselected(Sales), [Sales],,desc,dense)

     

    if(selectedvalues(Table[Sort Order]) =1, [Rank1], [Rank2] )

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello ebecerra ,

    We can achieve it by a little different method. By adding two rank functions in an if-else clause. The issue is RANKX function couldn't accept variables in the ORDER attribute.

    You can find the solution and measure formula below :

     

    Thanks,
    Neel