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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ShawnPrice
Helper I
Helper I

Conditional formatting for time

I"m looking for a way to do a conditional format on the table below. I have users who log in each day of the week. If they log in after 8:05:00 AM I want that day to highlighted in a Red.   Step 2 If possible then do a count of how red squares I have for that user.


23-Mar      24-Mar               25-Mar      26-Mar        27-Mar       30-Mar       31-Mar

8:15:00 AM 8:00:00 AM 8:01:00 AM 8:00:00 AM 7:59:00 AM 8:11:00 AM 7:56:00 AM

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@ShawnPrice 
Try follow the steps:

1. In query editor, select the two columns and then unpivot: 

unpivot.JPG

 

2. Close apply, create a flag measure

 

flag = IF(SUM('Table'[Value])>TIMEVALUE("8:05:00"),1,0)

 

 

3. Create a conditional format for Value column, rule by the flag measure

rule by flag.JPG

result color font.JPG

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

View solution in original post

7 REPLIES 7
V-pazhen-msft
Community Support
Community Support

@ShawnPrice 
Try follow the steps:

1. In query editor, select the two columns and then unpivot: 

unpivot.JPG

 

2. Close apply, create a flag measure

 

flag = IF(SUM('Table'[Value])>TIMEVALUE("8:05:00"),1,0)

 

 

3. Create a conditional format for Value column, rule by the flag measure

rule by flag.JPG

result color font.JPG

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

So it worked for a few of the times & users before 8:06, you can see some of them are working. Not sure why that would be, The Time fieid is a Time Data Type.Annotation 2020-06-03 081623.png

 

 

@ShawnPrice 

This is strange, only 6:34:00 AM gets wrong value, I have spent sometime on creating a few samples and did not met that issue. I will follow up when I figure it out. And please leave a update if you fixed it too.

 


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

Greg_Deckler
Super User
Super User

@ShawnPrice - Is your time an actual date/time or text? If it is actual date/time, then it is really a decimal number of 1/24/60/60 essentially and you could set you parameters accordingly.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

The Data Type is Time, the Format is h:nn:ss AM/PM

@ShawnPrice - OK, then your threshold should be .33680555555555556. That is 1/24/60 * (8 * 60 + 5)


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors