cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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
Frequent Visitor

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

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
2 REPLIES 2
Resident Rockstar

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!

Frequent Visitor

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

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors