Rankx for a Hierarchy

[Anonymous]

Hi All,

I am having trouble getting my matrix to sort based on a selected value from the slicer. For example, if the selected value is "Base Salary" then I would like the base salary column to be sorted in DEC order and the same idea with the Tota Salary column. The drill-down of the matrix is [Job Title] -> [Name], I would like it to only sort the Job title
Thank you in advance!

[Anonymous]

Hi  @Anonymous ,

Can you try to directly click the small arrow below to control whether the column is in reverse or positive order.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , You can create two ranks measure or vars 

Rank1 = RANKX(ALL(Table[Job Title]), [salary])

Rank2 = RANKX(ALL(Table[Name]), [salary])

Switch rank = Switch( True() ,

isinscope(Table[Name]) , [Rank2],

isinscope(Table[Job Title]) , [Rank1],

blank()

)

https://xxlbi.com/blog/new-dax-function-isinscope/

[Anonymous]

Hi   amitchandak! Thanks for your quick response,
I tried this just now, It is not working when I have the [Name] field as the second level to the heiarchy. Am I missing something?

[amitchandak]

@Anonymous ,

Switch rank = Switch( True() ,

isinscope(Table[Name]) , [Rank2],

isinscope(Table[Job Title])  && not(isinscope(Table[Name]) ) , [Rank1],

blank()

)

[Anonymous]

Unfortunately, this is not working either...