+ Reply to Thread
Results 1 to 16 of 16

PowerPivot Distinct New User by Date

  1. #1
    Registered User
    Join Date
    11-26-2013
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    6

    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. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: PowerPivot Distinct New User by Date

    Try this measure:
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    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.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: PowerPivot Distinct New User by Date

    Quote Originally Posted by xlnitwit View Post
    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. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: PowerPivot Distinct New User by Date

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

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

    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.
    =IF(RANKX(FILTER('Table',[UserName]=EARLIER([UserName])),[Date],,ASC)>1,0,1)

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

  7. #7
    Registered User
    Join Date
    11-26-2013
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    6

    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;

    DistinctNew:=CALCULATE(DISTINCTCOUNT('User Visit'[User Name]),FILTER(ALL('Login Date'[Login Date]),'Login Date'[Login Date] <= MAX('Login Date'[Login Date]))) -CALCULATE(DISTINCTCOUNT('User Visit'[User Name]),FILTER(ALL('Login Date'[Login Date]),'Login Date'[Login Date] < MAX('Login Date'[Login Date])))

    I feel I am making a mistake with this FILTER(ALL('Login Date'[Login Date]),'Login Date'[Login Date] <= MAX('Login Date'[Login Date])) ?

    Best Regards,
    Vikram

  8. #8
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    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. #9
    Registered User
    Join Date
    11-26-2013
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    6

    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
    Column 1: username
    Column 2: LoginDate

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

  10. #10
    Registered User
    Join Date
    11-26-2013
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    6

    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?
    Last edited by vsridha; 04-24-2017 at 01:43 PM.

  11. #11
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

    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.
    Last edited by CK76; 04-24-2017 at 02:54 PM. Reason: See Edit:

  12. #12
    Registered User
    Join Date
    11-26-2013
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    6

    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. #13
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    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.
    Last edited by Olly; 04-25-2017 at 04:59 AM.

  14. #14
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

    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. #15
    Registered User
    Join Date
    11-26-2013
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: PowerPivot Distinct New User by Date

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

  16. #16
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: PowerPivot Distinct New User by Date

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Max date for customer powerpivot
    By stephme55 in forum Excel General
    Replies: 0
    Last Post: 08-22-2016, 04:50 PM
  2. distinct vs. distinctcount in powerpivot
    By stephme55 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-27-2016, 01:45 PM
  3. Replies: 4
    Last Post: 07-23-2015, 03:35 PM
  4. [SOLVED] Trying to get the DISTINCT value for a specific Date and Code
    By antexity in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-23-2015, 04:21 PM
  5. Replies: 2
    Last Post: 06-02-2014, 09:34 AM
  6. Replies: 0
    Last Post: 11-20-2012, 05:34 AM
  7. Pivot Tables-To Find Count of Unique(Distinct)User id within the same Journal Entry.
    By Deepthik in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-10-2010, 06:09 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1