cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gazzer Member
Member

Sort using the Sort by Column Button not Working

I am trying to sort a simple table of data using the Sort by Column button on the Modeling ribbon (and the one on the Modeling tab). I can never get it to work. I have followed the steps from the directions on https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-sort-by-column/ but the order never changes.

 

The column I am trying to use is a simple numeric index column, created using the built-in function. It doesn't work on any field I have tried, whether included in the table of displayed data or not.

 

Any suggestions welcome. 

1 ACCEPTED SOLUTION

Accepted Solutions
Vvelarde Super Contributor
Super Contributor

Re: Sort using the Sort by Column Button not Working

@Gazzer

 

Hi, Using your sample:

 

Select Description from Field Panel after that go To Sort By Column and Choose Index.

 

And Voila!!!. 

 

Like this:

 

Sort.gif

 

Victor

Lima - Peru

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

16 REPLIES 16
Super User
Super User

Re: Sort using the Sort by Column Button not Working

The target column ( column you want to sort) must have the identical grainularity of the sort column, and there must be a 1 to 1 match between the columns. So if you have a month name column, there are 12 unique values. The sort column must also have 12 values.  If 1 = Jan then 1 must always = Jan. The 1:1 match must never chabge



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Moderator v-sihou-msft
Moderator

Re: Sort using the Sort by Column Button not Working

@Gazzer

 

@MattAllington is correct. It must be 1 to 1 relationship, which means it can only be one value for each entry in to column you want to sort. Otherwise, it will throw error message like below:

 

6.PNG

 

Regards,

Gazzer Member
Member

Re: Sort using the Sort by Column Button not Working

Thanks for the reply - none of what you describe is mentioned in the documentation: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-sort-by-column/#sort-using-the-sor...

 

I will try what you have described, but at the moment it feels like it will be impractical to implement (I accept I could be wrong)

Gazzer Member
Member

Re: Sort using the Sort by Column Button not Working

Thanks for the info. However, I do not get any error message, the sort order simply does not change. 

 

I am unclear as to why there would need to be a 1:1 relationship (or any relationship) when I am using a field within the table I am attempting to sort. 

shill1000 Member
Member

Re: Sort using the Sort by Column Button not Working

Are you simply trying to sort a table by one column or is there a reason you are trying to use the 'Sort by Column' function?

 

The first can be done simply by using the table header to sort the table.

 

The second is an option to sort a visual by a column on a reference table. As indicated, the reference table must have a 1:1 relationship with the data in the table you want to sort. This is used when you have a specific sort order that needs to be applied, inctead of a generic alphnumeric sequence.

 

What exactly are you trying to achieve?

Gazzer Member
Member

Re: Sort using the Sort by Column Button not Working

I am trying to display the data in the natural order, rather than by one of the visible fields. Essentially, I want to sort the visible data by a field which exists in the same table but which I do not want to display.

 

Imagine my table has four fields. I want to display fields one, two and three in table form, but I want it to be sorted by the values in field four. Sorting by any of the three visible fields is unsuitable. In the transformation I have added an index column to provide the natural order value and it is this field I want to sort by, but not display.

 

According to the documentation, this should be possible:

 

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-sort-by-column/#sort-using-the-sor...

shill1000 Member
Member

Re: Sort using the Sort by Column Button not Working

OK, so you appear to want to use the function as intended. I know it seems a daft question, but have you checked the order of the column you want to sort by? If it is in the same order as the column you are trying to sort it almost certainly result in no change. Perhaps a sample of the 2 columns would help us identify the issue?

Gazzer Member
Member

Re: Sort using the Sort by Column Button not Working

Unfortunately I cannot show the actual data as it contains personal information. I can confirm that the natural order of the data is not the same as the column I wish to sort by - adding the index field in the displayed table shows this.

 

I will see if I can mock up something to demonstrate the issue without including personal data.

Gazzer Member
Member

Re: Sort using the Sort by Column Button not Working

I want to sort by the 'Index' column, but without including it in the display table, as per below. The data is currently sorting by the Description column.

 

Attempting to Sort by Col.png

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 222 members 2,130 guests
Please welcome our newest community members: