topic Calculate Datediff between two tables with Many to Many Relationship in DAX Commands and Tips

Calculate Datediff between two tables with Many to Many Relationship

Anonymous — Mon, 25 May 2020 16:19:27 GMT

Hello,

I am trying to calculate the number of hours betwee two dates in two tables with a many to many relationship. The relationship between the tables is based on encounter id. Table 1 contains activation events and Table 2 contains drug administrations for each encounter. I need to determine whether there were 24 hours between the activation time in Table 1 and a related administration time in Table 2 for encounters. I can't figure out how to approach this problem.

Any help is sincerely appreciated.

Thank you.

Re: Calculate Datediff between two tables with Many to Many Relationship

Greg_Deckler — Mon, 25 May 2020 16:40:55 GMT

Anonymous

First thing, you should eliminate your many-to-many if at all possible. You can do this by creating a bridge table of distinct values between the two tables or you can create a combination key using concatenation of 2 or more columns.

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.