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?

Highlighted
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

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
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 97 members 1,573 guests
Please welcome our newest community members: