Forum Discussion
Post PatronHow 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
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
"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!
This is how it looks for me in Direct Query mode.
And you cannot sort based on measure :(
RyanPAdvocate II
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
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 ).
- ashishrj
Power Participant
shamsuddeenvp You have to sort this in SSAS model itself as direct query does not have the ability to perform sorting.
shamsuddeenvpThanks 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
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
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
Choose Sort By column :
