Forum Discussion

r9nv9's avatar
r9nv9
New Member
3 years ago
Solved

Summarize and group by (??)

Not sure that's the correct way to phrase it, but it gets us started. 

 

I have a dataset similar to the following:

 

 

I want to summarize the data by ID based on different activity types as such,

 

Time to launch app = launchapp datetime - login datetime

Time to start work = startwork datetime - login datetime

 

 

I've been working way to long on this and it's time I simply asked the true experts.

Need Help
  • tackytechtom's avatar
    tackytechtom
    3 years ago

    Hi r9nv9 ,

     

    Here a way on how to achieve the desired result by creating a new table:

     

    Table_New = 
SUMMARIZE (
    'Table',
    'Table'[ID],
    "Time to launch app", DATEDIFF ( CALCULATE ( SELECTEDVALUE ( 'Table'[DateTime] ), 'Table'[Activity] = "Login" ), CALCULATE ( SELECTEDVALUE ( 'Table'[DateTime] ), 'Table'[Activity] = "LaunchApp" ), MINUTE ),
   "Time to start work", DATEDIFF ( CALCULATE ( SELECTEDVALUE ( 'Table'[DateTime] ), 'Table'[Activity] = "Login" ), CALCULATE ( SELECTEDVALUE ( 'Table'[DateTime] ), 'Table'[Activity] = "StartWork" ), MINUTE )
)

     

    I am not sure though, if that is what you are looking for. You might be more interested in two separate measures. Here an example:

     

     

    Measure_time_to_launch_app = 
DATEDIFF ( 
    CALCULATE ( 
        SELECTEDVALUE ( 'Table'[DateTime] ), 
        'Table'[Activity] = "Login" 
    ), 
    CALCULATE ( 
        SELECTEDVALUE ( 'Table'[DateTime] ), 
        'Table'[Activity] = "LaunchApp" 
    ), 
    MINUTE 
)
    Measure_time_to_start_work = 
DATEDIFF ( 
    CALCULATE ( 
        SELECTEDVALUE ( 'Table'[DateTime] ), 
        'Table'[Activity] = "Login" 
    ), 
    CALCULATE ( 
        SELECTEDVALUE ( 'Table'[DateTime] ), 
        'Table'[Activity] = "StartWork" 
    ), 
    MINUTE 
)

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

2 Replies

  • tackytechtom's avatar
    tackytechtom
    Icon for Most Valuable Professional rankMost Valuable Professional
    3 years ago

    Hi r9nv9 ,

     

    Here a way on how to achieve the desired result by creating a new table:

     

    Table_New = 
SUMMARIZE (
    'Table',
    'Table'[ID],
    "Time to launch app", DATEDIFF ( CALCULATE ( SELECTEDVALUE ( 'Table'[DateTime] ), 'Table'[Activity] = "Login" ), CALCULATE ( SELECTEDVALUE ( 'Table'[DateTime] ), 'Table'[Activity] = "LaunchApp" ), MINUTE ),
   "Time to start work", DATEDIFF ( CALCULATE ( SELECTEDVALUE ( 'Table'[DateTime] ), 'Table'[Activity] = "Login" ), CALCULATE ( SELECTEDVALUE ( 'Table'[DateTime] ), 'Table'[Activity] = "StartWork" ), MINUTE )
)

     

    I am not sure though, if that is what you are looking for. You might be more interested in two separate measures. Here an example:

     

     

    Measure_time_to_launch_app = 
DATEDIFF ( 
    CALCULATE ( 
        SELECTEDVALUE ( 'Table'[DateTime] ), 
        'Table'[Activity] = "Login" 
    ), 
    CALCULATE ( 
        SELECTEDVALUE ( 'Table'[DateTime] ), 
        'Table'[Activity] = "LaunchApp" 
    ), 
    MINUTE 
)
    Measure_time_to_start_work = 
DATEDIFF ( 
    CALCULATE ( 
        SELECTEDVALUE ( 'Table'[DateTime] ), 
        'Table'[Activity] = "Login" 
    ), 
    CALCULATE ( 
        SELECTEDVALUE ( 'Table'[DateTime] ), 
        'Table'[Activity] = "StartWork" 
    ), 
    MINUTE 
)

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

    • r9nv9's avatar
      r9nv9
      New Member
      3 years ago

      SELECTEDVALUE is what I was missing.  Thanks!  This works perfectly.