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 w...
  • 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] )
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] )