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

Re: Modify the legend order

Stephan's solution looks perfect to my situation, but I don't see the "New group" option if if select the arrow. I only see the options "Delete field", "Change name" and "Show items whithout data". Has the "New group" option been deleted or am I looking at the wrong place?

dapster105 Regular Visitor
Regular Visitor

Re: Modify the legend order

@Kane

I would like to offer other web visitors what I think is an even better invisble solution in some circumstances, which is to prefix your text with a number of unicode 'zero width space' characters (https://www.fileformat.info/info/unicode/char/200B/index.htm).

 

Each of these characters has a code, and PowerBI will honour it in its default sorting, but the characters are not visible. Therefore you can add as many as you like. The more you add, the earlier the string will sort.

 

In my case, I do this in the SQL view definition which is the source of data for PowerBI e.g.

 

SELECT CASE [somenvarcharfield] WHEN 'alpha' THEN NCHAR(8203)+'alpha' WHEN 'beta' THEN...... 

 

e.g. producing the following strings in SQL...

NCHAR(8203)+N'alpha'

NCHAR(8203)+NCHAR(8203)+N'beta'

NCHAR(8203)+NCHAR(8203)+NCHAR(8203)+N'gamma'

 

Will produce the visible sort order in PowerBI...

gamma

beta

alpha

 

It may be possible to inject these unicode characters straight into the PowerBI user interface but I haven't tested this - the above works well for me and I generally stick to doing as much work in the view (rather than in PowerBI) as possible.

 

I hope this helps someone else.

 

Tim

Highlighted
mvarnado Occasional Visitor
Occasional Visitor

Re: Modify the legend order

The above solutions are valid, but I wanted to share my simple method.  YMMV.

 

I have a table of change tickets, and I want to sort them by Risk Level.  The values are Low, Medium, High, and Very High.  I want to have a stacked column in this order.  However, they list alphabetically and stack High, Low, Medium, Very High.

 

*  Create new column called "Risk Sort Order"

             RiskSortOrder = SWITCH('Change Tickets'[Risk Level], "Low", 1, "Medium", 2, "High", 3, "Very High", 4)

 

At this point, if you try to do "Sort By" on the Risk Level column by Risk Sort Order, you get the error that a column can not be sorted by a column it directly or indirectly affects (etc...  didn't screen shot it).

 

* Create another new column called "Risk Level Push" which will be a direct copy of the original Risk Level column

           RiskLevelPush = 'Change Tickets'[Risk Level]

 

* Select Risk Level Push column, Sort by Risk Sort Order

 

* Change Visual Legend to use Risk Level Push, rename at visual level "Risk Level"

 

And Viola!  Stacks are now in order as desired - Low, Medium, High, Very High.

 

 

This method works great when you have a small, finite number of legend items that you can predictably assign values.  Obviously, it's not going to work in a dynamic value situation.

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)