DAX: Count repeat users

[Anonymous]

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?

[JarroVGIT]

Hi @Anonymous ,

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! 🙂

[Anonymous · Accepted 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

2x - Number of users with 2 active days =

COUNTROWS(FILTER( SUMMARIZECOLUMNS( Sheet1[User],"@Total",DISTINCTCOUNT(Sheet1[Date])) ,[@Total] = 2) )

It needed to be distinctcount since I don't care how many times a user uses something on a given day, I just care if they used it.

So in pseudo-code I want the

count of users where distinctcount(User&Day) = 2