Forum Discussion
formatting time differences
Hi hastingse14
Download an example PBIX file here
I’d rewrite the measure so that it works out if a Time Diff is >7h or >10h and then decides what colour to use for the cell background, rather than return decimal values like 1 which you then have to write more code to decide colours
GT 7h = VAR _time_diff = SELECTEDVALUE('Time_Log'[Time Diff]) VAR _7h = 420 / 1440 VAR _10h = 600 / 1440 RETURN SWITCH( TRUE(), _time_diff > _10h, "red", _time_diff > _7h, "orange", "green" )The SWITCH function requires a default value which I have set to "green" here but if you don’t want a default (all cells will get this unless they are red or orange) then make it "" instead.
Click on the down arrow beside the Time Diff column in the visual and then select Conditional Formatting-> Background Color
Choose these settings and click OK
Your table should like something like this
Regards
Phil
Hi hastingse14
7 hours = 7/24 = 0.29
10 hours = 10/24 = 0.42
Calculated columns:
TimeDiff_Hours = Time_Log[Time Diff] * 24
/*To show the value in hours (rather than fraction of 24)*/
GT_7h_Column = IF( Time_Log[Time Diff] > (7/24), 1, 0 )
GT_10h_Column = IF( Time_Log[Time Diff] > (10/24), 1, 0 )
Measures (for aggregated checks in visuals or cards)
GT 7h (Measure) = IF( SUM(Time_Log[Time Diff]) > (7/24), 1, 0 )
GT 10h (Measure) = IF( SUM(Time_Log[Time Diff]) > (10/24), 1, 0 )
Conditional formatting:
/*Create a measure that returns a hex color depending on the row’s hours*/
TimeDiff_Color =
VAR hours = SELECTEDVALUE(Time_Log[Time Diff]) * 24
RETURN
SWITCH(
TRUE(),
hours > 10, "#D32F2F", // red for > 10h
hours > 7, "#FBC02D", // amber for > 7h
"#FFFFFF" // white (default)
)