Colour format based on rule in separate column

[jonbox]

Hi, 

I have a fairly simple question but can't get the logic down on powerBI...

Currently using the traditional conditional formatting i'm only setting a colour based on one metric e.g. if over 0.9, green...

What I would like to do is take the "goals" column and look at each row e.g. on one row the goal is 1, next goal is 0.9 etc and based the way the next column is formatted based on those goals;

for example using the below image; the goal for the first and second row is 1, so the july coloumn should only show green if equal to that, whereas the fourth row goals is 100%, so should only show green if 100%. Finally the fifth row goal is 0.9, so july should only show green if equal or above 0.9.

Issue with the traditional conditional format feature is that you can only base a column off of one rule:

Any help with this would be massively appreciated. thanks!

[v-angzheng-msft · Accepted solution]

Hi, @jonbox 

Try this:

Measure = 
IF(
    NOT(ISBLANK(MAX('Table'[Jul]))),
    SWITCH(
        TRUE(),
        MAX('Table'[Goals])<=MAX('Table'[Jul]),"Green",
        "red"))

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@jonbox , You can create a color measure and use that in condition formatting using field value option

example

Color = if(FIRSTNONBLANK('Table'[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170
,"lightgreen",if(FIRSTNONBLANK('Table'[Year],2014)>2018,"red","yellow"))

Colour =
SWITCH(TRUE(),
max('Table'[Month Year]) = "Jul-2020" && [Measure] <.9, "red",

max('Table'[Month Year]) = "Jul-2020" && [Measure] >=.9, "Green",
Max('Table'[Month Year]) < "Aug-2020", "orange",
//keep on adding
"green")

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

[jonbox]

Hi Amit, 

Thanks for the reply. How would this fit in my use case? looking at you example i'm struggling to put it into place.

As i mentioned, i'd really like for the measure to look at the goals column for each row, see for row 2 the goal is one, check the july column for each row, see for row 2 if it is above or below the goal for that row and colour the july row 2 either green or red.

I'm quite new to using mesaures in powerbi so hence why i need the help!

Thanks again.