Using an event log to find out who is logged into an application (not powerbi app)

[joschae]

All of my attempts have failed trying to use a log to find out how many licenses are used up for an application. I am typically getting a "Expression.Error: A cyclic reference was encountered during evaluation" error. My goal is to count the logins per user and per app. Users can be logged in over a 24 hour period.

Key note: The users have 4 apps they can log into and they can log into the same app with multple session. The logs will show the new login session, but the syntax is identical outside of the timestamp. My goal is to basically count the app logins greater than the last logout timestamp (per user, per app).

User 1	login	App 1	8/14/2023 8:05
User 1	login	App 1	8/14/2023 8:00
User 2	logout	App 4	8/14/2023 7:00
User 3	login	App 3	8/13/2023 4:01
User 1	logout	App 1	8/13/2023 17:00
User 3	login	App 2	8/13/2023 4:00
User 2	logout	App 1	8/12/2023 6:00
User 2	login	App 4	8/11/2023 4:00

Any ideas on the best way to accomplish this? Best Regards!

[v-cgao-msft]

Hi @joschae ,

Please first create a new calculated column:

Flag = 
VAR __user = 'Table'[User]
VAR __app = 'Table'[APP]
VAR __result =
    IF (
        'Table'[State] = "login"
            && CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[User] = __user
                        && 'Table'[APP] = __app
                        && 'Table'[DateTime] < EARLIER ( 'Table'[DateTime] )
                        && 'Table'[State] = "logout"
                )
            ) > 0,
        1
    )
RETURN
    __result

Then please new a measre:

Total = COUNTROWS(SUMMARIZE(FILTER('Table','Table'[Flag]>0),'Table'[User],'Table'[APP]))

Output is 1.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[joschae]

It's very close.  The only issue is that every login after a logout is flagged.  Even the ones that are weeks old.  I only need the most recent logins.  Meaning the logins after the last logout.

[v-cgao-msft · Accepted solution]

Hi @joschae ,

Please try this:

Flag 2 = 
VAR __user = 'Table'[User]
VAR __app = 'Table'[APP]
VAR __last_datetime = CALCULATE(MAX('Table'[DateTime]),FILTER(ALL('Table'),'Table'[User]=EARLIER('Table'[User]) && 'Table'[APP]=EARLIER('Table'[APP])&&'Table'[State]="login"))
VAR __result =
    IF (
        'Table'[State] = "login" && 'Table'[DateTime]=__last_datetime
            && CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[User] = __user
                        && 'Table'[APP] = __app
                        && 'Table'[DateTime] < EARLIER ( 'Table'[DateTime] )
                        && 'Table'[State] = "logout"
                )
            ) > 0,
        1
    )
RETURN
    __result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[joschae]

I think you pretty much nailed it and I understand the concept now.    I had to minor tweak so that all logins where flagged that were post the last logout. However I will accept the solution.   

VAR __last_datetime = CALCULATE(MAX('Table'[DateTime]),FILTER(ALL('Table'),'Table'[User]=EARLIER('Table'[User]) && 'Table'[APP]=EARLIER('Table'[APP])&&'Table'[State]="login"))

to 

VAR __last_logout_datetime = CALCULATE(MAX('Table'[DateTime]),FILTER(ALL('Table'),'Table'[User]=EARLIER('Table'[User]) && 'Table'[APP]=EARLIER('Table'[APP])&&'Table'[State]="logout"))

'Table'[DateTime]=__last_datetime

to

'Table'[DateTime]>__last_logout_datetime

The reason the original flag did not work is becuase my complete data is more like this. See User 1.

User 1	login	App 1	8/14/2023 8:05
User 1	login	App 1	8/14/2023 8:00
User 2	logout	App 4	8/14/2023 7:00
User 3	login	App 3	8/13/2023 4:01
User 1	logout	App 1	8/13/2023 17:00
User 3	login	App 2	8/13/2023 4:00
User 2	logout	App 1	8/12/2023 6:00
User 2	login	App 4	8/11/2023 4:00
User 2	logout	App 1	8/12/2023 6:00
User 2	login	App 4	8/11/2023 4:00
User 1	login	App 1	8/10/2023 8:00
User 1	logout	App 1	8/9/2023 15:00

Thank you!  I learned a lot from your expertise