topic Re: Adding Timestamp from a related table with a 1 to many relation under a certain condition in DAX Commands and Tips

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

RvdC — Mon, 22 Mar 2021 13:29:28 GMT

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

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

amitchandak — Mon, 22 Mar 2021 16:12:07 GMT

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)

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

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

RvdC — Tue, 23 Mar 2021 07:05:22 GMT

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

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

ERD — Tue, 23 Mar 2021 09:35:58 GMT

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.

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

RvdC — Tue, 23 Mar 2021 15:40:49 GMT

ERD

Thanks for your help. Great job!

Regards,

Rob