Double sort in Matrix

[learner03]

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 date	Alternate
	22/02/2022
	24/02/2022
21/02/2022	25/02/2022
	21/02/2022
	24/02/2022
24/02/2022	24/02/2022
23/02/2022	23/02/2022
	24/02/2022
24/02/2022	17/02/2022
23/02/2022	15/02/2022
23/02/2022	21/02/2022
	23/02/2022
	25/02/2022
17/02/2022	18/02/2022
18/02/2022	18/02/2022
24/02/2022	18/02/2022
	25/02/2022
	25/02/2022
18/02/2022	21/02/2022
	25/02/2022
	25/02/2022
17/02/2022	18/02/2022
	25/02/2022

@OwenAuger 

[v-henryk-mstf · Accepted solution]

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.

[OwenAuger]

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]

@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

[amitchandak]

@learner03 , Is there an opportunity is fill up or fill down in power Query ?

https://docs.microsoft.com/en-us/power-query/fill-values-column

Then we can use stepped layout off

[learner03]

@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.