Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Count session per user per day with 2 minutes interval

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 DateTimeUser
7/May/2020 10.15am1
7/May/2020 10.17am1
7/May/2020 10.18am2
7/May/2020 10.19am1
7/May/2020 10.20am2
7/May/2020 5.12pm1
7/May/2020 5.14pm1
7/May/2020 7.20pm1
7/May/2020 7.22pm1
7/May/2020 7.23pm3
7/May/2020 7.24pm1

 

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

DateUserIntervalSession per Day
7-May-2014-6 mins2
7-May-2012-4 mins1
7-May-2024-6 mins1
7-May-2030-2 mins1

 

Appreciate for any help. Thank you so much

1 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"
)

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

8 REPLIES 8
AllisonKennedy
Super User
Super User

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
 

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@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")

 

@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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

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

1.png

 

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")

 

@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"
)

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Yes, that will work for logoff.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.