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 stuck trying to figure out the following calculated column (not even sure if this is possible but would like confirmation as it seems messy). I would like the column to sum up all values from the Timeware[Labour Time (decimal)] when there is an exception otherwise just use the value from Labours[Labour Time (decimal)].
When an exception occurs the column should sum up values from Timeware[Labour Time (decimal)] that occur where Labours[StartTime] >= Timeware[StartTime] and Timeware[EndTime] <= Labourers[EndTime].
In this example below I would expect Labour Time Column (with exception logic) to be 16.9
Labours
Id | StartTime | EndTime | FirstName | Finished on Same Date? | Labour Time (decimal) | StartTime + 14 Hrs | EmosUsers.UserId | Link_EmpId - Date | Labour Time Column (with exception logic) |
310 | 05/03/2020 08:15 | 09/03/2020 10:49 | Stephen | Different Day | 98.565 | 05/03/2020 22:15 | 225 | 05/03/2020 00:00:00-225 | 8678 |
Timeware
employee_id | date_and_time | startTime | endTime | Link_EmpId - Date | Labour Time (decimal) |
225 | 05/03/2020 00:00 | 05/03/2020 07:49 | 05/03/2020 17:30 | 05/03/2020 00:00 | 9.683333333 |
225 | 06/03/2020 00:00 | 06/03/2020 07:47 | 06/03/2020 15:00 | 06/03/2020 00:00 | 7.216666667 |
225 | 09/03/2020 00:00 | 09/03/2020 07:47 | 09/03/2020 17:30 | 09/03/2020 00:00 | 9.716666667 |
My current DAX is:
Labour Time Column (with exception logic) =
//Measure calculates labour time to include logic that takes the time in work from Timeware if the employee has been logged into the tablet for more than 14hrs//
var currentEnd = Labours[EndTime]
var currentStart = Labours[StartTime]
return
IF(
(Labours[EndTime]) > (Labours[StartTime + 14 Hrs]),
CALCULATE(
SUM(
(Timeware[Labour Time (decimal)] )),
FILTER(
Timeware,
Timeware[endTime] <= currentEnd && currentStart >= Timeware[startTime]),
USERELATIONSHIP( Timeware[employee_id], Labours[EmosUsers.UserId])),
(Labours[Labour Time (decimal)])
)
Solved! Go to Solution.
Hi @jcarville
The 10 thing is probably just field formating, for the second part, I would troubleshoot the code by, first removing the dates from CALCULATE to confirm if USERELATIOSHIP is working correctly and later confirming the logic by creating the table with all columns used in the formula into a table so you can see where the issue is.
Optionally you can create a sample with both tables so I can troubleshoot my myself.
Hi @jcarville ,
We can create a calculated column in Labours table to meet your requirement:
Column =
CALCULATE (
SUM ( 'Timeware'[Labour Time (decimal)] ),
FILTER (
ALL ( 'Timeware' ),
'Timeware'[employee_id] = EARLIER ( 'Labours'[EmosUsers.UserId] )
&& (
'Timeware'[startTime] <= EARLIER ( 'Labours'[StartTime] )
|| 'Timeware'[endTime] <= EARLIER ( 'Labours'[EndTime] )
)
)
)
Best regards,
Why are you not doing sum in else
IF(
(Labours[EndTime]) > (Labours[StartTime + 14 Hrs]),
CALCULATE(
SUM(
(Timeware[Labour Time (decimal)] )),
FILTER(
Timeware,
Timeware[endTime] <= currentEnd && currentStart >= Timeware[startTime]),
USERELATIONSHIP( Timeware[employee_id], Labours[EmosUsers.UserId])),
sum(Labours[Labour Time (decimal)])
)
sum(Labours[Labour Time (decimal)])
@amitchandak- I am using a DAX column so don't need to use SUM in the else. The else part of the calculation is working as expected, its just the part where I need to SUM the values for the exceptions that is causing the issue.
Hi @jcarville
try this
var currentEnd = Labours[EndTime]
var currentStart = Labours[StartTime]
return
IF(
Labours[EndTime] > Labours[StartTime + 14 Hrs],
CALCULATE(
SUM( Timeware[Labour Time (decimal)] ),
Timeware[endTime] <= currentEnd,
currentStart >= Timeware[startTime],
USERELATIONSHIP( Timeware[employee_id], Labours[EmosUsers.UserId] )
),
Labours[Labour Time (decimal)]
)
@Mariusz- thanks for the reply.
You're solution has brought me closer but it is not returning the value I'm after. It returned 10 (which is the first row in sheet below rounded up for column F) but I was expecting 16.9 (the sum of both rows)
Hi @jcarville ,
We can create a calculated column in Labours table to meet your requirement:
Column =
CALCULATE (
SUM ( 'Timeware'[Labour Time (decimal)] ),
FILTER (
ALL ( 'Timeware' ),
'Timeware'[employee_id] = EARLIER ( 'Labours'[EmosUsers.UserId] )
&& (
'Timeware'[startTime] <= EARLIER ( 'Labours'[StartTime] )
|| 'Timeware'[endTime] <= EARLIER ( 'Labours'[EndTime] )
)
)
)
Best regards,
Hi @Mariusz , @v-lid-msft thank you both for your help with this query. In the end both of your answers helped to guide me to the solution I needed so thanks for that 😁
Hi @jcarville
No problem, you welcome.
Hi @jcarville
The 10 thing is probably just field formating, for the second part, I would troubleshoot the code by, first removing the dates from CALCULATE to confirm if USERELATIOSHIP is working correctly and later confirming the logic by creating the table with all columns used in the formula into a table so you can see where the issue is.
Optionally you can create a sample with both tables so I can troubleshoot my myself.
check if this can help
@amitchandak - appreciate the reply but I couldn't find anything in your guide to help with my issue.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |