Forum Discussion
Substract time columns from diff datasets
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?
12 Replies
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.
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
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 twoTable1 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!
Yes, there is a direct relationship between the two tables
Datediff( datum[EST start], datum[EST], minutes)
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
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.
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
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.
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
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!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.
