cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

How can i user sort by other column in live/direct query to SQL server table

Dear Team,

 

How can I sort a visual by a column which is not used in the visual but available in data set. I know we can do this in SSAS cube. I want to implement this for the live/direct query on top od SQL server table.

 

for example, months i need to sorted by month orders not by alphabets.

 

Thanks!
Shamsuddeen

14 REPLIES 14
Highlighted
Super User I
Super User I

@shamsuddeenvp You have to sort this in SSAS model itself as direct query does not have the ability to perform sorting.

Highlighted

Thanks @ashishrj. Yes, i know in SSAS as well as load option in Power BI we can do. wanted to check whether this feature is available in DQ as well, as this is musty have feature for date intelligence.

 

Also it would be very helpful if you have a look at my post 

http://community.powerbi.com/t5/Service/PowerBI-is-not-able-to-connect-with-live-ssas-tabular-model/...

 

I am still facing the issue. Seems the effective username issue (UPN has got domain name suffix in active directory). All connection/gateway are fine.

 

Thanks!
SHamsuddeen

 

Highlighted
Frequent Visitor

Direct query to the SQL Server database, you're out of luck on sorting. I would create a YYYYMM field and make it a TEXT field. The Year Month will show up as 201605 rather than 2016 May, but it's still better than 2016 April showing up before 2016 February.

Highlighted
Frequent Visitor

Well I was also breaking my head on getting this done, specifically with respect to month sorting based on the month number. With the help of my collegue we could find a solution.

On the PBI desktop, the "Fields" blade select the column you want to sort (the column based on which you want to sort should be present in the same table),  under the "Modelling" tab at the ribbon the "Sort by Column" is enabled and you can use any other column for sorting.

 

This works absolutely fine for the Direct Query mode. I have used this with Azure SQL

Highlighted

"This works absolutely fine for the Direct Query mode"

Err.. not for me it doesn't. The entire Modelling ribbon is disabled. 

I can sort by a column in the Tabular Model, but not by a measure value.... or can I? 

 

 

Highlighted

Not sure why the Modelling ribbon is disabled for you! Sort.png

 

This is how it looks for me in Direct Query mode.

 

And you cannot sort based on measure 😞

Highlighted

Hi,

 

Apologies for the delay, I finally found this reply. 

Looks like there's a functionality difference between Direct Query to SSAS, and Direct Query to SQL... and it also depends on the data type.

Cheers,

Reuben

 

 

 

Highlighted
Anonymous
Not applicable

Are this behavior and other limitations of Direct Query mode documented in a single place anywhere in Power BI documentation?

Highlighted
Anonymous
Not applicable

Are the limitations of Direct Query mode documented anywhere?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors