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.
Hello,
i having problem to find duration from login and logout for same User ID.
however i find a solution in
Calculating time between check in and check out from different rows
and it give me this resulte
by using this DAX
Col1 =
CALCULATE (
MIN ( 'Employee-event'[Index] ),
FILTER (
ALLEXCEPT ( 'Employee-event', 'Employee-event'[UserID] ),
'Employee-event'[Index] > EARLIER ( 'Employee-event'[Index] )
&& 'Employee-event'[Event] = "OUT"
)
)
TIME OUT =
LOOKUPVALUE (
'Employee-event'[Time],
'Employee-event'[Index], 'Employee-event'[Col1]
)
Duration in Min =
IF (
'Employee-event'[Event] = "IN",
DATEDIFF ( 'Employee-event'[Time], 'Employee-event'[TIME OUT], MINUTE ),
BLANK ()
)
it's very good but i was unable to apply to huge data(over million rows) run out of memory
There is anyway to do it in Query Editor or other solution
Solved! Go to Solution.
Not sure why you have the LOOKUPVALUE step. Please try this expression instead:
Duration =
VAR __thislogin = 'Employee-event'[Time]
VAR __nextlogout =
CALCULATE (
MIN ( 'Employee-event'[Time] ),
ALLEXCEPT ( 'Employee-event', 'Employee-event'[UserID] ),
'Employee-event'[Team] > __thislogin,
'Employee-event'[Event] = "OUT"
)
RETURN
IF (
'Employee-event'[Event] = "IN",
DATEDIFF ( __thislogin, __nextlogout, MINUTE ),
BLANK ()
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @AliAlHamadi ,
Please check again. I just replicated the formula which @mahoneypat has given and it gives the correct answers.
Pls check again.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Not sure why you have the LOOKUPVALUE step. Please try this expression instead:
Duration =
VAR __thislogin = 'Employee-event'[Time]
VAR __nextlogout =
CALCULATE (
MIN ( 'Employee-event'[Time] ),
ALLEXCEPT ( 'Employee-event', 'Employee-event'[UserID] ),
'Employee-event'[Team] > __thislogin,
'Employee-event'[Event] = "OUT"
)
RETURN
IF (
'Employee-event'[Event] = "IN",
DATEDIFF ( __thislogin, __nextlogout, MINUTE ),
BLANK ()
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
This worked perfect for me to analysis employee badging data. Thanks!
Unfortunately, this did not work. These are the results
Some records are correct while others are wrong
Hi @AliAlHamadi ,
Please check again. I just replicated the formula which @mahoneypat has given and it gives the correct answers.
Pls check again.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Could you please help me ??
I have the same problem , but I want to turn the duration from log in and log out intu hh:mm:ss.
Also I have some users which are log in but they aren't log out that day..Could you please help me how to make a case in order to close the time for that user, I mean to count only the hours in that certain date.
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |