cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cottrera
Helper IV
Helper IV

DAX to determine chart colour

Hi when I publish reports to power bi service the chart colours I have keep changing. To overcome this I would like to use DAX to determin the colours that will be displayed in the chart.

 

Form example I have a dataset of teams A,B,C,D and the jobs they have completed. I would like to use conditional formating and DAX to determine what colour relates to what team.

 

For example if Team A then HEX 12239E , If Team B then HEX 118DFF and so on

 

**Please note I have tried manually setting chart colours with format , chart colours and advanced. This works on the desktop version  but the colours always change when published***

 

Thank you

Richard

1 ACCEPTED SOLUTION
MUsommer
Resolver II
Resolver II

Hi Richard,

 

I am not sure whether this will fix the issue with the colors changing when you publish, but using a lookup table for your legend values ensures that the colors remain the same across different visuals.

 

To use the same colors for e.g. different teams consistently across different visuals, you can create a table containing the name of each team and then create a relationship between the teams "lookup table" and "teams" in the tables you are working with. Once you create a visual, make sure you use the value for teams in your lookup table as legend. It could look something like this (where "teams" is the lookup table):

 

test.PNG

 

This will return return the same color for team A, B, etc. across the different visuals, but again, I'm not sure whether that will fix the issue with the colors changing after publishing (maybe this way the colors will always be consistently wrong at least 😁). The colors are based on the colors in your theme and can be changed by going to view -> themes (hit the little arrow on the right) ->customize current theme -> then changing color 1, 2, etc.

View solution in original post

7 REPLIES 7
Anand24
Super User II
Super User II

Hi @cottrera,

 

Which type of visual are you using? Is it line chart or column charts?
You certainly can't conditionally format colors in these types of graphs.

Also, the data color in your services version changes usually only when the categorical value also changes. Otherwise usually the color set by you won't change.

However, the DAX you are looking for is below:
SWITCH(TRUE(),
'Table'[Team] = "Team A", "#12239E",

'Table'[Team] = "Team B", "#118DFF"
)

 

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

Thank you for your response. One of the previous post resolved the issue I am expiriencing at the moment. However I will give your SWITCH idea ago with some other projects I will be working on in the future.

Hi I am using a tree chart. I ready many other posts where users have expireienced the same issues. The colours in desk top are displaying differently in power bi service

@cottrera,

You can't conditionally format in Treemap visual.
You can check for the issues here: https://community.powerbi.com/t5/Issues/idb-p/Issues

You can submit this as an idea here: https://ideas.powerbi.com/

 

You can raise a request with Microsoft and look for a possible solution here: https://powerbi.microsoft.com/en-us/support/

 

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

Thank you for your response and links greatly appreciated.

 

MUsommer
Resolver II
Resolver II

Hi Richard,

 

I am not sure whether this will fix the issue with the colors changing when you publish, but using a lookup table for your legend values ensures that the colors remain the same across different visuals.

 

To use the same colors for e.g. different teams consistently across different visuals, you can create a table containing the name of each team and then create a relationship between the teams "lookup table" and "teams" in the tables you are working with. Once you create a visual, make sure you use the value for teams in your lookup table as legend. It could look something like this (where "teams" is the lookup table):

 

test.PNG

 

This will return return the same color for team A, B, etc. across the different visuals, but again, I'm not sure whether that will fix the issue with the colors changing after publishing (maybe this way the colors will always be consistently wrong at least 😁). The colors are based on the colors in your theme and can be changed by going to view -> themes (hit the little arrow on the right) ->customize current theme -> then changing color 1, 2, etc.

View solution in original post

Thank you for your response. I am already using a similar model to the one you provided. However I tried the customize current theme -> then changing color 1, 2, etc and this seems to have resolved the issue I was experiencing with publishing and colours changing.

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

html:
Carousel MBAS ARun Part 1 768x460

MBAS 2021

Join Arun Ulagaratchagan and the Power BI team at MBAS! #MSBizAppsSummit #CommunityRocks

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!