- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ) ) )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Tried to do the suggested step but still it doesn't work. Anyone who know workarounds here?
Thank you
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This works well when the matrix only has one row.
But if it has two rows, aka hierarchy matrix, this doesn't work.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Plase mark this as Solution. That is absolut genious and easier then a compolex measure 😉
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Goodness! It is now 2024 but this response saved me.
Thanks! 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
wow - thank you! this worked perfectly
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
this didn't work for me either
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ) ) )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello,
Can you provide me a .pbix file? I really appreciate it. Thanks.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
12-19-2023 12:58 AM | |||
08-12-2024 02:36 AM | |||
04-22-2024 06:12 AM | |||
Anonymous
| 07-25-2024 11:27 PM | ||
10-11-2024 03:15 AM |
User | Count |
---|---|
127 | |
81 | |
59 | |
58 | |
44 |
User | Count |
---|---|
182 | |
114 | |
82 | |
67 | |
52 |