DAX to determine chart colour

[cottrera]

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

[Anand24]

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 !!!

[cottrera]

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.

[cottrera]

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

[Anand24]

@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 !!!

[cottrera]

Thank you for your response and links greatly appreciated.

[Anonymous · Accepted solution]

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

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.

[cottrera]

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.