condition formatting not working as expected

[avivbnbn24]

Hi, I have a matrix table that includes branch number in rows, date in columns, and issued card measure in values. I want to create a condition formatting in the value field.

in the value field I put a new measure that includes some conditions :

colored format =
VAR TargetValue = [issued_card]
VAR HoursValue = [working_hours]

VAR IsTargetEmpty = ISBLANK(TargetValue) || TargetValue = 0
VAR IsHoursEmpty = ISBLANK(HoursValue) || HoursValue = 0

RETURN
SWITCH(
TRUE(),
IsTargetEmpty && IsHoursEmpty, "#FF0000", // Red
IsTargetEmpty, "#FFA500", // Orange
BLANK() // No color
)

There are relationships between date and working_hours and between branches and working hours. and of course between date, branches, and issued card. 

For some reason, the formatting condition works only when I add the working hours to the visual. I don't want that measure to appear in the visual, but when I remove it, the condition formatting doesn't work as expected. 


help me out here guys

[v-bmanikante · Accepted solution]

Hi @avivbnbn24 ,

Thank you for reaching out to Microsoft Fabric Community Forum.

@johnt75 , @pankajnamekar25 , @bhanu_gautam Thank you all for your quick response.

Attached is a sample Power BI report with mock data that demonstrates the conditional formatting logic based on your scenario. Please check it out and let me know if you need help adjusting it to your actual dataset.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!


Regards,
B Manikanteswara Reddy

[avivbnbn24]

Do you know why it works only with 0 and not blank?

[avivbnbn24]

That helped me!

After I looked at your solution, I understood that the problem was with the measure [ISSUED_CARD].
When the value was 0, the condition formatting worked, but when it was blank, it didn't. So I changed all values to 0 instead of blank. 
It brings other problems, but things I could handle. Do 
you have any idea why it works only with 0 and not blank?

[avivbnbn24]

??

[johnt75]

The easiest way to debug this would be to store the result of the switch statement in a new variable, and then change the measure to return each variable in turn. Add the measure to the matrix so you can see the results, and you should be able to see which variable is not being computed in the way you planned.

[pankajnamekar25]

Hello @avivbnbn24 

You can try below measure

colored format =

VAR TargetValue = [issued_card]

VAR HoursValue = CALCULATE([working_hours])  // Force context propagation

VAR IsTargetEmpty = ISBLANK(TargetValue) || TargetValue = 0

VAR IsHoursEmpty = ISBLANK(HoursValue) || HoursValue = 0

RETURN

SWITCH(

    TRUE(),

    IsTargetEmpty && IsHoursEmpty, "#FF0000", // Red

    IsTargetEmpty, "#FFA500", // Orange

    BLANK() // No color )

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated

[bhanu_gautam]

@avivbnbn24 , Try using

DAX
colored format =
VAR TargetValue = [issued_card]
VAR HoursValue = CALCULATE([working_hours])

VAR IsTargetEmpty = ISBLANK(TargetValue) || TargetValue = 0
VAR IsHoursEmpty = ISBLANK(HoursValue) || HoursValue = 0

RETURN
SWITCH(
TRUE(),
IsTargetEmpty && IsHoursEmpty, "#FF0000", // Red
IsTargetEmpty, "#FFA500", // Orange
BLANK() // No color
)