Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Dedupe ID - Keep most recent record

I have a list of Visitor IDs to a website for a specific timespan.  For purposes of summarizing the data, I would like to dedeupe the IDs in the list, keep only the timestamp of the most recent visit...
Need Help
Show and Tell
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    Anonymous

     

    (DAX(SUMMARIZECOLUMNS(analytics_clickstream_201802[visid_comb);"LastDate":MAX(analytics_clickstream_201802[date_time]))

    )).

     

    Replace : with ; 

     

    If you have problems with ; replace it with , (This is for regional settings)

     

    Regards

    Victor

     

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Ashish_Mathur

 

The link below containas a sample data .xslx (Two tabs, Raw Data and Desired State)

 

Simply put, I have one column of Visitor IDs, and the other a timestamp.  If for example I look over the course of a year, I may see repeated Visitor IDs (i.e. the individual has visited the site multiple times over the last year) however the adjacent column is timestamped.

 

I would like to dedupe the Visitor ID column (Column A) so the data is distilled down to a single instance, showing the latest (newest) timestamp.

 

https://www.dropbox.com/s/6wdubhk9kj8kalq/Dedupe%20Sample%20Data.xlsx?dl=0

 

Thanks for your help!

 

 

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
8 years ago

Anonymous

 

Hi, you can create a New Table (Modeling - New Table)

 

 

NewTable =
SUMMARIZECOLUMNS (
    'Sample Data'[Visitor ID];
    "LASTDATE"; MAX ( 'Sample Data'[Date] )
)

Regards

VIctor

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Vvelarde

     

    It occurred to me as you were posting your response that the Visitor ID is based on a concatentation based on a Dax Function.

     

    Will this work?

    • Vvelarde's avatar
      Vvelarde
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Anonymous

       

      Yes, this should be Work.

       

      It takes the Visitor ID (after the concatenation was made it). The visitor ID is a calculated column right?

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Vvelarde

         

        This is the statement I am using for the ID creation:

        visid_comb = CONCATENATE(analytics_clickstream_201802[post_visid_low],analytics_clickstream_201802[post_visid_high])

         

        This is your recommendation, modified for the table:

        Column = SUMMARIZECOLUMNS(analytics_clickstream_201802[visid_comb);"LastDate":MAX(analytics_clickstream_201802[date_time]))

         

        This is the error message when I apply your suggestion, and I am not sure where my error is:

        The syntax for ')' is incorrect. (DAX(SUMMARIZECOLUMNS(analytics_clickstream_201802[visid_comb);"LastDate":MAX(analytics_clickstream_201802[date_time]))

        )).

         

        Appreciate all of the help.