Forum Discussion

Rabi's avatar
Rabi
Icon for Resolver I rankResolver I
2 years ago

Conditionally Format values in column with blanks

Hi Everyone, 

 

I have Approved and Unapproved in the column header in a matrix and i am conditionally formatting the values if Approved red else Green, but there are blanks in the column and i want to format those blanks the colour assined to Approved and Unapproved.  How can i achieve this. Below is my dax measure

Color Status = 
IF(
    MAX('TableA'[Approved/Unapproved]) = "APPROVED",
    "Red",
    "Green"
)

Thanks for help !!

 

"DAX help"
"help" "targets" "tables" "Related"
"Need Help"

1 Reply

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Create a Measure for Conditional Formatting: Create a new measure that will be used for conditional formatting. Modify your existing measure to handle the blank values

     

    Color Status =
    IF(
    ISBLANK(MAX('TableA'[Approved/Unapproved])),
    "YourColorForBlank", -- Replace with the color for blanks
    IF(
    MAX('TableA'[Approved/Unapproved]) = "APPROVED",
    "Red",
    "Green"
    )
    )

     

    1. In the above DAX measure, the ISBLANK function is used to check if the value is blank. If it is, you can specify the color you want for blank cells. Adjust "YourColorForBlank" to the desired color.

    2. Apply Conditional Formatting: Now, apply conditional formatting to your visual (matrix or table) based on the new measure.

      • Select the column you want to format.
      • Go to the "Format" pane.
      • Find the "Conditional formatting" section.
      • Choose "Color scale" or "Font color" depending on your requirement.
      • Select the new measure ("Color Status") as the field to base the formatting on.

      Adjust the formatting rules to fit your color-coding needs.

    3. Adjust Colors for Blanks: Make sure that you've set the color for blanks in the formatting options.

      • If using "Color scale," you might need to set the color for the minimum or maximum value (whichever represents blanks).
      • If using "Font color," set the color for the value you specified for blanks in the DAX measure.

    This way, your visual will now use the conditional formatting measure to determine the color for each cell, including handling blanks according to your specifications.

     
     
     
     

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

     

    In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.