Exception Logic DAX Column Help

[jcarville]

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)])
)

Relationship between tables

[Mariusz · Accepted 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.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn