View My Drafts
Theres a few ways to do this. Often we use a bitmap for this sort of stuff (1,2,4,8,16), but 10 x will do the same thing. (1,10, 100)
At the end you need two tables (or 3 if date is a dimension and not degenerate)
Usage (summary of Mobile)
Channels (reference Matrix table)
You can PowerQuery to "group" your source table using the group by function (as opposed to calculated tables which are way less efficient)
- First to Group by User, Date, Channel ( in case a user logged in twice)
- Secondly to group by User, Date (with a sum on channel to create the ChannelKey of 111, etc)
Bob Duffy Database Architect Prodata | MCM (SQL Server) | MCA (SQL Server) | MVP (SQL Server) | SSAS Maestro | Email: email@example.com | Blog: http://blogs.prodata.ie | Mob: +353 (0) 87 051 3735 | Office: +353 (1) 2933883 | Live Meeting - Meet Now
That's the downside of pre calculating the channel mix – you lose flexibility on date grain.
What date granularities do you need to calculate ?
If its only 2-3 variations (say daily, weekly, monthy), then you could create three aggregate tables in Power query and use an if/switch statement to pick the appropriate table
Eg Create a Date Dimension and
Users:=If (isfiltered(Date[Month]) =1, 'MobileAggMonth'[MonthuserCount], 'MobileAggDate'[DateUsersCount] )
If you need "truly" flexible date selection then I think you'll need to calculate the channel mix in DAX at query time (not as precalculated tables in PowerQuery or calculated table), which will perform like a dog (eg using a double SUMMARIZE). Let us know and I'll grab some time at the weekend and send on example.
What sort of volume do you have of data ? DAX style solutions as opposed to modelling solutions don't work so well at super high volume due to the run time grouping/scanning and non-additive nature, but at lower volume its doable.