Forum Discussion
Calculating Average Time Logged In (do not add concurrent login time for a user)
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.
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 🙂
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?
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.
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.