cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

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 5
Highlighted
Community Champion
Community Champion

Re: Reoccurring Users

If you have a solution with CALCULATE

 

File | Options and settings | Options | DirectQuery -> Allow unresticted measures

 

Direct Query.png

Highlighted

Re: Reoccurring Users

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

 

Highlighted
New Member

Re: Reoccurring Users

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. 

Highlighted

Re: Reoccurring Users

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

 

Highlighted
New Member

Re: Reoccurring Users

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. 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors