Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
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 ) ) )
Hi,
Tried to do the suggested step but still it doesn't work. Anyone who know workarounds here?
Thank you
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.
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
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 ) ) )
Hello,
Can you provide me a .pbix file? I really appreciate it. Thanks.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
104 | |
99 | |
98 | |
38 | |
37 |
User | Count |
---|---|
158 | |
124 | |
75 | |
74 | |
63 |