Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shamsuddeenvp
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

15 REPLIES 15
MdRahmatulla26
New Member

In Import / Direct Query : 

 

Click on the Column which needs to be sorted (i.e Month Name). and then click "Sort by Column" - You can select any columns (month Number) - based on that selected column to be sorted.

 

SSAS Live :

 

You cant use the above option. Either do the sorting at SSAS end ( Select Column, Right top corner - there is an option to sort by other column) 

 

Or,

 

Add the Monthname and Month Number column in the table. Then select the table and sort by Month Number (...). and go to Field property - change the Month Number columns font color and hide the column. this will be a workaround ( Not a solution ).

Anonymous
Not applicable

If you do not have access to edit the cube in Visual Studio and you have another column that you would like to sort, you can add this extra field as tooltip to any chart. After doing so, you can select it from the sorting menu.

 

 

Anonymous
Not applicable

Please note that this is for SSAS tabular model (assumes that you already created a model and connected online to PowerBI)

 

You have to do the sorting in SSAS tabular model using visual studio. For this, select the table and column to sort and click the button "Sort By Column".  This will give you the option to "Sort By" a particular column that need to sort.

 

Click OK and process the model and re-deploy it to see the results in Power BI

 

sort1.jpg

 

 

Choose Sort By column :

 

Sort2.jpg

Kavita
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

Wasn't working for me...   

 

Have a TEXT field with the Month names, and a numeric field for each month's number.

With the TEXT field as the X-Axis, I then select SortBy Column, selecting the numeric field; but I guess it's not sorting since the Axis is stuck on categorical

 

Added the numeric field to the Axis, then having both the text and numeric listed (text first). and voila, c'est bon

"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? 

 

 

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 😞

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

 

 

 

Anonymous
Not applicable

Are the limitations of Direct Query mode documented anywhere?

Hi,

 

I'm sorry, I don't know. This kind of thing seems to change quite frequently.

 

R

 

Confirmed - it still not working with October 2017 update, whereas custom sorting runs fine in cube preview (SSIS).

Anonymous
Not applicable

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

Anonymous
Not applicable

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.

ashishrj
Power Participant
Power Participant

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

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.