Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tps136
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 Smiley Happy

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
v-chuncz-msft
Community Support
Community Support

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

View solution in original post

10 REPLIES 10
v_mark
Helper V
Helper V

Hi,

 

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

Thank you

JustSayJoe
Advocate II
Advocate II

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.

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

Thanks! 🙂

wow - thank you! this worked perfectly

this didn't work for me either

v-chuncz-msft
Community Support
Community Support

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

Hello,

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.