cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jcastr02 Member
Member

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 AGO, then shade red, if within the last 7 days, yellow.    

 

I'd like to combien both of those rules if blank and then the red/yellow combo

 

Please see screen shot. cond. formatting.png

8 REPLIES 8
Community Support Team
Community Support Team

Re: Conditional Formatting based on DATE and IS BLANK

Hi @jcastr02 ,

 

We can create a measure and set it as Field Value of Condition Format.

 

ColorFormat =
IF (
    TODAY () - MAX ( 'Table'[Class Start Date] ) <= 7,
    IF ( ISBLANK ( [YourMeasure] ), "#FFFF00" ),
    IF ( ISBLANK ( [YourMeasure] ), "#FF0000" )
)

6.PNG

 

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.

 

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 Member
Member

Re: Conditional Formatting based on DATE and IS BLANK

@v-lid-msft 

 

What should I put for "Your Measure" would it be for the point value for each of the modules, meaning I would have create several quick measures?         I never created a measure for the ISBLANK rule - just did a rule in conditional formatting.  

 

see screen shot.rule.png

jcastr02 Member
Member

Re: Conditional Formatting based on DATE and IS BLANK

Community Support Team
Community Support Team

Re: Conditional Formatting based on DATE and IS BLANK

Hi @jcastr02 ,

 

Sorry for late reply, Can you find the Format by Field Value in the Condition Format? What the visual are you using?

 

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.

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 Member
Member

Re: Conditional Formatting based on DATE and IS BLANK

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...

color format.png

 

Community Support Team
Community Support Team

Re: Conditional Formatting based on DATE and IS BLANK

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" )
)

3.PNG

 

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

 

4.PNG

 

5.PNG

 


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.

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 Member
Member

Re: Conditional Formatting based on DATE and IS BLANK

@v-lid-msft see attachment

 

Trying to update field value with DAX but boxes are not turning colors....see attachmentcolor format DAX.png

Community Support Team
Community Support Team

Re: Conditional Formatting based on DATE and IS BLANK

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.

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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)