cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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

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

1 ACCEPTED SOLUTION
Community Support

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 ) )
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
11 REPLIES 11
Helper V

Hi,

Tried to do the suggested step but still it doesn't work. Anyone who know workarounds here?

Thank you

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.

Anonymous
Not applicable

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.

Helper I

Plase mark this as Solution. That is absolut genious and easier then a compolex measure 😉

Super User

Goodness! It is now 2024 but this response saved me.

Thanks! 🙂

Regular Visitor

wow - thank you! this worked perfectly

Regular Visitor

this didn't work for me either

Community Support

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 ) )
)
```
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

Hello,

Can you provide me a .pbix file? I really appreciate it. Thanks.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.