Forum Discussion
Reoccurring Users
I just stared using Power BI and I am developing a dashboard that will show user statistics over a given period of time. One of the statistics is the number of reoccurring users vs new users. I have a fact table that contains an instance of each time a user made a request to our system. The important columns in the fact table are DATEKEY and USERID.
We are defining a reoccurring user as a user that has been in the system at least once, regardless of the seleced time window.
So basically what I need to do is this: For the selected date range, grab all the distinct users. Then for each user, count the occurrences of the user for all time. For any user with a count greater than one, flag as reoccurring, otherwise flag as new. Then count the number of reoccurring and new.
I would like to show the counts of reoccurring vs new in a pie chart.
One other important note is I am using DirectQuery to view my dashboard. A lot of solutions I have tried and and seen required the use of CALCULATE and other functions that are not supported in DirectQuery mode.
Any advice would be greatly appreicated.
5 Replies
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
