Forum Discussion

M_Potts's avatar
M_Potts
Frequent Visitor
8 years ago
Solved

How to sort a table by two columns using date and a number

I am writing up a dashboard for my company. I am trying to sort by two things:   Firstly, I want to sort by date in descending order (to get the latest date)   Secondly I want to sort by a number...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi M_Potts,

     

    As mentioned in above link you provided, the highlighted part should measures rather than original table columns.

     

    In your scenario, please create two extra measures:

    primary measure = MAX('Table'[Date])
secondary measure = MAX('Table'[Number])

    Then, modify your final rank measure to:

    Final Rank = 
RANKX (
    ALL ( 'Table'),
    RANKX ( ALL ( 'Table' ), [primary measure],, DESC )
        + DIVIDE (
            RANKX ( ALL ( 'Table' ), [secondary measure],, DESC ),
            ( COUNTROWS ( ALL ( 'Table' ) ) + 1 )
        )
)

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi M_Potts,

 

As mentioned in above link you provided, the highlighted part should measures rather than original table columns.

 

In your scenario, please create two extra measures:

primary measure = MAX('Table'[Date])
secondary measure = MAX('Table'[Number])

Then, modify your final rank measure to:

Final Rank = 
RANKX (
    ALL ( 'Table'),
    RANKX ( ALL ( 'Table' ), [primary measure],, DESC )
        + DIVIDE (
            RANKX ( ALL ( 'Table' ), [secondary measure],, DESC ),
            ( COUNTROWS ( ALL ( 'Table' ) ) + 1 )
        )
)

 

Best regards,

Yuliana Gu

  • M_Potts's avatar
    M_Potts
    Frequent Visitor
    8 years ago

    Hi v-yulgu-msft!

     

    Thank you, this worked perfectly!

     

    For anyone who is a bit of a noob like me, once you've done the above solution, remember to search for the new column 'Final Rank' in your table so that you can order it according to your specs (as in the picture provided in the above post).