sorting by two rows in a matrix

[Anonymous]

Hello,

I can't seem to find a solution on this... assuming there is some sort of workaround that I can do (I expect that I can, but who knows).

My problem is that I need to sort a matrix in descending order by a number of searches, but when you expand the first row, the second row will be sorted by date descending.

Note: the names are marked out for privacy.

So it currently looks like this:

Collapsed:

Expanded:

The goal is to keep it descending by 'Number of Searches', but when you expand the name, the dates will be in descending order (regardless of number):

Name		Number of Searches
Name		132
	9 March, 2021	4
	8 March, 2021	60
	5 March, 2021	48
	4 March, 2021	9
	3 March, 2021	11
Name		80
	9 March, 2021	41
	4 March, 2021	18
	1 March, 2021	20
	26 February, 2021	1
Name		4
	5 March, 2021	1
	25 February, 2021	3

You can choose to sort the matrix by the 'Date' if you expand the rows that contain dates, but this puts all of the names in alphabetical order and overrides the oringal sort by 'Number of Searches'.

Thanks for any help that can be provided.

Edit: Both 'Name' and 'Date' fields are in the Rows section. There is nothing in the column section of the visual pane. 

'Number of Searches' is a measure in the values section.

I've tried sorting by an index column, grouping columns, etc., and nothing has worked so far. If anyone knows if there's a work around, it would be much appreciated.

[v-yiruan-msft]

Hi @Anonymous ,

Do you want to sort the matrix data by the field Date descending? If yes, you can follow the steps in the below screenshot to set it. Otherwise, please provide more details about your requirement. Thank you.

Best Regards

[Anonymous]

Hi @v-yiruan-msft ,

I want to sort both by the number of searches in descending order, and then when you click the '+' to expand a row, those dates that the searches took place would be in descending order, regardless of how many searches there were.

So, sort by number of searches descending when the rows are collapsed; 

sort by date descending when the rows are expanded BUT keep the sort descending by number of searches on the upper heirarchy.

I'd also like to add that clicking on the ellipses and then sorting by the date removes the sorting by descending number of searches.

It puts the names in alphabetical order, which isn't the goal.

I hope that makes sense! 

If it's not possible, I'll just try to give these results in a different way.

[v-yiruan-msft]

Hi @Anonymous ,

This may require you to make sort settings for two times:
1. When there is only one hierarchy level Name in matrix, select the More options (...) menu in the upper right corner of the matrix, then select Sort descending and select Sort by, and then select the field Number of Searches

2. When you click the "+" in front of Name to expand the data, select the More options (...) menu in the upper right corner of the matrix, then select Sort descending and select Sort by, and then select the field Date

Best Regards

[Anonymous]

@v-yiruan-msft 

Apologies for the late response--it's been a busy week!

Unfortunately, I've tried this, and it normally overwrites the previous sort by Number of Searches.

I will try again though and let you know!