Forum Discussion

learner03's avatar
learner03
Icon for Post Partisan rankPost Partisan
4 years ago
Solved

Double sort in Matrix

How can I sort with the criteria as Arrivl Date Asc to Desc with non-blanks on top an then second sort by Alternate column from Ase to Desc Date.

it is a matrix visual

Arrive dateAlternate
 22/02/2022
 24/02/2022
21/02/202225/02/2022
 21/02/2022
 24/02/2022
24/02/202224/02/2022
23/02/202223/02/2022
 24/02/2022
24/02/202217/02/2022
23/02/202215/02/2022
23/02/202221/02/2022
 23/02/2022
 25/02/2022
17/02/202218/02/2022
18/02/202218/02/2022
24/02/202218/02/2022
 25/02/2022
 25/02/2022
18/02/202221/02/2022
 25/02/2022
 25/02/2022
17/02/202218/02/2022
 25/02/2022

OwenAuger 

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    Hi learner03 ,

     

    According to your description, the best way to sort the reference is as follows: you can create a column and use the result of the rankx function as the index.

    Column = IF('Table'[Arrive date]=BLANK(),1,2)
    Col_rank = 
RANKX (
    'Table',
    RANKX ( 'Table', 'Table'[Column],, ASC ) * 1000
        + RANKX ( 'Table', 'Table'[Alternate],, DESC, DENSE )
)


    If the problem persists, please point out the specific error and provide a screenshot of the expected results. Looking forward to your feedback.


    Best Regards,
    Henry


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

5 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    4 years ago

    Hi learner03 

    In a matrix, nesting is handled automatically, so I would suggest creating sort by columns for these two columns.

     

    I wasn't quite clear on the sort order - did you mean that for both columns, the most recent dates should appear first?

     

    For such sorting, I would created columns (in Power Query) that are the negative of the original date (possibly with a constant added), but map null values to zero.

    e.g. for Arrive date, the sort column could be:

    if [Arrive date] = null then 0 else -Number.From([Arrive date])

    Then set Arrive date to sort by this column in the model.

    Similarly for Alternate.

     

    Regards,

    Owen

    • learner03's avatar
      learner03
      Icon for Post Partisan rankPost Partisan
      4 years ago

      OwenAuger Yes, you understood correctly that I need sorted by recent dates in both but first sort by Arrival date and second by Alternate,

      I tried as you said, but whe I sort it, then the 0 is coming at the top

    • learner03's avatar
      learner03
      Icon for Post Partisan rankPost Partisan
      4 years ago

      amitchandak I can't fiill up/down with any dates as the empty ones are based on when the product reaches. So, if we do not know when the product will reach, then it will by empty field.

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi learner03 ,

     

    According to your description, the best way to sort the reference is as follows: you can create a column and use the result of the rankx function as the index.

    Column = IF('Table'[Arrive date]=BLANK(),1,2)
    Col_rank = 
RANKX (
    'Table',
    RANKX ( 'Table', 'Table'[Column],, ASC ) * 1000
        + RANKX ( 'Table', 'Table'[Alternate],, DESC, DENSE )
)


    If the problem persists, please point out the specific error and provide a screenshot of the expected results. Looking forward to your feedback.


    Best Regards,
    Henry


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