Forum Discussion
Finding duration between two time rows grouped based on two other columns on Power BI
I have data in the below format, sorted by User asc, Session ID asc and Time asc. I need to find the duration each user spends on each Event within each session.
For example, using the below data:
| Event | User | Session ID | Time |
| a | 1 | x | 10/29/2024 9:37 |
| a | 1 | x | 10/29/2024 9:43 |
| a | 1 | x | 10/29/2024 9:43 |
| b | 1 | x | 10/29/2024 9:43 |
| b | 1 | x | 10/29/2024 9:44 |
| a | 1 | x | 10/29/2024 9:44 |
| a | 2 | y | 11/5/2024 6:53 |
| a | 2 | y | 11/5/2024 6:53 |
| a | 2 | y | 11/5/2024 6:53 |
| c | 2 | y | 11/5/2024 6:55 |
| c | 2 | y | 11/5/2024 6:55 |
| c | 2 | y | 11/5/2024 6:55 |
| b | 2 | y | 11/5/2024 6:55 |
| b | 2 | y | 11/5/2024 6:55 |
| a | 2 | y | 11/5/2024 6:55 |
| a | 2 | y | 11/5/2024 6:55 |
| a | 3 | z | 10/28/2024 6:34 |
I need help to create a summarised table in terms of the below:
| Event | session ID | Average Duration spent in minutes |
| a | x | 7 |
| a | y | 1 |
| a | z | 1 |
| b | x | 1 |
| b | y | 1 |
| c | y | 1 |
The duration spent is to be considered per User, per session.
I was able to get this easily on excel using formulas but wanted to replicate on powerBI, which I am newly exploring. I understand the excel formula logic doesnt work the same way on powerbi. Thank you!
Hi nk123 ,
As far as I know, if you want to get duration, you just need to get min and max date for each Event/User/SessionID.
Then the new table should looks like as below.
Summarized Table = VAR _T1 = SUMMARIZE('Table','Table'[Event],'Table'[User],'Table'[Session ID],"Duration", VAR _MIN = CALCULATE(MIN('Table'[Time])) VAR _MAX = CALCULATE(MAX('Table'[Time])) RETURN DATEDIFF(_MIN,_MAX,MINUTE) ) RETURN _T1Here I am confused about how you get the average. I can see some duplicate data in your sample. Does these data make scense?
And I think based on your sample, some duration will return 0 and the average will return 0 as well.
The result you want is incorrect. Please show more details for us.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Add a Calculated Column:
Duration (Minutes) =
VAR PrevTime =
CALCULATE(
MAX('Table'[Time]),
FILTER(
'Table',
'Table'[User] = EARLIER('Table'[User]) &&
'Table'[Session ID] = EARLIER('Table'[Session ID]) &&
'Table'[Event] = EARLIER('Table'[Event]) &&
'Table'[Time] < EARLIER('Table'[Time])
)
)
RETURN
IF(
ISBLANK(PrevTime),
0,
DATEDIFF(PrevTime, 'Table'[Time], MINUTE)
)Create a new Table:
Summarized Table =
SUMMARIZE(
'Table',
'Table'[Event],
'Table'[Session ID],
"Average Duration (Minutes)",
AVERAGEX(
FILTER(
'Table',
'Table'[Event] = EARLIER('Table'[Event]) &&
'Table'[Session ID] = EARLIER('Table'[Session ID])
),
'Table'[Duration (Minutes)]
)
)This table will contain Event, Session ID, and the average duration (in minutes) spent on each event per sessio
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedInHi nk123, Please try the below DAX to create a new table
Summarized Table =
SUMMARIZE(
YourTable,
YourTable[Event],
YourTable[Session ID],
"Average Duration (Minutes)",
AVERAGEX(
SUMMARIZE(
YourTable,
YourTable[User],
YourTable[Event],
YourTable[Session ID],
"Duration Per User",
SUM(YourTable[Duration])
),
[Duration Per User]
)
)Hi nk123 ,
As far as I know, if you want to get duration, you just need to get min and max date for each Event/User/SessionID.
Then the new table should looks like as below.
Summarized Table = VAR _T1 = SUMMARIZE('Table','Table'[Event],'Table'[User],'Table'[Session ID],"Duration", VAR _MIN = CALCULATE(MIN('Table'[Time])) VAR _MAX = CALCULATE(MAX('Table'[Time])) RETURN DATEDIFF(_MIN,_MAX,MINUTE) ) RETURN _T1Here I am confused about how you get the average. I can see some duplicate data in your sample. Does these data make scense?
And I think based on your sample, some duration will return 0 and the average will return 0 as well.
The result you want is incorrect. Please show more details for us.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
