cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Find duration from login and logout in same column

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 

 

 

Capture.PNG22

 

 

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
Super User III

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

 

 





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Highlighted

Hi @AliAlHamadi ,

 

Please check again. I just replicated the formula which @mahoneypat  has given and it gives the correct answers.

 

Pls check again.

 

1.jpg

 

 

 

Column =
VAR __thislogin = 'Table'[Time]
VAR __nextlogout =
CALCULATE (
MIN ( 'Table'[Time] ),
ALLEXCEPT ( 'Table', 'Table'[User ID] ),
'Table'[Time] > __thislogin,
'Table'[Event] = "OUT"
)
RETURN
IF (
'Table'[Event] = "IN",
DATEDIFF ( __thislogin, __nextlogout, MINUTE ),
BLANK ()
)

 

 

2.JPG

 

 

 

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

View solution in original post

4 REPLIES 4
Highlighted
Super User III
Super User III

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

 

 





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Highlighted

Unfortunately, this did not work. These are the results

 

Capture3.PNG

 

Some records are correct while others are wrong

Highlighted

Hi @AliAlHamadi ,

 

Please check again. I just replicated the formula which @mahoneypat  has given and it gives the correct answers.

 

Pls check again.

 

1.jpg

 

 

 

Column =
VAR __thislogin = 'Table'[Time]
VAR __nextlogout =
CALCULATE (
MIN ( 'Table'[Time] ),
ALLEXCEPT ( 'Table', 'Table'[User ID] ),
'Table'[Time] > __thislogin,
'Table'[Event] = "OUT"
)
RETURN
IF (
'Table'[Event] = "IN",
DATEDIFF ( __thislogin, __nextlogout, MINUTE ),
BLANK ()
)

 

 

2.JPG

 

 

 

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

View solution in original post

Highlighted

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors