Calculating Average Time Logged In (do not add concurrent login time for a user)

[NB689]

Hello,

I have a Dax problem that is building off of a previous problem that I was able to solve. 

I am looking to provide a metric that show the average hours:minutes logged in per user, per day.

One of the requirements is since users can have multiple logins per day, and some of these may over lap (since they can be logged in on multiple devices) I do not want to double count these concurrent login time per user.

I also want to divide by the total number of unique calendar days that all users are logged in by.

Here is what my test data set looks like:

For User A, I would Expect to see the following:

User ID	LogIn	LogOut	AVG Login Time
A	7/10/2022 8:00:00 AM	7/11/2022 4:00:00 PM	16hr:00M
A	7/15/2022 8:00:00 AM	7/15/2022 4:00:00 PM	8hr:00M
A	8/3/2022 10:00:00 AM	8/3/2022 8:00:00 PM	10hr:00M
A	8/3/2022 11:00:00 AM	8/3/2022 12:40:00 PM	1hr:40M
A	8/5/2022 8:00:00 AM	8/5/2022 9:00:00 PM	1hr:00M
Total			5hr:50M

My formula would look like this = 

            (Total Hr:M logged in - any concurrent time) divided by "Unique # of Users" divided by "# of Calendar Days"

Total Hours: 36hr:40M

Subtract the concurrent logged in time: -1 hr:40M (I do not want to count this since the user was logged in on another instance at this time.)

Divided by the number of unique User IDs: 1

Divided by the number of calendar days: 5 (The 10th, 11th, 15th, 3rd, and 5th)

So: 

     (36hr:40M - 1hr:40M) / 1 / 5 = 7hr:00M

For User B I would expect to see this:

User ID	LogIn	LogOut	AVG Login Time
B	7/15/2022 8:00:00 AM	7/15/2022 10:00:00 AM	2hr:00M
B	8/2/2022 1:00:00 PM	8/2/2022 7:00:00 PM	6hr:00M
B	8/2/2022 8:00:00 AM	8/2/2022 12:30:00 PM	4hr:30M
B	8/2/2022 9:00:00 AM	8/2/2022 10:00:00 AM	1hr:00M
B	8/2/2022 3:00:00 PM	8/2/2022 4:00:00 PM	1hr:00M
Total			6hr:15M

The math for user B looks like this: Total minutes is 14hr:30m, but on the 2nd, the only time logged in was from 8am to 12:30 pm, and 1:00pm to 7pm. So the total time logged in on the 2nd was 10 hours and 30 minutes.    

10hr:30M (on the 2nd) + 2hr:00M (on the 15th) = 12hr:30M.

12hr:30M divided by two days (the 2nd and the 15th = 6hr:15M

Divided by unique number of users, which is 1, so this doesn't change the total.

So, for everyone's time, I would expect to see 

Total Time: 51hr:10M

Total Time that is non-concurrent: 48hr:00M (35 from user A, 

[daXtreme]

@NB689 

There is a better way to do such calculations but you have to change the model. You'll need to create a table that will, for each user, store each and every second (if that's the resolution you want) when they were logged in (you can either store all the unique moments for each user or even non-unique moments to be able to determine to which login instance the moment belongs). This avoids the issue of overlapping time frames nicely and calculations will be blazingly fast (not to mention the simplicity of DAX).

The table would be something like this:

UserID|

Day|

Hour|

Minute|

Second

Can you see how easy it'll be to caclulate what you want?

The key is to have a good data model, not insane DAX skills 🙂

[NB689]

Love it. This is going to be a huge set of data, so setting up my data model would be the way to go. I am familiar with making a list of dates. I have some old M code that I could do this in power query, to either use the minimum login to max login, or a static list. I'm not sure however how to create the table the way that you have it listed out. Would I just create a table that has a list of dates, and join this table somehow with my current data, or do I need to create this new table using power query and or dax? 

[daXtreme]

@NB689 

Power Query has everything (and more) to let you create any table of any sophistication. Don't worry about the volume of data because most of the columns (if not all) will have a very low cardinality, hence the compression will be great. You should join your User dimension via UserID to the other one, so that filtering a user filters their entries in the helper table (which will probably be hidden as a fact table). Of course, you can add other columns to the fact and create other dimensions that will link to it to enable slicing by their attributes (please note that slicing in the UI should never be done via the columns of the fact table, only via dimensions).

It's all up to you what kind of info you want to expose or calculate.

To create the fact table you'll probably need some M code but I have a feeling the setup could be achieved via the point-and-click method as well. Just try.

[NB689 · Accepted solution]

I have been researching how to do this today, but have not been able to find anything that shows me how to create the fact table. I know that Power Query makes it possible, I am just running into a dead end with my knowledge level to be able to come up with a solution. If I need to I could close this question out, and re-ask it gearing it more towards the solution that you suggested. We do have several other aggregations that we want to do that would be easier to create with a more optimized data model.