# PowerPivot Distinct New User by Date

1. ## PowerPivot Distinct New User by Date

Hi All, this is my first post.

I am very new to PowerPivot and I want to recreate a calculation that I have in my normal pivot table in power pivot.

Lets say I have 2 columns; Column 1: UserName, Column 2: Date. I want a PivotTable/Chart with Distinct New users by date (who have visited my website). In the normal pivot table, I had a helper column in my source table with this formula (=IF(COUNTIF(\$A\$2:\$A2, \$A2)>1,0,1). So this marks the first visit of each user as 1 and my pivot sums this to give new users who visit my site each day.

How can I recreate this in POWERPIVOT (Excel 2013) without using a helper column? Can I use Calculated Measure or Calculated Column for this?

Thanks in advance for the help!

2. ## Re: PowerPivot Distinct New User by Date

Try this measure:
``Please Login or Register  to view this content.``

3. ## Re: PowerPivot Distinct New User by Date

Hi,

If you're using the data model, there should be a default distinct count option to use.

4. ## Re: PowerPivot Distinct New User by Date

Originally Posted by xlnitwit
Hi,

If you're using the data model, there should be a default distinct count option to use.
But that won't give him the distinct NEW users, which is his requirement...

5. ## Re: PowerPivot Distinct New User by Date

Oh, my sincere apologies. I completely misread the formula in the original post!

6. ## Re: PowerPivot Distinct New User by Date

You can also use RANKX on date with following condition in Calculated Column. Similar to what you did in regular Excel formula.

Basically sets 1st occurrence of UserName as 1, else 0.

7. ## Re: PowerPivot Distinct New User by Date

Hi Olly,

Thank you so much for the response. You do understand my requirement correctly, but I am struggling to execute your formula correctly. Lets us say that my Date column is called 'Login Date' so I dont get confused between function Date and column name. This is what I have entered which returns an Error;

Best Regards,
Vikram

8. ## Re: PowerPivot Distinct New User by Date

You do have a relationship between your 'User Visit' and 'Login Date' tables, don't you?

Try attaching a sample workbook, so I can see your model.

Oh, and please use CODE tags to post your code - and I strongly recommend visiting www.daxformatter.com to layout your DAX formulae a bit better...

9. ## Re: PowerPivot Distinct New User by Date

Olly, thanks i will use the formatter.

Both columns are in the same table and there is only 1 table in the model at the moment as I am just getting started with PowerPivot.

Table Name: UserDate

Other columns not relevant at this point. Does this clarify the scenario? else I will upload an example tomorrow from work. Thank you.

10. ## Re: PowerPivot Distinct New User by Date

Hi CK76, thanks a lot for your response.

I tried your suggestion, an error msg told me to replace ASC with 1. Once I executed, my sum of calculated-column which was supposed to be around 5900 was about 8300. Any idea why this might be the case?

11. ## Re: PowerPivot Distinct New User by Date

Is there instance where you'd have same user appearing multiple times in same date?

That could cause duplication with the set up.

One way to get around it is to add very small number (such as Row# * 1 millisecond/or smaller to date value) to break ties.

Edit: Alternately you can filter table for distinct set of [UserName] & [Date] combo.

12. ## Re: PowerPivot Distinct New User by Date

CK76, thanks, that might be the reason. There might be instances of users logging in multiple times on the same day.

How do I adapt your formula to filter for Distinct UserName and DateCombo. I would prefer to avoid adding (Row# * 1 millisecond/or smaller to date value) if possible.

13. ## Re: PowerPivot Distinct New User by Date

You really don't want to use a calculated column. A measure will work perfectly.

Use:
``Please Login or Register  to view this content.``

14. ## Re: PowerPivot Distinct New User by Date

I'd suggest you go with Olly's suggestion. Measures are far more efficient than calculated columns in this instance especially when there's multiple instance of same username in same date.

Just note that unless you use this measure to populate another table etc. You won't be able to show Grand Total in your pivot table (since measures are calculated in context of each cell/row within pivot table).

Another method is to use PowerQuery to summarize data (group by) and report on the table.

15. ## Re: PowerPivot Distinct New User by Date

Olly, this solution works like a charm. Problem solved, Thank you so much!

16. ## Re: PowerPivot Distinct New User by Date

Originally Posted by vsridha
Olly, this solution works like a charm. Problem solved, Thank you so much!
Great. Happy to help, thanks for the feedback

There are currently 1 users browsing this thread. (0 members and 1 guests)