cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Skilled Sharer
Skilled Sharer

Exception Logic DAX Column Help

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

IdStartTimeEndTimeFirstNameFinished on Same Date?Labour Time (decimal)StartTime + 14 HrsEmosUsers.UserIdLink_EmpId - DateLabour Time Column (with exception logic)
31005/03/2020 08:1509/03/2020 10:49StephenDifferent Day98.56505/03/2020 22:1522505/03/2020 00:00:00-2258678

 

Timeware

employee_iddate_and_timestartTimeendTimeLink_EmpId - DateLabour Time (decimal)
22505/03/2020 00:0005/03/2020 07:4905/03/2020 17:3005/03/2020 00:009.683333333
22506/03/2020 00:0006/03/2020 07:4706/03/2020 15:0006/03/2020 00:007.216666667
22509/03/2020 00:0009/03/2020 07:4709/03/2020 17:3009/03/2020 00:009.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 tablesRelationship between tables

10 REPLIES 10

Hi @jcarville 

 

No problem, you welcome.

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
LinkedIn

 

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors