Forum Discussion
If statement conditional formatting based on two field values
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?
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.
4 Replies
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/1682539Hi 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.
