The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
I do have in Power BI the follwoing transactional data where it shows when a user was logging on and off in an application:
What I need is to create a report where I show the following:
It might be the case that a user XYZ apeears in the transaction log with an on record but there is no off record yet because the user is still using the application.
Solved! Go to Solution.
Hi @Anonymous,
We can pivot the table and the times will pair automatically. You can check out the demo in the attachment.
1. Sort the column User first, then the column Timestamp;
2. Add an index;
3. Add a custom column;
if [Activity] = "on" then [Index] else null
4. Right click the Custom column, choose fill down;
5. Remove the Index column;
6. Click the column Activity and then click the Timestamp with "Ctrl", pivot these two columns;
7. Remove the column Custom.
8. Apply the changes;
9. Add a calculated column;
Duration = IF ( ISBLANK ( Table2[on] ), 9999999999, IF ( ISBLANK ( Table2[off] ), DATEDIFF ( TIMEVALUE ( [on] ), TIMEVALUE ( NOW () ), SECOND ), DATEDIFF ( [on], [off], SECOND ) ) )
10. Create a table visual, filter the [off] is blank, you can get the answer of your first question.
Best Regards,
Dale
Have a look at this article on Mean Time Between Failure (MTBF). Essentially the same type of calculation and you will want to use EARLIER. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Thanks @Greg_Deckler indeed you have a great article there. However I am not that lucky to have both timestamps in the same column. As you can see I do have the on/off events on different lines. Furthermore, I do have entries that look like the one below, since two sessions can run in parallel.
Activity Timestamp User
on 13:53:52 XYZ
on 13:55:30 XYZ
off 15:16:40 XYZ
off 15:16:43 XYZ
HI @Anonymous,
1. How to get the result 9 seconds of user CHTSI? It's 45 seconds in my opinion.
2. How to check which two are a pair when the sessions run in parallel?
Best Regards,
Dale
Hi @v-jiascu-msft (Dale),
1. the value of 9 seconds was an example, not a precise calculation. Sorry.
2. You don't know which two are a pair but you don't care because you need to calculate the total time an app was used. So any two sequetial pairs you select if you don't select the same row twice will give you the same sum. See an example below.
Transactions of user XYZ starting and stoping the app
ID Timestamp(sec) Type
1 t=1 start
2 t=2 start
3 t=4 stop
4 t=6 start
5 t=8 stop
6 t=9 stop
Lets say that somehow you know that the real pairs were (ID1, ID6) (ID2, ID3) and (ID4, ID5) so te total time that app was used by user XYZ would be (9-1)+(4-2)+(8-6)=(8+2+2)=12 sec
However during your calculation you selected start and stops sequentaly without knowing what really happened (ID1, ID3) (ID2, ID5) and (ID4, ID6) so the total time that the app was used by user XYZ would be claclulated to be (4-1)+(8-2)+(9-6)= 3+6+3=12 sec. The challenge in the last step is to not select (ID4, ID5) as the last pair since ID5 has been selected before in pair (ID2, ID5).
Hi @Anonymous,
We can pivot the table and the times will pair automatically. You can check out the demo in the attachment.
1. Sort the column User first, then the column Timestamp;
2. Add an index;
3. Add a custom column;
if [Activity] = "on" then [Index] else null
4. Right click the Custom column, choose fill down;
5. Remove the Index column;
6. Click the column Activity and then click the Timestamp with "Ctrl", pivot these two columns;
7. Remove the column Custom.
8. Apply the changes;
9. Add a calculated column;
Duration = IF ( ISBLANK ( Table2[on] ), 9999999999, IF ( ISBLANK ( Table2[off] ), DATEDIFF ( TIMEVALUE ( [on] ), TIMEVALUE ( NOW () ), SECOND ), DATEDIFF ( [on], [off], SECOND ) ) )
10. Create a table visual, filter the [off] is blank, you can get the answer of your first question.
Best Regards,
Dale
Awesome and neat solution! Thanks a lot Dale.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |