Using a variable to determine sort order inside Rankx

[ebecerra]

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 · Accepted solution]

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]

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!

[Anonymous]

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

[amitchandak]

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