cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RvdC
Frequent Visitor

Adding Timestamp from a related table with a 1 to many relation under a certain condition

Hi,

I have the following issue. I have two tables in PowerBI. Table ‘Tasks’ and Table ‘Workorder’

There is a 1 to n relationship. The key is the workorderID. A Workorder can have multiple different tasks.

The Table Tasks:

 

Table Taks.png

 

The Table Workorder:

 

Table Workorder.png

 

I need a DAX query to calculate the throughput time between the workorder starttime and the workorder endttime. The workorder ends with the endtime in the Workorder table if there is no task started with a Team_ID ‘CLAIM_01’ or ‘CLAIM_02’. If there is a task started with Team_ID ‘CLAIM_01’ or ‘CLAIM_02’, the workorder endtime is the starting time of this task.

 

If it is possible to put the right endtime in case of a Team_ID with ‘CLAIM_01’ or ‘CLAIM_02’ into a new column in the Table Workorder, then it’s easy to calculate the throughputtime.

 

The endresult should look like this:

 

Table end result.png

 

In SQL the querys are:

 

ALTER TABLE Workorder

ADD Start_Claim_handling DATETIME, Throughputtime INT

 

UPDATE Workorder

SET Start_Claim_handling = T.ST

FROM Workorder

INNER JOIN (SELECT workorderID as WID, Start AS ST FROM Tasks

WHERE Team_ID = 'CLAIM_01' OR Team_ID = 'CLAIM_02') AS T ON 1=1

AND Workorder.workorderID = T.WID

 

UPDATE Workorder

SET Throughputtime = CASE WHEN Start_Claim_handling IS NULL THEN

DATEDIFF(day, WO_Start, WO_End) ELSE DATEDIFF(day, WO_Start, Start_Claim_handling) END

 

In SQL it’s fairly simple. I don’t know how to do this in DAX.

 

Thanks in advance for your help.

Rob van de Coevering

 

1 ACCEPTED SOLUTION
ERD
Solution Sage
Solution Sage

Hello @RvdC ,

You can try these 2 measures:

#Start_claim = 
CALCULATE(
    MAX('Tasks'[Start]),
    FILTER('Tasks', 
    'Tasks'[workorderID] = RELATED('Workorder'[workorderID])
    && 'Tasks'[TeamID] IN {"CLAIM_01", "CLAIM_02"}
    )
)

#ThroughputTime = 
VAR WOStartDate = MAX('Workorder'[WO_Start])
RETURN
IF(
    ISBLANK([#Start_claim]),
    DATEDIFF(WOStartDate,MAX('Workorder'[WO_End]),DAY),
    DATEDIFF(WOStartDate,[#Start_claim],DAY)
)

But, please, pay attention that they will only return 1 Start value and 1 ThroughtTime value per workorderID. In case there are several TeamID values (not null) then you need to decide what should be done:
find 1 maximum value per workorderID by measure or build a new table that will show all values matched from Tasks table.

View solution in original post

4 REPLIES 4
ERD
Solution Sage
Solution Sage

Hello @RvdC ,

You can try these 2 measures:

#Start_claim = 
CALCULATE(
    MAX('Tasks'[Start]),
    FILTER('Tasks', 
    'Tasks'[workorderID] = RELATED('Workorder'[workorderID])
    && 'Tasks'[TeamID] IN {"CLAIM_01", "CLAIM_02"}
    )
)

#ThroughputTime = 
VAR WOStartDate = MAX('Workorder'[WO_Start])
RETURN
IF(
    ISBLANK([#Start_claim]),
    DATEDIFF(WOStartDate,MAX('Workorder'[WO_End]),DAY),
    DATEDIFF(WOStartDate,[#Start_claim],DAY)
)

But, please, pay attention that they will only return 1 Start value and 1 ThroughtTime value per workorderID. In case there are several TeamID values (not null) then you need to decide what should be done:
find 1 maximum value per workorderID by measure or build a new table that will show all values matched from Tasks table.

View solution in original post

RvdC
Frequent Visitor

@ERD

 

Thanks for your help. Great job!

Regards,

Rob

amitchandak
Super User IV
Super User IV

@RvdC ,  Try a measure like this with common columns from one side

Measure =
var _1 = max(Tasks[Start])
return
datediff(min(Workorder[WO_Start]) , coalesce(max([WO_End]),_1), day)

 

or

Measure =
var _1 = max(Tasks[Start])
return
sumx( Workorder[Workorder ID]), datediff(min(Workorder[WO_Start]) , coalesce(max([WO_End]),_1), day))



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

Proud to be a Super User!

RvdC
Frequent Visitor

@amitchandak 

 

Thanks for your answers.

 

I tested the first measure (the second did not work). This gives the following results:

workorderID                      Measure  result           Should be

A                                             2                                            1

B                                             41                                          41

C                                             3                                             0

D                                            6                                             1

 

The measures is not working with the condition of de steps with CLAIM_01 or CLAIM_02. It should calculate the time difference in days between the WO_Start and the Start time of the record with CLAIM_01 or CLAIM_02 in it within the Task table.

Do you have a suggestion how to add such a condition?

Thanks.

Rob

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors