Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I will first say that this needs fixed. The solution found here is just too annoying to be useful, there should be a simpler solution. As a note, the instructions in the article do not work if you are pulling from a shared data source as you don't have the option to go to the modeling tab and chose a column to sort by.
On that note:
I have created a table visualization to show counts. The problem is I need it sorted by a column not on the visual. Using the article I defined a column to sort on. This works when the column is present when it's in the table, however when I pull the column from the table, the sort no longer works.
Can anyone let me know what I'm doing wrong?
Version: 2.79.5768.1082 64-bit (March 2020)
Solved! Go to Solution.
Hi, @Anonymous
Do you have the right selection for the columns you want to sort?
Here is the demo.
Try again follow these steps:
1.make sure you have selected column" ClosedMMYY_Display" you want sort in table view ,not other column
Then sort column "ClosedMMYY_Display" by column "ClosedMMYY"
The table visual will change as follows
("ClosedMMYY_Display" have sorted by column "ClosedMMYY" in ascending order now)
What you need is to reclick "ClosedMMYY_Display" to sort column "ClosedMMYY_Display" in descending order
you can also set it in table visual options
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
The objective of the sort column is that you do not need to add that. I doubt your visual is not sorted on month year. It is not defined correctly.
In data view when Month Year is selected at that time column Close Month day should be sort column or should be selected as sort column
I don't understand what you mean by the statement: "I doubt your visual is not sorted on month year" looking at the attached images, it obviously is not sorted correctly.
When the sorting column is not present, it reverts to an alphabetic sort.
Regardless, per the MS Article, I should be able to go to the modeling tab and choose a sort order when the column is not on the visual. As the modeling sort order is set to a column not on the visual, why isn't the visual sorting per the modeling tables sort? This table was created specifically for this example and I have not clicked on any column headings to force a different sort.
Hi , @Anonymous
I can not determine the possible cause without specific test data .If you can share me some sample data ,it will be better .
And here is a demo. It works well in my test.
1.Usually a column is sorted by itself (show as below)
The table visual will also sort by the first field applied in the table by defaul
2.If you click the column "index" and make it short by "Value" in data view
The table visual will sort by "Value" first , then short by "Index"
Even if you only reserved index field, it will still sort like this by default.
You also can click "Value" then shift +click "Index" in a table visual to achieve the same result.
Therefore, please ensure that the data format is correct so that all fields themselves can be sorted correctly, and there is no error in sorting steps.
Best Regards,
Community Support Team _ Eason
The file is here with the data.
In the modeling tab I selected this option. I also tried to do the sort on the query itself.
Hi, @Anonymous
Do you have the right selection for the columns you want to sort?
Here is the demo.
Try again follow these steps:
1.make sure you have selected column" ClosedMMYY_Display" you want sort in table view ,not other column
Then sort column "ClosedMMYY_Display" by column "ClosedMMYY"
The table visual will change as follows
("ClosedMMYY_Display" have sorted by column "ClosedMMYY" in ascending order now)
What you need is to reclick "ClosedMMYY_Display" to sort column "ClosedMMYY_Display" in descending order
you can also set it in table visual options
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I can understand your point, it seems counter-intuitive to have to sort a column on the visual by the column you want to sort by. The visualization should just have a "sort column" option or something on it.
This solution also fails if the sort value is not unique to the matching column. If a date has multiple sort values (let's say that for a day, you have a status, and the sort order is a combination of the date and status), the date will have as many sort values as the status. I'm not saying this is a smart situation, but it is just a point.
Also, this does not work in a Live Connection model unless the people developing the data source set up the sort columns relationship at that level. People on the data building side may not know how the visualization team builds their pages or different teams using the same table may sort differently. In the latter case, the data team cannot set a sort column and it's up to the visualization teams. With the current solution, it is not possible to do it at the front end side. This is the problem I experience, without complete control in the front end, I am at the mercy of other teams to resolve the problem. Using due diligence the data source wouldn't change until everyone using the tables knows that their visuals are not affected.
Is there something I'm missing when connected to a Live source which lets me do this sorting?
I'll mark your last as accepted even though it does not resolve the concern. If this is 'by design' then can you point me to an idea which I need to vote for or where I should put in the idea?
Hi , @Anonymous
If what you want is just to make the sort colum invisible in table visual ,maybe you also can try to change the width of the column in the table visual to 0.
So far, there is no unified and clear solution in Powerbi Desktop.Due to data limitations in liveconnect connection mode, we can do even less to do this sort.For some visualizations (line chart ,bar chart ... ) ,you can try to add a field to the tooltip and then sort by it. Refer to see video for more details. But it is not applicable to table visual.
You can come up with a new idea here / vote for existing ideas and add you comment to improve function.
Best Regards,
Community Support Team _ Eason
Hello,
I am not seeing an option to hide or set a column width to 0. I can pull the column over, but it does still appear that the user could expand the column itself.
I do not see an option for a line chart, but will look at others.
I understand the limitations when doing a live connection, this is why I don't understand why it's not controlled in another manner at the visualization bit, not the datasource.
Thanks for the help