Forum Discussion
Reoccurring Users
If you have a solution with CALCULATE
File | Options and settings | Options | DirectQuery -> Allow unresticted measures
Hi dameeks
I would be interested in your solution which only works in DirectQueryMode.
I probably have another solution (based on context transition) which should always work.
Let us know :)
Tristan
I just turned that option on. However the "solutions" I found were not exactally what I wanted. Still trying to wrap my head around how this DAX stuff works. Any pointers on how to generate a formula to calculate these user counts would be a big help.
Well ok, here is my suggestion.
I am assuming you have 3 tables: Calendar, Fact and Users.
Calendar and Users are both linked to your Fact table through a '1 to Many' relationship, respectively with [DateKey] and [UserID].
Below the steps :
1. Compute the first date usage (or purchase) ever in the Users table through a calculated column:
DateOfFirstUsageEver = Calculate ( min (Fact[DateKey]) )
This calc. column basically uses context transition (so you need to wrap the min into a calculate!!)
2. Create a relationship between Calendar[Date] and Users[DateOfFirstUsageEver]. This relationship is inactive by default (because of ambiguity, e.g there would be several paths to reach Users Table from Calendar Table) but you can leverage it to compute your number of new Users.
NumOfUsers = Countrows(Users)
NumOfNewUsers = Calculate ( [NumOfUsers] , USERELATIONSHIP(Users[DateOfFirstUsageEver], Calendar[Date]))
USERELATIONSHIP locally activates the relationship (e.g only for this particular measure) between Users and Calendar we have just created and allows you to compute the number of new users in the current filter context (e.g takes into account the dates you select)
3. You need a measure to compute the number of distinct users in the current filter context.
TotalUsers = Distinctcount(Fact[UserID]) works fine.
Your users are either returning or new. As a consequence:
TotalUsers= NumOfNewUsers + NumOfReturningUsers (1)
From (1), we have: NumOfReturningUsers = TotalUsers- NumOfNewUsers
You now have both New and Returning Users measures. Play with it !
Note: I generally never use calculated columns. As the DAX gurus suggest, calculated columns should only be considered when slicing of filtering data (mainly because of the Vertipaq columnar storage... adding columns to your model requires more space!). But in this particular case, I find it very useful.
Hope it helps :)
Feel free to ask if you have any questions !
Tristan
Data & BI consultant at AZEO
That is not exactly correct in my situation. I do not have a distinct table for users. This is more of a web marketing view. All I have are users identified by some unique value in a cookie.
Assuming this is what my main fact table looks like for a given month:
User A
User B
User C
User C
User B
Using your solution, all the users would have a min date of the current month. so my distinct users and new user count would be 3 resulting is 0 reoccurring.
What I would expect is 3 new users and 2 reoccurring.
What I need is a distinct count of users that are greater than 1. That would be my number of reoccurring.
