Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
achandlerworth
Frequent Visitor

DAX: Count repeat users

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?

 

1 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

View solution in original post

2 REPLIES 2
JarroVGIT
Resident Rockstar
Resident Rockstar

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):

image.png

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

 

 





Did I answer your question? Mark my post as a solution!

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

 
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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.