Forum Discussion

BrandonL's avatar
BrandonL
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

Calculating 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 ha...
  • ibarrau's avatar
    ibarrau
    6 years ago

    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, 

ibarrau's avatar
ibarrau
Icon for Super User rankSuper User
6 years ago

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, 

  • BrandonL's avatar
    BrandonL
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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.

    • ibarrau's avatar
      ibarrau
      Icon for Super User rankSuper User
      6 years ago

      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,