Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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 ).
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.
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 :
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!
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
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).
Are this behavior and other limitations of Direct Query mode documented in a single place anywhere in Power BI documentation?
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.
@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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |