Forum Discussion
Conditional Formatting to Conditional Column if Null Field
Hello! I would like to apply conditional formatting to a conditional column I created in Power BI Desktop but I'm stumped. I have a table with four columns. The RTA_Current column is conditional and fills in if there is a date in RTA_Actual. The logic I'd like to apply is:
If RTA_Actual is not null
Change RTA_Current background color to gray
If RTA_Actual is null
Change RTA_Current background color to yellow if < 30 days from today
Change RTA_Current background color to red if < Today
Any ideas?
Hi BulldogTom ,
Here are the steps you can follow：
1. Create measure.
Color = var _datediff=DATEDIFF(MAX('Table'[RTA Current]),TODAY(),DAY) return IF( MAX('Table'[RTA Actual])<>BLANK(),"gray", IF( MAX('Table'[RTA Actual])=BLANK()&&_datediff<30,"yellow", IF( MAX('Table'[RTA Actual])=BLANK()&&MAX('Table'[RTA Current])<TODAY(),"red" )))2. [[RTA Current] – Conditional formatting – Background color.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
4 Replies
Hi BulldogTom ,
Here are the steps you can follow：
1. Create measure.
Color = var _datediff=DATEDIFF(MAX('Table'[RTA Current]),TODAY(),DAY) return IF( MAX('Table'[RTA Actual])<>BLANK(),"gray", IF( MAX('Table'[RTA Actual])=BLANK()&&_datediff<30,"yellow", IF( MAX('Table'[RTA Actual])=BLANK()&&MAX('Table'[RTA Current])<TODAY(),"red" )))2. [[RTA Current] – Conditional formatting – Background color.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Great work!
you could do it in a switch statement, and do some precalculations with the dates in a variable potentially to work out the time differences
https://learn.microsoft.com/en-us/dax/switch-function-dax
https://learn.microsoft.com/en-us/dax/datediff-function-dax
are you able to share your data in text format?
Thanks for taking a look. I'll read up on these options. Here is a data excerpt:
Project RTA RTA Actual B591 breakroom HVAC repairs 12/01/2022 01/18/2023 DDRT Add B811 to the DDRT FIS Contract 01/22/2024 DDRT B499A and B499B Fire Pump Repairs 10/27/2022 05/15/2023 DDRT B581 Restroom Partition Renovation 12/14/2022 12/14/2022 DDRT Renovation Building 552 Zone One Restrooms 03/31/2022 03/30/2022 DDRT Replace Carpet in B500 01/16/2024 DDWG Bldg. 368 Overhang Bay E South West 06/30/2023 DDRT B499 East Dock Canopy Replacement 1/22/2024 DDWG Bldg. 660 Damaged Fire Riser Repair 10/19/2022 10/19/2022 DDRT Inst Big Fans in B499 Z2 02/12/2024 DDNV Replace Dry Fire System Compressors 03/15/2024
