Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I need help with a formula to calculate/bucket the number of users who've used a system more than X times in a time period.
I know how to do this in other systems but I'm still new to DAX.
Using this simple data
User Date Activity
A 8/1/2019 1
A 8/1/2019 1
A 8/2/2019 1
B 8/1/2019 1
C 8/3/2019 1
C 8/3/2019 1
C 8/4/2019 1
C 8/5/2019 1
I'd like a measure called
"2x - Number of users with 2 activity days"
Since A & C have activity on two separate days this should = 2
"3x - Number of users with 3 activity days"
Since only C has activity on three separate days this should = 1
I'm essentially looking for an aggregation of an aggregation. I need to count where the distinct count of the combination of User&Day = 2 and again where it = 3.
note, this calculation needs to be on the fly if possible so that it's dependant on the date range I select.
Any thoughts?
Solved! Go to Solution.
@JarroVGIT, Thank you for your assistance.
I had to tweak your formula to get the results I needed. But you've set me on the path to better understanding some of the capabilities.
The formula should read
Hi @achandlerworth ,
That is an interesting question! I just recently read up on SUMMARIZECOLUMNS() and that would definitly help here. First what you need to do is create a Date table and create a relationship with the Date column in your dataset. Then use the Datetable[Date] as slicer. DateTable can be created like this:
On the Dataview, click 'New Table' and insert the following DAX:
CalendarDateTable = CALENDARAUTO()
This looks for date columns in your model and gives the full spread of said date columns.
Then, your measures will be something like this:
2x - Number of users with 2 activity days =
COUNTROWS(FILTER(SUMMARIZECOLUMNS( 'Table'[User], 'Table'[Date], "@Total", SUM( 'Table'[Activity] ) ), [@Total] = 2))
This results in this (given your example data):
It's 1 because only C has 2 activities on that date range.
Well that was a fun question to answer, if you liked it then please mark this as solution and don't forget those thumbs up! 🙂
EDIT: Had to change DAX and screenshot, I now see that you wanted visits per day 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
@JarroVGIT, Thank you for your assistance.
I had to tweak your formula to get the results I needed. But you've set me on the path to better understanding some of the capabilities.
The formula should read
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |