Sorting by multiple columns

[anandav]

Hi Experts,

I have the following data.

Employee Training Date

Greg	Training 1	19/09/2017
Bill	Training 1	19/09/2017
Joe	Training 1	19/09/2017
David	Training 1	19/09/2017
Jimbo	Training 1	19/09/2017
Greg	Training 2	19/08/2017
Bill	Training 2	19/08/2017
David	Training 2	19/08/2017
Greg	Training 3	19/07/2017
Bill	Training 3	19/07/2017
Joe	Training 3	19/07/2017
David	Training 3	19/07/2017

When I use a Table visual the data is displayed sorted by Name.

I can sort the data in PowerQuery using multiple columns:

= Table.Sort(#"Changed Type",{{"Employee", Order.Ascending}, {"Training", Order.Descending}, {"Date", Order.Ascending}})

In Data view it is displayed correctly as per PowerQuery sort.

But when I display the data in Table or Matrix visual it is only sorted by Name.

How can I enable Table visual to follow the PowerQuery sort order?

Thanks for any advise in advance.

[JustSayJoe]

Step 1: In your Matrix, add a field to the row hierarchy (which you will later remove).

Step 2: Change the Matrix to a Table visual.

Step 3: Sort all the columns you wish to sort, and in the order you wish them to be sorted.

Step 4: Delete the field you previously added in Step 1 from the table.

Step 5: Change the Table back to a Matrix visual.

Step 6: Ensure the right fields are back in the Row hierarchy.

This will maintain your multiple sorted fields within your matrix, even when you expand your row hierarchies.

[Anonymous]

Hi All,

Wonder if you can help?

I have data that I want to sort specifically, I was told there was a measure for it.

Basically on excel you can move the columns on the pivot table. So i can have 0-7,8-13,14-20 in order ( 8-13 always defaulting a date) so when i create a pivot, the order is 8-13, 0-7, 14-20.

How am I am to change the order in Power BI?

[Anonymous]

@v-chuncz-msft I created the measure you created previously in the thread and when I tried to place it as a value in the table field, it created a single count. Is there somewhere else I should be placing this measure? Thanks so much!

[garythomann]

The Matrix visual lacks custom sorting functionality and more.  There are lots of ideas on the community site.  Below are the front runners.  Please vote these up!

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/11185431-add-a-multiple-columns-s...

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17401381-conditional-formatting-f...

[v-chuncz-msft · Accepted solution]

@anandav,

You may try using ISONORAFTER Function to add a measure.

Measure =
VAR t =
    SUMMARIZE (
        ALLSELECTED ( Table1 ),
        Table1[Employee],
        Table1[Training],
        Table1[Date]
    )
RETURN
    COUNTROWS (
        FILTER (
            t,
            ISONORAFTER (
                    Table1[Employee], SELECTEDVALUE ( Table1[Employee] ), DESC,
                    Table1[Training], SELECTEDVALUE ( Table1[Training] ), ASC,
                    Table1[Date], SELECTEDVALUE ( Table1[Date] ), DESC
            )
        )
    )

[usman2338]

Would appreciate if you could explain what is going on in the code. I've used this and it gives me the correct sorting behaviour but i don't understand the workings of the code. Thanks

[anandav]

Hi @v-chuncz-msft,

Thank you for the prompt reply. That works perfectly!

I was also able to achieve this by adding an Index column in the Power Query as well. But good to learn how to do it in DAX.

Just wondering:

So any data re-ordering / sorting done in PowerQuery is really of no use in any of the visuals.

Do you think this is a bug or by design?

If by design, I am not sure what use it is to sort something in PowerQuery and the sort is not maintained in visuals?

I will mark your answer as solution shortly but just waiting to get some thoughts on the above questions from you ro anyone who might shed some light.

[usman2338]

I guess Using Index as you mentioned is the easiest. Just sort in power query and then create Index column

[v-chuncz-msft]

@anandav,

Sorting in Power Query can be used in later steps.

[anandav]

Voted for the idea here:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/11185431-add-a-multiple-columns-s...

Now it is wait and see time.

[Anonymous]

Please vote for this idea as well - adding multiple columns sort feature for Matrix table

https://ideas.powerbi.com/ideas/idea/?ideaid=23f41a4f-75c1-ed11-9ac5-501ac5248c97