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

[anjanikumar]

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

Login time is true ,Logout time is false.(Both are same column(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.

[PaulOlding]

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.

[anjanikumar]

Hi @PaulOlding ,

Thank you for your response !!

TimeSpent = SUMX(SUMMARIZE('Sample','Sample'[EID], 'Sample'[Timestamp]), VAR _MinLogin = CALCULATE(MIN('Sample'[Timestamp]),'Sample'[LogStatus] = TRUE()) VAR _MaxLogout = CALCULATE(MAX('Sample'[Timestamp]),'Sample'[LogStatus] = FALSE()) RETURN IF(_MinLogin && _MaxLogout && _MinLogin < _MaxLogout, _MaxLogout - _MinLogin ))

this below Error is coming while using that dax in calculated coulmn.please help me.

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

[serpiva64]

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 !

[tamerj1]

Hi @anjanikumar 

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

[anjanikumar]

Hi tamerj1,

Login time is true ,Logout time is false.

Thank you for your response !!

[tamerj1]

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

[anjanikumar]

<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

[tamerj1]

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

[anjanikumar]

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

[tamerj1]

@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. 

[anjanikumar]

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.

[tamerj1]

@anjanikumar 
Can you please a file with with sample no sensitive data?

[anjanikumar]

i tried as a measure but no luck@tamerj1.

below is the output value 

[tamerj1]

Hi @anjanikumar 
Click the link to download the file with the solution https://www.dropbox.com/t/ZfSaBSEPUWcHWiZz
The report looks like this

Please remember when you add EID and Date to the report you select "Don't Summarize"
The measure code is 

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

Please let me know if you face any issue. Have a great day!

[anjanikumar]

this measure i have written for that Time Spent column

Time Spent = VAR LogInTime = CALCULATE ( MINX ('Sample', 'Sample'[Timestamp]), Sample[LogStatus] = TRUE() ) VAR LogOffTime = CALCULATE ( MAXX ( 'Sample', 'Sample'[Timestamp]), 'Sample'[LogStatus] = FALSE()) RETURN LogOffTime - LogInTime

but that values are not correct.Any suggestion ?

[tamerj1 · Accepted solution]

@anjanikumar 
I think I have created some confusion by using the same name for both the mesure and the column. Please redownload the file here with proper names https://www.dropbox.com/t/6oAPYAPeyM5YkXEb

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

TimeSpent Column = 
VAR LogInTime = 
    CALCULATE ( MINX ( Data, Data[time] ), ALLEXCEPT ( Data, Data[EID], Data[Date] ), Data[SysStatus] = TRUE() )
VAR LogOffTime = 
    CALCULATE ( MAXX ( Data, Data[time] ), ALLEXCEPT ( Data, Data[EID], Data[Date] ), Data[SysStatus] = FALSE() )
RETURN
    LogOffTime - LogInTime

[anjanikumar]

Hi @tamerj1 

look into above image !!

am getting that values but those are not correct.  any suggestion .

[tamerj1]

Just change the data type to Time Short

[serpiva64]

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

[anjanikumar]

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