topic Calculating Duration from 2 tables in DAX Commands and Tips

Calculating Duration from 2 tables

BrandonL — Tue, 31 Mar 2020 20:32:50 GMT

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

Re: Calculating Duration from 2 tables

ibarrau — Wed, 01 Apr 2020 12:41:23 GMT

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,

Re: Calculating Duration from 2 tables

BrandonL — Wed, 01 Apr 2020 17:13:38 GMT

All 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.

Re: Calculating Duration from 2 tables

ibarrau — Wed, 01 Apr 2020 18:16:32 GMT

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,