Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
Community Champion
Community Champion

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.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

4 REPLIES 4
ERD
Community Champion
Community Champion

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.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

RvdC
Frequent Visitor

@ERD

 

Thanks for your help. Great job!

Regards,

Rob

amitchandak
Super User
Super User

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@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
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.