If statement conditional formatting based on two field values

[Anonymous]

Hi there,

I have a matrix visual below. I want to set up conditional formatting based on the value of the different types. For example, University Connection should have a colored background if the sum of January is below 2, but Street Team should have a background color if the sum of any month is below 4, and social media post below 12. The values are the same for each month but different for each type and I have 14 types total.

Is this possible? If so, can someone point me to a video or documentation? 

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Thanks for reaching out to us.

>>  I want to set up conditional formatting based on the value of the different types. For example, University Connection should have a colored background if the sum of January is below 2, but Street Team should have a background color if the sum of any month is below 4

You can try this, create the measure below

Condition formatting = 
var _type=MIN('Table'[Type])
var _month=MIN('Table'[Month])
return
SWITCH(TRUE(),
_type="University Connection",IF(CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Month]=_month&&'Table'[Type]=_type))<2,1),
_type="Street Team",IF(CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Month],'Table'[Type]))<4,1))

When type="University Connection", this measure sums the value of January, and judges whether it is less than 2. If it is, it returns 1.
When type="Street Team", sum the value of each month, and judge whether it is less than 4, if so, return 1.

then, create the measure

Measure = IF([Condition formatting]=1,"yellow")

In matrix, since row and column do not support conditional formatting, we set conditional formatting for value.

and if you have many types, then I'm afraid you'll have to manually add other types of rules in the first measure.

Best Regards,

Community Support Team _Tang

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

[Anonymous]

Yay! So that got me halfway there. It's not allowing me to enter a specific row. Do you know how I should format the formula to change only the row I want?

If I change [type] to [Street Team], it says it cannot find the name.

[amitchandak]

@Anonymous ,

Create a color measure and use that in conditional formatting using the field value option

if( [Street Team] < 4 && [social media] <12, "Red", "White")

How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/1682539

[Anonymous]

ay! So that got me halfway there. It's not allowing me to enter a specific row. Do you know how I should format the formula to change only the row I want?

If I change [type] to [Street Team], it says it cannot find the name.