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

[RvdC]

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:

The Table Workorder:

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:

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

[ERD · Accepted solution]

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.

[RvdC]

@ERD

Thanks for your help. Great job!

Regards,

Rob

[amitchandak]

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

[RvdC]

@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