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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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

11 REPLIES 11
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 III
Advocate III

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.

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

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.