cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
iGi Frequent Visitor
Frequent Visitor

Custom Sorting in PowerBI?

Hello all, 

 

Seeing as how helpful people were with my last two questions, I thought I'd ask another, which has been nagging me. 

 

I'm aware of the sorting function in graphs, but that seems to sort the items alphabetically. 

 

What I'm hoping to do is sort non-alphabetically, in an order that makes sense otherwise - highest to lowest, or whatever the case. 

See here: 

PowerBI Sorting Question.png

 

I'd like it to read Critical, High, Medium, Low. Is there anything I can do here? 

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
v-haibl-msft Super Contributor
Super Contributor

Re: Custom Sorting in PowerBI?

@iGi

 

You can create a table like following one and create relationship with the original table.

Custom Sorting in PowerBI_1.jpg

 

Then create a calculated column in original table with following formula.

Column = RELATED( Table2[ID] )

Custom Sorting in PowerBI_2.jpg

 

At last, select the Type column and make it sorted by above created column.

Custom Sorting in PowerBI_3.jpg

 

Custom Sorting in PowerBI_4.jpg

 

Best Regards,

Herbert

View solution in original post

18 REPLIES 18
v-haibl-msft Super Contributor
Super Contributor

Re: Custom Sorting in PowerBI?

@iGi

 

You can create a table like following one and create relationship with the original table.

Custom Sorting in PowerBI_1.jpg

 

Then create a calculated column in original table with following formula.

Column = RELATED( Table2[ID] )

Custom Sorting in PowerBI_2.jpg

 

At last, select the Type column and make it sorted by above created column.

Custom Sorting in PowerBI_3.jpg

 

Custom Sorting in PowerBI_4.jpg

 

Best Regards,

Herbert

View solution in original post

iGi Frequent Visitor
Frequent Visitor

Re: Custom Sorting in PowerBI?

@v-haibl-msft Wow! Didn't even think of doing that. Thank you! I'll give this a try to see if it will work with the larger table that I'm using here. 

 

On a separate note, how did you learn this? It resembles SQL to me. 

iGi Frequent Visitor
Frequent Visitor

Re: Custom Sorting in PowerBI?

@v-haibl-msft

Since I'm unfamiliar with this, I'm getting stuck at adding a new column and referencing the ID column from the newly created table. 

 

Here is where I am: 

 

1. Added a new table with Risk and ID columns:

New Table.png

 

2. Created a link between the two tables on Risk Rating and Risk:

Link.png

 

3. Clicked on Custom Column and the only columns available to me are those in the original, primary table and I don't know how to reference the new "Risk Order" table in the formula: 

Inserting column.png

 

Any advice? 

iGi Frequent Visitor
Frequent Visitor

Re: Custom Sorting in PowerBI?

@v-haibl-msft I got it! I was editing the query earlier, not realizing that I should just go through the Modeling tab under the table. 

Anyway, I was able to select "ID" from the new table: 

Formula.png

 

Sorted the Risk Rating column as you said, saved and the change took. 

 

Learned something new today. Smiley Happy

 

Thank you!

 

forzajoensy Frequent Visitor
Frequent Visitor

Re: Custom Sorting in PowerBI?

Great tips! Thanks! Its a long way around from what should be a custom sort or drag feature link in a pivot table. Will be available some time I guess.

brjones Frequent Visitor
Frequent Visitor

Re: Custom Sorting in PowerBI?

I thought this would solve my problem, but for some reason when I do the 'sort by column' step it does not sort by that column.  I made sure that no other columns had any kind of sort applied to them.

I created a new table based on an excel file.  

Then created a link between the new table and the one I wanted to sort by category.

Then did the sort by column step.  The screen flashed like it was doing something, but nothing happened.

 

The Table1 (T1) in my case is a query built from some other queries.

My Table2 (T2) is an excel file with two columns that I imported.

The linked column is labeled 'Reason Lost', with a 'Many to One' setting from T1 to T2, and cross filter set to 'both'

 

I would love some help with this, because the only other way I know to sort these correctly is to add an index in front of every item to get it to sort correctly, and that is visually distracting.

brjones Frequent Visitor
Frequent Visitor

Re: Custom Sorting in PowerBI?

 

Thanks so much for this!  

 

I had to delete some visualizations and re-create them in order for it to work correctly, but it's working beautifully now Smiley Happy

 

GREAT TIP!

lizaramlee Frequent Visitor
Frequent Visitor

Re: Custom Sorting in PowerBI?

im having same problem, its not sorted out.. do i  need to redo the visualisation?

ahxl Frequent Visitor
Frequent Visitor

Re: Custom Sorting in PowerBI?

Hi guys

 

This solution worked great for a situation where I had months - August, September and October not displaying in the correct order in my visual.

 

However, I was wondering, why did my creating of a calculated column in the SAME table using the following formula not work? So within the same table, I created a calculated column:

COLUMNSORT = IF('Table1'[MONTH] = "August", 1, IF('Table1'[MONTH] = "September", 2, IF('Table'[MONTH] = "October", 3)))

 

And then I tried to sort MONTH using this new calculated column 'COLUMNSORT', but it displays a message 'This column cannot be sorted by a column that is directly or indirectly sorted by this column'.

 

Why does it display this error message? I understand the logic that it cant sort the column because this calculated column directly sorts MONTH. So then why can it do a sort off a RELATED function which brings the exact same values into the main table? Isn't it the same thing basically?

 

Just trying to understand the logic in the two methods.

 

Thanks all

 

Regards

Ahxl

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 45 members 931 guests
Please welcome our newest community members: