Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have session events data in a table like this, how would you approach calculating time duration per session?
As you can see, a red and a blue circles are 2 different sessions the data is sorted descending. The logic behind the calculation should be something like Max timestamp value - min timestamp value segmented by Session ID. So for the red circle it would be 4:53
Solved! Go to Solution.
Hi @Anonymous ,
Based on your description, you can create a measure as follows.
Here is my test table.
Test =
var x1=MAXX(FILTER(ALL('Table'),[session id]=SELECTEDVALUE('Table'[session id])),[timestamp])
var x2=MINX(FILTER(ALL('Table'),[session id]=SELECTEDVALUE('Table'[session id])),[timestamp])
return
x1-x2
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Based on your description, you can create a measure as follows.
Here is my test table.
Test =
var x1=MAXX(FILTER(ALL('Table'),[session id]=SELECTEDVALUE('Table'[session id])),[timestamp])
var x2=MINX(FILTER(ALL('Table'),[session id]=SELECTEDVALUE('Table'[session id])),[timestamp])
return
x1-x2
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
In power query, I would copy and rename the table (if you need the intermediate timestamps for something), rename timestamp to starttime, make a copy of it called endtime, then group by id and in the popup add min/max aggregation accordingly for start/endtime.
Edit: oh and then use the pre-existing time intelligence functions to get the duration from starttime and endttime columns.
Sounds very logical, will try this one thanks! Nevertheless, I am not sure what sort of intelligence functions you are talking about.
Here's a screenshot
@Anonymous , do you have any column like session id?
Yes, I do!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |