cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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

3 REPLIES 3
New Member

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 

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

Hello,

 

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors