Substract time columns from diff datasets

[LearnerMM]

Dear Mentors,

I need to substarct column "EST Start" from "EST" ...basically trying to calculate delay.

"EST" indicates schedule - Source is dataset1

"EST Start"  indicate actual start time - source is dataset2

Both dataset1 and dataset2 are connected using manage relationship.

Could you guide me how can I substract below columns from each other - to get delay in minutes? 

[v-karpurapud]

Hi @LearnerMM 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @LearnerMM 

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank You!

[Anonymous]

Hi @LearnerMM,

Any update on this? Can you please share some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[rohit1991]

Hi @LearnerMM 

Thanks for clarifying. If Power BI isn’t letting you select columns across datasets, it’s likely due to how the model is set up especially if you're using a composite model (Import + DirectQuery) or separate sources without an active relationship.

 

If Table1[EST] is the scheduled time and Table2[EST Start] is the actual time, and there's a one-to-many relationship from Table1 to Table2, you can write a calculated column in Table2 like this:

DelayMinutes = 
DATEDIFF(
    RELATED(Table1[EST]), 
    Table2[EST Start], 
    MINUTE
)

Double-check your relationship direction in the model view. Make sure you’re referencing from the correct side (many side → one side) when using RELATED(). If you're using different sources, consider merging them in Power Query or creating a bridge table with shared keys.

[LearnerMM]

Hey Thanks @rohit1991 @Deku @danextian  ...... actually problem is - It dose not pull out column name from other datasource.

We can substract 2 columns from same data source - but if you try to select another datasource columns , Power BI is not giving me option to select from other datasource. 

Can  Iconnect with anyone of you ? 

Thanks

Mangesh M

[danextian]

Can you please show your diagram/relationship view? If there's a direct  one-to-many relationship between the two tables, you should be able to use RELATED on the many side. The RELATED function cannot be used on the "one" side of a one-to-many relationship because there could be multiple matching rows in the related table, and RELATED does not perform aggregation.

[Deku]

Datediff( datum[EST start], datum[EST], minutes)

[LearnerMM]

Hey Thanks @Deku  ...... actually problem is - It dose not pull out column name from other datasource.

We can substract 2 columns from same data source - but if you try to select another datasource columns , Power BI is not giving me option to select from other datasource. 

Can  Iconnect with anyone of you ? 

Thanks

Mangesh M

[danextian]

Hi @LearnerMM 

By "manage relationship" you mean there is a direct relationship between the two tables or is there a dimension table in between? Which one's on the many side? Which one's on the one side? Some functions will work depending on which side of the relationship the table is? Please show your diagram.

[LearnerMM]

Yes,  there is a direct relationship between the two tables

[LearnerMM]

Hey Thanks @danextian  ...... actually problem is - It dose not pull out column name from other datasource.

We can substract 2 columns from same data source - but if you try to select another datasource columns , Power BI is not giving me option to select from other datasource. 

Can  Iconnect with anyone of you ? 

Thanks

Mangesh M

[tharunkumarRTK]

@LearnerMM 

Please provide the details of the relationship you created. For example if [EST] column is in Table1 and [EST Start] column is in Table2 and if I assume you created 
One to Many relationship between these two 

Table1 on one side and Table2 on the many side then you can follow this DAX measure syntax 

Delay = SUMX(Table2, Datediff(Related(Table1[EST]),  Table2[EST Start], Minute  ) )

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!