topic Re: Using a variable to determine sort order inside Rankx in DAX Commands and Tips

Using a variable to determine sort order inside Rankx

ebecerra — Tue, 07 Dec 2021 01:03:14 GMT

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.

Re: Using a variable to determine sort order inside Rankx

amitchandak — Tue, 07 Dec 2021 02:48:53 GMT

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

Re: Using a variable to determine sort order inside Rankx

Anonymous — Tue, 07 Dec 2021 07:17:40 GMT

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

Re: Using a variable to determine sort order inside Rankx

AlexisOlson — Tue, 07 Dec 2021 17:18:33 GMT

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

Re: Using a variable to determine sort order inside Rankx

ebecerra — Tue, 07 Dec 2021 19:00:18 GMT

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!