Forum Discussion

jcastr02's avatar
jcastr02
Post Prodigy
6 years ago

Conditional Formatting based on DATE and IS BLANK

I currently have a rule in place that if a data point is BLANK, then shade the table box RED.  I'd like to add another rule based on Class Start date.  If the class  start date is    MORE Than 7 days...
Need Help
jcastr02's avatar
jcastr02
Post Prodigy
6 years ago

Yes, I can find it.  I am using a table visualization - just to clarify on the below..    What am I entering for yourmeasure?     My other rule is set as per below as well...

 

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi jcastr02 ,

 

We can create Different Measure for each column in table using formula like following

 

ColorFormatForModule1 =
IF (
    DATEDIFF ( MAX ( 'Table'[Class Start Date] ), TODAY (), DAY ) <= 7,
    IF ( ISBLANK ( SUM ( 'Table'[Module 1] ) ), "#FFFF00" ),
    IF ( ISBLANK ( SUM ( 'Table'[Module 1] ) ), "#FF0000" )
)

 

ColorFormatForModule2 =
IF (
    DATEDIFF ( MAX ( 'Table'[Class Start Date] ), TODAY (), DAY ) <= 7,
    IF ( ISBLANK ( SUM ( 'Table'[Module 2] ) ), "#FFFF00" ),
    IF ( ISBLANK ( SUM ( 'Table'[Module 2] ) ), "#FF0000" )
)

 

Then we set measure as the Field Value for each column in table visual.

 

 

 


BTW, pbix as attached.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • jcastr02's avatar
    jcastr02
    Post Prodigy
    6 years ago

    v-lid-msft see attachment

     

    Trying to update field value with DAX but boxes are not turning colors....see attachment

    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi jcastr02 ,

       

      Is the "M1 - STAR Overview" a measure? If it is a measure, Does is just sum the value of "Module 1" field?

       

      Please also try to use the following measure as the Field Value of Conditional Format:

       

      ColorFormatForModule1 =
IF (
    DATEDIFF ( MAX ( 'Table'[Class Start Date] ), TODAY (), DAY ) <= 7,
    IF ( SUM ( 'Table'[Module 1] ) + 0 = 0, "#FFFF00" ),
    IF ( SUM ( 'Table'[Module 1] ) + 0 = 0, "#FF0000" )
)

       

      Best regards,

      Community Support Team _ Dong Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.