topic Re: How much time employee(EID) spent with system based on login and logout time. in DAX Commands and Tips

How much time employee(EID) spent with system based on login and logout time.

anjanikumar — Thu, 10 Mar 2022 10:05:50 GMT

As per above table, want measure for "How much time employee(EID) spent with system based on login and logout time"

output like this E1105D(EID) ->date(2/20/2022) -> time(18:35(true value) - 21:59(false value)) = 3:24 hours spent on that date with system.

please help me asap.

Re: How much time employee(EID) spent with system based on login and logout time.

serpiva64 — Thu, 10 Mar 2022 10:24:14 GMT

Hi,

sorry but i think that with your exemple is not possible to do anything.

ok 3:24 for the first

but the second start on 02/20/2022 and logout on02/23/2022?!?

then he logins twice ???

Re: How much time employee(EID) spent with system based on login and logout time.

tamerj1 — Thu, 10 Mar 2022 10:28:55 GMT

Hi anjanikumar

do you mean to say MIN (true value) - MAX (false value)?

Re: How much time employee(EID) spent with system based on login and logout time.

serpiva64 — Thu, 10 Mar 2022 10:31:54 GMT

please explicit the complete output for your data not only the first one in order to understand better

Re: How much time employee(EID) spent with system based on login and logout time.

anjanikumar — Thu, 10 Mar 2022 10:33:07 GMT

Hi tamerj1,

Thank you for your response !!

Re: How much time employee(EID) spent with system based on login and logout time.

serpiva64 — Thu, 10 Mar 2022 10:37:38 GMT

Sorry, but you have two consecutive login for the same Employee?

Re: How much time employee(EID) spent with system based on login and logout time.

anjanikumar — Thu, 10 Mar 2022 10:39:55 GMT

yes , will be login twice some times.

but the second start on 02/20/2022 and logout on02/23/2022?!? -- this might be date related issue. leave this.

but we will take it initial time and last time in same day.

Re: How much time employee(EID) spent with system based on login and logout time.

anjanikumar — Thu, 10 Mar 2022 10:43:55 GMT

yes , we have two logins for same employee, but will take it initial login time and last logout time in same day.

Re: How much time employee(EID) spent with system based on login and logout time.

serpiva64 — Thu, 10 Mar 2022 10:46:52 GMT

sorry, but there is not a logout in 2/23/2022 after the login at 17:17

Re: How much time employee(EID) spent with system based on login and logout time.

anjanikumar — Thu, 10 Mar 2022 10:53:25 GMT

leave it data @serpiva64 .

i want measure for initial login and last logout time in same day.(in between time difference needed).

Re: How much time employee(EID) spent with system based on login and logout time.

tamerj1 — Thu, 10 Mar 2022 11:09:34 GMT

Hi anjanikumar
Here a sample file with the solution https://www.dropbox.com/t/MxuzOBQwBl6QgZbX

Time Spent = VAR LogInTime = CALCULATE ( MINX ( Data, Data[time] ), Data[SysStatus] = TRUE() ) VAR LogOffTime = CALCULATE ( MAXX ( Data, Data[time] ), Data[SysStatus] = FALSE() ) RETURN LogOffTime - LogInTime

Re: How much time employee(EID) spent with system based on login and logout time.

anjanikumar — Thu, 10 Mar 2022 12:25:07 GMT

<pi>A circular dependency was detected: Sample[Timespent], 7ca15ac4-071a-4982-aa19-f14a8747771f, Sample[Timespent].</pi>

getting this error while writing above measure please help me

Re: How much time employee(EID) spent with system based on login and logout time.

tamerj1 — Thu, 10 Mar 2022 12:55:37 GMT

anjanikumar
Are writing a measure or a calculated column? Please make sure you create a measure

Re: How much time employee(EID) spent with system based on login and logout time.

serpiva64 — Thu, 10 Mar 2022 13:03:47 GMT

Hi,

you can try this in power query:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBBCgIxDEXvkvXAJL/tVLPWU5RZ6GJO4P0xIMhESOyi0MLjpXlj0EELPezcRbjd7AJeGSsYsIdctDTal0GvSS73QbRdJ3yAsgS+cp7bVaL//XI94OqZ27T2YG71+6LM+IyrX9/zw4H/dJ7hcp/vnHCus+fizjnXAy7unHCuc85Z5/0N", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SysStatus = _t, #"System#" = _t, EID = _t, date = _t, time = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"time", type time}}),
#"Filtered Rows f" = Table.SelectRows(#"Changed Type", each ([SysStatus] = "f")),
#"Filtered Rows t" = Table.SelectRows(#"Changed Type", each ([SysStatus] = "t")),
#"Grouped Rows1" = Table.Group(#"Filtered Rows f", {"EID", "System#", "date", "SysStatus"}, {{"Max", each List.Max([time]), type nullable time}}),
#"Grouped Rows" = Table.Group(#"Filtered Rows t", {"EID", "System#", "date", "SysStatus"}, {{"Min", each List.Min([time]), type nullable time}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"EID", "System#", "date"}, #"Grouped Rows1", {"EID", "System#", "date"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Max"}, {"Max"}),
#"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "Custom", each if [Max]>[Min] then [Max]-[Min] else 0)
in
#"Added Custom"

If this post is useful to help you to solve your issue consider giving the post a thumbs up

and accepting it as a solution !

Re: How much time employee(EID) spent with system based on login and logout time.

anjanikumar — Thu, 10 Mar 2022 13:41:57 GMT

i tried as a measure but no luck@tamerj1.

below is the output value

Re: How much time employee(EID) spent with system based on login and logout time.

tamerj1 — Thu, 10 Mar 2022 14:00:04 GMT

Just change the data type to Time Short

Re: How much time employee(EID) spent with system based on login and logout time.

anjanikumar — Fri, 11 Mar 2022 05:43:43 GMT

in calculated column same value is coming in all rows how to rectify this ?

Re: How much time employee(EID) spent with system based on login and logout time.

tamerj1 — Fri, 11 Mar 2022 06:19:01 GMT

anjanikumar
You need to create as a measure. Did you download the sample file? Create the measure, then create the table visual the same way as in the sample file.
If you still need a calculated column, please in the same screeshot above handwrite the expected results of the new calculated column.

Re: How much time employee(EID) spent with system based on login and logout time.

anjanikumar — Fri, 11 Mar 2022 06:25:05 GMT

already i have created both(measure and column) but getting same value in all rows ,that is the problem.

Timespent = MAXX(filter('Sample','Sample'[LogStatus]="False"),FORMAT('Sample'[Timestamp],"yyyy/MM/dd")-MINX(filter('Sample','Sample'[LogStatus]="true"), FORMAT('Sample'[Timestamp],"yyyy/MM/dd")))

this is calculated column please suggest any modification for that.

Re: How much time employee(EID) spent with system based on login and logout time.

PaulOlding — Fri, 11 Mar 2022 08:24:56 GMT

Hi anjanikumar

This is a measure to get logged in time, calculated for each EID & each day separately.

Logged In Time = SUMX( SUMMARIZE('Sample','Sample'[EID], 'Sample'[date]), VAR _MinLogin = CALCULATE(MIN('Sample'[time]),'Sample'[SysStatus] = TRUE()) VAR _MaxLogout = CALCULATE(MAX('Sample'[time]),'Sample'[SysStatus] = FALSE()) RETURN IF(_MinLogin && _MaxLogout && _MinLogin < _MaxLogout, _MaxLogout - _MinLogin ) )

The IF condition is checking there's a login time on that day, a logout time and the login is before the logout. If that isn't the case that day is ignored.