cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shamsuddeenvp Post Patron
Post Patron

how to sort a column by other column which is not used in the visual

Dear,

 

How can I sort a column by another column which is not used in visual in PowerBI. I know we can do this if the data source is SSAS, and from the cube model we can do. However if we use inmemory, how can we make use this feature?

 

This is must to have feature for time intelligence analysis.

 

Br,

SHamsuddeen

1 ACCEPTED SOLUTION

Accepted Solutions

Re: how to sort a column by other column which is not used in the visual

You need to go into the table/data view, click in the column to be sorted, click "sort by column" and then select the surrogate sort column"



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

7 REPLIES 7

Re: how to sort a column by other column which is not used in the visual

You need to go into the table/data view, click in the column to be sorted, click "sort by column" and then select the surrogate sort column"



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

shamsuddeenvp Post Patron
Post Patron

Re: how to sort a column by other column which is not used in the visual

Thanks @MattAllington.

 

I should start wearing glass:).

 

I was checking in both data tab aswell as query editor.

 

Br,

Shamsuddeen

 

 

Swethadesaik
New Member

Re: how to sort a column by other column which is not used in the visual

Hello Matt,

 

I do not see the surrogate column option that comes with sort column. Can you please let me know if this feature is specific to Power BI Premium or is there something i am missing?

I am using power bi pro.

 

Thanks,

Swetha K Desai

Swethadesaik
New Member

Re: how to sort a column by other column which is not used in the visual

Hello Matt,

 

I do not see the surrogate column option that comes with sort column. Can you please let me know if this feature is specific to Power BI Premium or is there something i am missing?

I am using power bi pro.

 

Thanks,

Swetha K Desai

Highlighted
ymicromed Advocate I
Advocate I

Re: how to sort a column by other column which is not used in the visual

Hello,

 

Add the column you want to use for sorting the graph to the "Tooltips" under the Values.

 

 

 

 PowerBI Sorting with unused Column.PNG

 

And then on the top right of the graph you will be able to choose this unused column in the graph to sort the data.

 

 PowerBI Sorting with unused Column1.PNG

 

I hope this works for you.

Mohamed

MaxW Helper II
Helper II

Re: how to sort a column by other column which is not used in the visual

I had this issue with sorting a table visual by two columns. I couldn't use 'sort by column' because the column I wanted to sort didn't have unique values. 

I got the error 'We can't sort the 'Column Name' column by 'sort order'. There can't be more than one value in 'Sort Order' for the same value in 'Column Name'. Please choose a different column for sorting'.

@ymicromed had a good workaround for charts, but there are no tooltips in table visuals. My workaround in the end was to add the 'Sort Order' column to the table visual, but set the column width to nothing to hide it. I had to turn off 'Word wrap' on the column header and values to keep the table tidy.

 

It would be nice if there was a workable 'two column' sort for this scenario, or if a field could be added to a visual purely for sorting. similar to how we can add visual filters without the field being in the visual.

Anonymous
Not applicable

Re: how to sort a column by other column which is not used in the visual

Try the following:

 

1) no object selected on the canvas

2) under Fields - click column you want to be sorted by another column 

3) on tab "Modeling" choose "Sort by column"

4) choose column to use as sort

 

If sort column is not unik create a new column that is example create YearMonth column to sort Month-Year column in the right order:

                 YearMonth = FORMAT('table'[Date];"YYYYMM")

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors