set a background color of a field based column categories with other column in Table or Matrix

[ramyavelampati]

Hi, 

I have a table with two columns "Category" and "Defect". Category have values like coding, planning, requirement etc with corresponding defects in Defect column. I want to bulid a table with background color formatting based on each category for given values of defects. 

For example:

If table(category) = "coding" and table(defect) = <10 then green

If table(category) = "coding" and table(defect) = >10 then red

If table(category) = "planning" and table(defect) = <30 then green

If table(category) = "planning" and table(defect) = >30 then red

Please help me resolve this. 

Thanks

[v-juanli-msft · Accepted solution]

Hi @ramyavelampati

First, the condition needs to be corrected. Since "=" symbol can only exsit in one condition, I assume the condition is:

If table(category) = "coding" and table(defect) = <10 then green

If table(category) = "coding" and table(defect) =    10 then red

If table(category) = "planning" and table(defect) = <30 then green

If table(category) = "planning" and table(defect) =    30 then red.

To achieve your goals, we should use conditional table formatting in Power BI.

Basically, we need to create a measure to detemine the color rule.

There are two ways.

1. create a IF condition in a measure.

Measure =
IF (
    MAX ( [category] ) = "coding"
        && MAX ( [defect] ) <= 10,
    1,
    IF ( MAX ( [category] ) = "planning" && MAX ( [defect] ) <= 30, 1, 0 )
)

2.create a conditional column in Power Query and then create a simple measure.

Measure 2 = IF(MAX([defect])<=MAX([Custom]),1,0)

Then you can set conditional formatting as the first way.

Best Regards

Maggie

[ramyavelampati]

This is dindn't work. As I have 3 different values for one category and these mesures change for other category as well. for example is 10 is green in coding, 12 will be green for planning, 20 will will be green for requirements. 

I already tried the meausre you mentioned, it is turning all the values into one color. 

[v-juanli-msft]

Hi @ramyavelampati

Generally, it will work in the way i provided, perhaps you are in a different scenario.

Could you show some data or screenshot to describe your problem clearly?

Let me check it again and I will come back to tell you.

Best Regards

Maggie

[Anonymous]

Hi @ramyavelampati

I have given solution to a similar issue here, please check and you can follow the same.

https://community.powerbi.com/t5/Desktop/Conditional-formatting-based-on-multiple-conditions/m-p/464...

Another similar post:

https://community.powerbi.com/t5/Desktop/Colour-Table-Cell-by-Text-Value/m-p/460960#M213853

Thanks

Raj