Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
MattAllington
Community Champion
Community Champion

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

View solution in original post

17 REPLIES 17
Anonymous
Not applicable

The Solution is not working

mattved
Advocate I
Advocate I

Why is this topic flagged as solved when it isn't?! The solution does not work and you are wasting my time.

For visuals like Bar Charts etc. the solution of @ymicromed worked well for me: to add the column you wish to sort the graph by, under 'Tooltips'.

this worked for bar charts. Thanks!

MaxW
Advocate II
Advocate II

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

what would be the benfit of hiding and word wrap, could you please attach screen shot 

Anonymous
Not applicable

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")

 

MattAllington
Community Champion
Community Champion

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

I have added a new measure that is calculated by a DAX function. When I click this custom column in the table/data view, the tab does not have the "sort by column" option, but it does when I click a column that is part of the table schema. Are there limitations to which columns can have the "sort by column" or is there a different way to sort custom measures?

 

PowerBI Desktop Measure Tools ribbon does not display column tools for custom sort optionsPowerBI Desktop Measure Tools ribbon does not display column tools for custom sort options

I tried your method. The data tab is now sorted with the column I desire, but my matrix in the Report tab, still use the matrix column to sort. 

I guess the "Sort by column" function doesn't carry the magic to matrix?

Anonymous
Not applicable

@Zhengy , I think I figured it out.  You'll need to have a sortable column (I used YearMonthInt mentioned in previous posts) in the date table.  What we were missing is that you have to select the problem column (MMM-YY for me), and sort it  by column YearMonthInt.  So Sort By Column step is done with problem column selected, not solution column.

YES!!!! Thank you!

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

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

Kudos to you!! 

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

Thanks @MattAllington.

 

I should start wearing glass:).

 

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

 

Br,

Shamsuddeen

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.