Forum Discussion

tps136's avatar
tps136
Icon for Advocate II rankAdvocate II
8 years ago
Solved

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 :smileyhappy: I am trying to sort 2 columns (1st level - Employee by ASC...
Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    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 ) )
    )
JustSayJoe's avatar
JustSayJoe
Icon for Advocate IV rankAdvocate IV
3 years ago

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.