Power BI Conditional Formatting Function

This document provides a succinct solution for applying conditional formatting to the column RCA-1 in the table vw_RNTTP_SurveryCompletion based on the value of the rca1enabled date column.

Goal

• Apply red formatting to RCA-1 if:

  • rca1enabled equals 1 AND RCA-1 is null.

• Apply gray formatting to RCA-1 if:

  • rca1enabled equals 0.

DAX Code Snippet

ConditionalFormatRCA =
VAR IsRCA1Null = ISBLANK(Table[vw_RNTTP_SurveryCompletion[RCA-1]])
VAR IsRCA1EnabledOne = Table[vw_RNTTP_SurveryCompletion[rca1enabled]] = 1
VAR IsRCA1EnabledZero = Table[vw_RNTTP_SurveryCompletion[rca1enabled]] = 0
RETURN
    SWITCH(TRUE(),
        IsRCA1EnabledOne && IsRCA1Null, "Red", // If rca1enabled is 1 and RCA-1 is null, format as red
        IsRCA1EnabledZero, "Gray",             // If rca1enabled is 0, format as gray
        "Default"                               // Default case: other forms or no formatting
    )

Explanation of the Code

1. Variables:

   • IsRCA1Null: Checks if the RCA-1 column is null.
   • IsRCA1EnabledOne: Checks if the rca1enabled column equals 1.
   • IsRCA1EnabledZero: Checks if the rca1enabled column equals 0.

2. SWITCH Statement:

   • Returns "Red" if rca1enabled is 1 and RCA-1 is null.
   • Returns "Gray" if rca1enabled is 0.
   • Returns "Default" for any other condition, ensuring smooth handling of unexpected scenarios.

How to Use

• Navigate to the conditional formatting options in Power BI.
• Use the ConditionalFormatRCA measure in the formatting pane to set the background or font color of the RCA-1 column based on its value as described.

Example Scenario

Consider a dataset where the vw_RNTTP_SurveryCompletion table has the following values:

After applying the above DAX measure for conditional formatting:

• The first row with RCA-1 as null and rca1enabled as 1 will turn red.
• The second row (not null) will be unaffected.
• The third row with RCA-1 as null and rca1enabled as 0 will turn gray.
• The fourth row will remain unchanged as it meets the default condition.

Conclusion

This solution provides an efficient way to visually represent data conditions in Power BI using DAX. By applying the discussed DAX measure, you can enhance the readability and interpretability of your report effectively.

For advanced techniques and to further improve your skills in DAX and Power BI, consider exploring courses available on the Enterprise DNA platform.