cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jcarville
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

2 ACCEPTED SOLUTIONS
Mariusz
Community Champion
Community Champion

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

 

 

View solution in original post

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

 

9.jpg


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
amitchandak
Super User
Super User

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@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.

Mariusz
Community Champion
Community Champion

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

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
LinkedIn

 

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

 

Capture.PNG

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

 

9.jpg


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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 😁

Mariusz
Community Champion
Community Champion

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

 

Mariusz
Community Champion
Community Champion

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

 

 

View solution in original post

amitchandak
Super User
Super User

@amitchandak  - appreciate the reply but I couldn't find anything in your guide to help with my issue.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.