Sort the RankX results in asc or desc based on user's input through slicer

[ribs]

I could get the RankX working but I don't want to display the RankX measure in the matrix rather I want to add it to the filter pane of the visual. The problem is I can't tell if the results are sorted in ascending or descending or the Ranks are mixed. How do I get this working through DAX or better ask the user to dynamically select ASC or DSC

Here is my RankX function;

Dynamic Rank =

VAR SelectJahr= SELECTEDVALUE(RankYearSelector[Year])

RETURN

SWITCH(

    TRUE();

    SelectJahr== 'From'[From Value];RANKX(ALLSELECTED(Customer[Name]); [Dynamic Revenue 1];;DESC;Dense);

    SelectJahr== 'To'[To Value 2]; RANKX(ALLSELECTED(Customer[Name]);[Dynamic Revenue 2];;DESC;Dense)

)

The matrix has 3 columns: Customers, Revenue1 Year, Revenue2 Year. This compares the revenue between two years that is specified through parameters- 'From'[From Value] and 'To'[To Value 2]

This parameter- RankYearSelector[Year]- user selects based on which Yearof the two above to Rank

Thank you in advance

[ribs]

Thank you! That worked😊

[BeaBF · Accepted solution]

@ribs Hi!

First, create a new table with this code:

SortDirection =
DATATABLE("SortOrder", STRING, {{"ASC"}, {"DESC"}})

Second, update your measure:

Dynamic Rank =
VAR SelectJahr = SELECTEDVALUE(RankYearSelector[Year])
VAR SortOrder = SELECTEDVALUE(SortDirection[SortOrder])
VAR RevenueValue =
SWITCH(
TRUE(),
SelectJahr = 'From'[From Value], [Dynamic Revenue 1],
SelectJahr = 'To'[To Value 2], [Dynamic Revenue 2],
BLANK()
)
RETURN
SWITCH(
TRUE(),
SortOrder = "ASC", RANKX(ALLSELECTED(Customer[Name]), RevenueValue, , ASC, Dense),
SortOrder = "DESC", RANKX(ALLSELECTED(Customer[Name]), RevenueValue, , DESC, Dense),
BLANK()
)

Now, add your Dynamic Rank measure to the visual-level filter pane.

Set a condition like Dynamic Rank is less than or equal to 5 or whatever top N filter you want.

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!