Forum Discussion
Microsoft EmployeeCalculating Duration from 2 tables
I have 2 tables:
- First contains Start Time
- Second Contains End Time
How do i calculate the duration between these times using 2 unique id pivots: Session_ID & Player_ID as each "session" will have multiple player entries and each table will have multiple sessions. Also each player can participate in multiple sessions just not simultaneously.
| Event_ID | Player_ID | Session_ID | TimeStamp | Event |
| 16 | 1 | NGL_I6XtO | 3/20/2020 6:37:36 AM | Player_session_Start |
| 3216 | 2 | COOZB | 3/20/2020 9:54:30 AM | Player_session_Start |
| 11008 | 3 | NUDGX | 3/20/2020 10:04:06 AM | Player_session_Start |
| Event_ID | Player_ID | Session_ID | TimeStamp | Event |
| 500 | 1 | NGL_I6XtO | 3/20/2020 8:31:55 AM | Player_session_End |
| 22005 | 2 | COOZB | 3/20/2020 11:23:15 AM | Player_session_End |
| 19456 | 3 | NUDGX | 3/20/2020 12:45:32 PM | Player_session_End |
Hi. How is the relationship between the tables?
If you have a 1-1 then you have no problem you can do two things, merge them together or create a column in table 1 like:
DATEDIFF ( Table1[StartDate]; RELATED(Table2[EndDate]); HOUR )You can use datediff to get any interval of time you want.
If you have 1-* we should talk again, let us know the relationship.
Regards,
3 Replies
- ibarrau
Super User
Hi. How is the relationship between the tables?
If you have a 1-1 then you have no problem you can do two things, merge them together or create a column in table 1 like:
DATEDIFF ( Table1[StartDate]; RELATED(Table2[EndDate]); HOUR )You can use datediff to get any interval of time you want.
If you have 1-* we should talk again, let us know the relationship.
Regards,
BrandonLAll of the tables in this dashboard are linked as *-* unfortunately as each table can contain multiple rows with the same player_id or session_id. However there is only one row each table with the unique combination of a single player_id AND session_id together if that makes sesnse.
When working in the hierarchy it doesnt allow me to select any other type of relationships. Says its invalid.
- ibarrau
Did you try creating a new column with the combine values of player_id and session_id on each table and then relate them? That way you might get a result of 1-* or 1-1. If you have this you will be able to create the measure I have just suggested.
Be careful with the new column cases, be sure to have all trim and lower or upper case if they contain chars.
Regards,