Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jcastr02
Post Prodigy
Post Prodigy

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
v-lid-msft
Community Support
Community Support

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.

@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

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.

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

 

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.

@v-lid-msft see attachment

 

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.