Count session per user per day with 2 minutes interval

[Anonymous]

Hi everyone

I was asked to get a number of user stays active in an app per day and length in each session.

The system is designed in a way that once user logins, a session automatically refresh every 2 minutes until session expires. There is Log Out but rarely happens. In that case, there is a need to derive data with 2 minute accuracy from the time user login.

Here is the current table available

Login DateTime	User
7/May/2020 10.15am	1
7/May/2020 10.17am	1
7/May/2020 10.18am	2
7/May/2020 10.19am	1
7/May/2020 10.20am	2
7/May/2020 5.12pm	1
7/May/2020 5.14pm	1
7/May/2020 7.20pm	1
7/May/2020 7.22pm	1
7/May/2020 7.23pm	3
7/May/2020 7.24pm	1

And desire outcome is to have 2 additional columns/measures named "Interval" and "Session per Day".

Date	User	Interval	Session per Day
7-May-20	1	4-6 mins	2
7-May-20	1	2-4 mins	1
7-May-20	2	4-6 mins	1
7-May-20	3	0-2 mins	1

Appreciate for any help. Thank you so much

[AllisonKennedy]

I'm halfway there, but haven't got the duration part yet, need to think about @Greg_Deckler 's suggestion of using MAXX to get the duration...

Here's what I have so far: 

Create a series of Calculated COLUMNS in the table:

LogInRecheck = LogTbl[Login DateTime]+TIME(0,2,0)

LoginStatus = IF(COUNTROWS(FILTER(LogTbl,EARLIER(LogTbl[User])=LogTbl[User] && EARLIER(LogTbl[Login DateTime])=LogTbl[LogInRecheck]))=1, "","Login")

This already gives you total number of sessions per user - use the count of LoginStatus column as a value in the report.

So we're just missing the duration/interval. 

As a side note, to get the number for each login you could also create a column (not the most efficient solution and right now won't take into account different dates, but for some reason I decided to rank the users first so I already had that information handy): 

UserRank = RANKX(FILTER(LogTbl,LogTbl[User]=EARLIER(LogTbl[User])), LogTbl[Login DateTime],,ASC)

LoginNumber = RANKX(LogTbl,IF(COUNTROWS(FILTER(LogTbl,EARLIER(LogTbl[User])=LogTbl[User] && EARLIER(LogTbl[Login DateTime])=LogTbl[LogInRecheck]))=1,0,LogTbl[UserRank]),,ASC,Dense)-1

[sanimesa]

@AllisonKennedyMaybe we can also detect the logoff step? Does this work? I just reversed the loginrecheck condition in the second comparison:

LogoffStatus2 = IF(COUNTROWS(FILTER(UserLoginTable, EARLIER(UserLoginTable[User])=UserLoginTable[User] && EARLIER(UserLoginTable[LogInRecheck])=UserLoginTable[Login DateTime]))=1, "","Logoff")

[AllisonKennedy]

@sanimesa  sounds like you're getting it to work. 

Here's one idea for the interval: 

SessionID = COUNTROWS(FILTER(LogTbl,LogTbl[User]=EARLIER(LogTbl[User]) && LogTbl[LoginStatus]="Login" && LogTbl[UserRank]<=EARLIER(LogTbl[UserRank])))

Interval = SWITCH(COUNTROWS(FILTER(LogTbl,LogTbl[SessionID]=EARLIER(LogTbl[SessionID]) && LogTbl[User]=EARLIER(LogTbl[User]))), 1, "0-2 min", 2, "2-4 min", 3, "4-6 min", 4, "6-8 min", 5, "8-10 min", ">10 min")

both as calculated columns again

I'm sure @Greg_Deckler can provide a more elegant solution, but that's what my brain has come up with in the late hours of the NZ evening. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

@AllisonKennedy Your solution is brilliant!. Thank you.

Almost there.. it works if the duration is in exact 2 minutes, however, the interval should be within 2 mins. This is how it looks like in my current data. Those highlighted in yellow is marked as "Login" although it should be blank.

I tried to modify your code to this, but still not giving me the correct result. Any idea what should it be?

LoginStatus =

IF(

COUNTROWS(

FILTER(

Query1,

EARLIER(Query1[IDCHANNELUSER]) = Query1[IDCHANNELUSER] &&

EARLIER(Query1[EXECUTION_TIME]) >= Query1[LogInRecheck] &&

EARLIER(Query1[EXECUTION_TIME]) < Query1[LogInRecheck]

)

) = 1, "","Login")

[AllisonKennedy · Accepted solution]

@Anonymous You're close, but I would expect your DAX to return almost all LOGIN??

Earlier as used in this expression is enabling us to compare Execution time to the Login Recheck for every single row in the table within the FILTER expression. You need to figure out if that Execution time is within 2 minutes of any other Execution time for the same user, so we'll count all the rows where that condition is true. The below formula works with my sample data, but you'll want to examine it and your data closely and make sure you understand what it's doing, as if your intervals become too close together it could start to have some inconsistencies again.

LoginStatus =
IF (
COUNTROWS (
FILTER (
LogTbl,
EARLIER ( LogTbl[User] ) = LogTbl[User]
&& DATEDIFF ( EARLIER ( LogTbl[Login DateTime] ), LogTbl[LogInRecheck], MINUTE ) >= 0
&& DATEDIFF ( EARLIER ( LogTbl[Login DateTime] ), LogTbl[LogInRecheck], MINUTE ) <= 2
)
) = 1,
"Login"
)

[AllisonKennedy]

Yes, that will work for logoff.

[Greg_Deckler]

@AllisonKennedy  you should be able to get the duration/interval using this technique: See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Sorry, should have posted that...earlier (bad joke).

[Greg_Deckler]

Well, haven't looked at this close enough to give you a definitive answer, but it is going to involve EARLIER. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

I think I would start by getting the minutes between a row and the previous row (using MAXX and EARLIER). Then you can go from there to aggregate rows since if if that duration is longer than 2 minutes it is a new session.

That's what I'm thinking.