Sorting by a column not on a visual

[Anonymous]

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?

Column On TableColumn Not On Table

Version: 2.79.5768.1082 64-bit (March 2020)

[amitchandak]

@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

[Anonymous]

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.

[v-easonf-msft]

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

[Anonymous]

@v-easonf-msft 

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.

[v-easonf-msft · Accepted 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]

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?

[v-easonf-msft]

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

[Anonymous]

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