Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All
I need to display the average login time in a card visual, which can be filtered using a User name slicer. Since some users log in and out multiple times per day, we only require the average of the earliest login time recorded each day.
Example: We Would only need the average time of te first login time each day.
User - Name | User - ID | Action | Date | Time |
User 1 | 001 | Login | 2024/04/16 | 07:36:25 |
User 1 | 001 | Log Out | 2024/04/16 | 17:05:33 |
User 1 | 001 | Login | 2024/04/15 | 07:38:48 |
User 1 | 001 | Log Out | 2024/04/15 | 09:22:05 |
User 1 | 001 | Login | 2024/04/15 | 14:06:52 |
User 1 | 001 | Log Out | 2024/04/15 | 17:00:55 |
User 1 | 001 | Login | 2024/04/14 | 08:03:25 |
User 1 | 001 | Log Out | 2024/04/14 | 13:01:36 |
User 1 | 001 | Login | 2024/04/14 | 13:59:52 |
User 1 | 001 | Log Out | 2024/04/14 | 16:49:23 |
I am looking for your support.
Thanks in advance.
Solved! Go to Solution.
@HotChilli , thanks for your concern about this case.
Hi @RudolphVan
You can use the following dax to get the first login time each day:
Column = CALCULATE(MIN('Table'[Time]),FILTER('Table','Table'[Date] = EARLIER('Table'[Date]) && 'Table'[Action] = "Login"))
Then you can try converting the time to its corresponding number of seconds, calculating the average of those seconds, and then converting back to the time format.
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@HotChilli , thanks for your concern about this case.
Hi @RudolphVan
You can use the following dax to get the first login time each day:
Column = CALCULATE(MIN('Table'[Time]),FILTER('Table','Table'[Date] = EARLIER('Table'[Date]) && 'Table'[Action] = "Login"))
Then you can try converting the time to its corresponding number of seconds, calculating the average of those seconds, and then converting back to the time format.
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks a bunch, this solved my problem.
Average of a time? That doesn't sound right. What would that be for the data shown?
User | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
91 | |
87 | |
47 | |
28 | |
22 |