Forum Discussion

ribs's avatar
ribs
Icon for Helper I rankHelper I
1 year ago
Solved

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

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

  • BeaBF's avatar
    BeaBF
    1 year ago

    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!

2 Replies

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    1 year ago

    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!