Matrix multiple Sort columns

[tps136]

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

[v_mark]

Hi,

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

Thank you

[JustSayJoe]

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]

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 

[MichaelDoig]

This works well when the matrix only has one row.

But if it has two rows, aka hierarchy matrix, this doesn't work.

[JustSayJoe]

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.

[AlexanderKais]

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

[mussaenda]

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

Thanks! 🙂

[JenJaRu]

wow - thank you! this worked perfectly

[nglenn1]

this didn't work for me either

[v-chuncz-msft · Accepted solution]

@tps136,

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

[dsaladi]

Hello,

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