Forum Discussion
Matrix multiple Sort columns
Hi All,
I've gone thru all other request in Desktop - Matrix Multiple column sort. But nothing worked. Need experts help :smileyhappy:
I am trying to sort 2 columns (1st level - Employee by ASC and then 2nd level- totalCount by DESC)
I know it is not possible to have multi sort in Matrix. Any DAX help here please?
Current Matrix Result:
Employee Customer SaleCount ReturnCount TotalCount
10000002 C100000 20 1 21
C100002 5 1 6
C100001 10 1 11
10000001 C100003 2 1 3
C100004 5 1 6
Expected Matrix Result:
Employee Customer SaleCount ReturnCount TotalCount
10000001 C100004 5 1 6
C100003 2 1 3
10000002 C100000 20 1 21
C100001 10 1 11
C100002 5 1 6
Thanks, TPS
You may try using ISONORAFTER Function to add a measure.
Measure = VAR e = SELECTEDVALUE ( Table1[Employee] ) VAR c = [TotalCount] VAR t = SUMMARIZE ( ALLSELECTED ( Table1 ), Table1[Employee], Table1[Customer] ) RETURN COUNTROWS ( FILTER ( t, ISONORAFTER ( Table1[Employee], e, DESC, [TotalCount], c, ASC ) ) )
16 Replies
If you have the latest version of PowerBI desktop you can do this in 4 following steps:
Step 1: Convert the matrix into a table
Step 2: Sort the table by "Employee" column (Click again if you want to change the sorting order in opposite direction)
Step 3: Now add additional sort in the table by using Shift + Lift Click on "TotalCount " column (Shift + Lift Click again to change sort order to opposite direction)
Step 4: Convert the table back to matrix now, and your matrix retain these sort orders now
If this helps you then please give a thumbs up to the solution
This works well when the matrix only has one row.
But if it has two rows, aka hierarchy matrix, this doesn't work.
You can maintain multiple levels of the hierarchy by converting your Matrix to a Table, then removing your sub-level of the hierarchy from the table, do the multiple sort by steps in the table, convert the table back to a matrix, and finally add back in the sub-level of the hierarchy to the rows of the matrix. This should maintain the sort by order for multiple levels of the row hierarchy.
** Updated the steps for clarity (3/28/2025) **
Step 1: In your Matrix, add a field to the row hierarchy (which you will later remove).
Step 2: Change the Matrix to a Table visual.
Step 3: Sort all the columns you wish to sort, and in the order you wish them to be sorted.
Step 4: Delete the field you previously added in Step 1 from the table.
Step 5: Change the Table back to a Matrix visual.
Step 6: Ensure the right fields are back in the Row hierarchy.
This will maintain your multiple sorted fields within your matrix, even when you expand your row hierarchies.
You may try using ISONORAFTER Function to add a measure.
Measure = VAR e = SELECTEDVALUE ( Table1[Employee] ) VAR c = [TotalCount] VAR t = SUMMARIZE ( ALLSELECTED ( Table1 ), Table1[Employee], Table1[Customer] ) RETURN COUNTROWS ( FILTER ( t, ISONORAFTER ( Table1[Employee], e, DESC, [TotalCount], c, ASC ) ) )Hello,
Can you provide me a .pbix file? I really appreciate it. Thanks.
I have another solution.
Until the control is fixed to allow for multiple sorting patterns I would suggest the following.
Create a column with the fields you want sorted in the order you want sorted.
So in my example my matrix is grouped by Unit. But I want my sort to be Unit and Days till launch.
So creating a column as so
SortColumn = Data[Unit] & ":" & Data[DaysTillLaunch]
But in my case Unit is a text and [DaysTillLaunch] is an integer, (Cannot convert value error)
so I have to convert the [DaysTillLaunch] to a text by using the Format to give me leading zeros.
SortColumn = Data[Unit] & ":" & FORMAT(Data[DaysTillLaunch],"000000")
Then I add the sort column to values and sort ascending.
It's clumsy but it works.
Unsure if this meets your criteria exaclty, but here's how I got around the on-going issue with sorting by multiple levels in the Row Header of a Matrix.
Let's say you have the following data fields...
Month
Date
Region
Sales
You want to end with a Matrix that ultimately has a Row Header hierarchy of Month | Date | Region, with the Sales being the value, and you'd like the Sales value to maintain its decending sort order for both the Date & Region sub-levels when you expand them.
What I did was, I put the Month, Date, and Sales into a Table first. In a table you can hold the SHIFT button and click on multiple columns to sort by. So I sorted by Date decending and Sales decending. I then converted the table to a MATRIX, which maintains the sort order from the table. Finally, I added the Region field to the rows of the matrix under the Date. Now when I expand the Date to the Region level, it maintains the decending order by for my Sales values.
Hope this makes sense and helps someone.Hi,
Tried to do the suggested step but still it doesn't work. Anyone who know workarounds here?
Thank you
