Forum Discussion
IF Statement Help
I need a little help with an IF statement as it's somewhat working but not fully.
The statement is this:
xMeasure = IF(Employee[Duration] >= 8, 1, Employee[Duration]/8)
What I'm trying to get to happen is that if an employee has worked 8 or more hours in a day, fill in xMeasure with a '1'. Else, divide their time by 8 to get some decimal number (i.e. 6 hours / 8 hours would be 0.75).
This is almost all working in the sense that it's doing the math on the IF FALSE part of the statement. But, if I have an employee that worked 9.25 hours, it fills in xMeasure with 1.16 vs. just a 1.
Data types all appear to be correct in that Employee[Duration] is a "Decimal Number" and xMeasure is a "Decimal Number."
Employee[Duration] is being calculated by: ( ( DATEDIFF ( Timecards[ActualStartTime], Timecard[ActualEndTime], SECOND ) ) /60 ) /60
4 Replies
Have you tried simply reversing the logic to remove any decimal issues that might be occuring?
xMeasure = IF(Employee[Duration] < 8, Employee[Duration]/8, 1)
I would assume since you are calculating the hours that somewhere a calculation is continuing into decimal places that are hidden.
Based on this, I did go back to the start. I decided to make a new calculated column that does nothing but takes the Employee[Duration] column and divides by 8. Low and behold a discrepency developed. Digging a bit further, I found that for every employee that simply clocks in followed by a clock out at the end of the day (meaning no break in between), the IF statement works fine. However, for the employees that are following the rules (a discussion for another forum) and clocking in in the morning, clocking out at lunch, clocking in after lunch, and clocking out at the end of the day (so - two time entries per day), the formula wasn't working.
The way the two times were being aggregated was by simply removing the two columns. So, I DO need to go back and look at that Employee[Duration] column formula and getting a little more specific on it.
I'll start another thread if I need help with that one.
Thank you for the response.
I hadn't tried that, but I did as you suggested. Interestingly enough, there was no change in any of the values. So, that gives me a couple of clues.
I need to correct my original question/problem/statement.
This column isn't a measure but rather a new calculated column. Not sure if that has any bearing on the result.
