cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anandav Established Member
Established Member

Sorting by multiple columns

Hi Experts,

 

I have the following data.

 

Employee Training Date

GregTraining 119/09/2017
BillTraining 119/09/2017
JoeTraining 119/09/2017
DavidTraining 119/09/2017
JimboTraining 119/09/2017
GregTraining 219/08/2017
BillTraining 219/08/2017
DavidTraining 219/08/2017
GregTraining 319/07/2017
BillTraining 319/07/2017
JoeTraining 319/07/2017
DavidTraining 319/07/2017

 

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

Capture.JPG

 

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.

Capture.JPG

 

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

Capture.JPG

 

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

 

Thanks for any advise in advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Sorting by multiple columns

@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
            )
        )
    )
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

7 REPLIES 7
Community Support Team
Community Support Team

Re: Sorting by multiple columns

@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
            )
        )
    )
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

anandav Established Member
Established Member

Re: Sorting by multiple columns

Hi @v-chuncz-msft,

Thank you for the prompt reply. That works perfectly! Man Happy

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.

Community Support Team
Community Support Team

Re: Sorting by multiple columns

@anandav,

 

Sorting in Power Query can be used in later steps.

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.
anandav Established Member
Established Member

Re: Sorting by multiple columns

usman2338 Frequent Visitor
Frequent Visitor

Re: Sorting by multiple columns

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

Highlighted
usman2338 Frequent Visitor
Frequent Visitor

Re: Sorting by multiple columns

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

taylor_c Frequent Visitor
Frequent Visitor

Re: Sorting by multiple columns

@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!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 167 members 1,828 guests
Please welcome our newest community members: