Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm wondering if this is possible. I have a data that contains break times for each person.
Driver Name | Break Time |
Person A | 9/21/2020 09:40:26 AM |
Person B | 9/21/2020 10:27:37 AM |
Person B | 9/21/2020 10:00:44 AM |
What I want is to create a table wherein the color the cell changes based on that person's break time vs current time. Below is the
color code to be followed:
1. Yellow: 45 to 60 mins before break time
2. Red: 44 or less minutes before break time
3. Gray: If on/after the break time
Here is an example on how it should look:
So the cell color changes based on the current/real time.
Please let me know if this can be achieved in Power BI.
Best regards,
Mark V.
Solved! Go to Solution.
Here are the steps you may take (I name the source table as ‘BreakTime’😞
1. Create a measure:
Time Diff = DATEDIFF(NOW(),MAX(BreakTime[Break Time]),MINUTE)
2. Use conditional formatting for Break Time: Format by Rules, Based on field Time Diff, and create the rules as you like.
Otherwise, you can also use format by field for background color. Create a measure:
Color Measure =
VAR __currentTime = NOW()
VAR __timeDiff = DATEDIFF(__currentTime,MAX(BreakTime[Break Time]),MINUTE)
RETURN
IF(__timeDiff < 60 && __timeDiff >= 45,"Yellow",IF(__timeDiff < 45 && __timeDiff > 0,"Red",IF(__timeDiff <= 0,"Gray","White")))
Best Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
Here are the steps you may take (I name the source table as ‘BreakTime’😞
1. Create a measure:
Time Diff = DATEDIFF(NOW(),MAX(BreakTime[Break Time]),MINUTE)
2. Use conditional formatting for Break Time: Format by Rules, Based on field Time Diff, and create the rules as you like.
Otherwise, you can also use format by field for background color. Create a measure:
Color Measure =
VAR __currentTime = NOW()
VAR __timeDiff = DATEDIFF(__currentTime,MAX(BreakTime[Break Time]),MINUTE)
RETURN
IF(__timeDiff < 60 && __timeDiff >= 45,"Yellow",IF(__timeDiff < 45 && __timeDiff > 0,"Red",IF(__timeDiff <= 0,"Gray","White")))
Best Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
Thank you for your response and assistance @amitchandak and @v-jingzhang. They are very helpful.
I used @v-jingzhang method which I suits my needs.
Many thanks to you both.
@markefrody , I did not get your logic completely. But You can have measure like
//Change logic as per need
Measure =
var _time = now() -[Break Time] // or datediff([Break Time] ,now(),min)
return
Switch ( true(),
_time >0 ,"Grey",
_time <-60 , "Red",
_time <-45 , "Yellow",
"White"
)
And use Conditional formatting with "Field Value" option
refer : https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |