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