Dedupe ID - Keep most recent record

[Anonymous]

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.

The Visitor ID is a column created from a concatenation DAX statement.

Is there a preferred way of doing this?

[Ashish_Mathur]

Hi,

Share your data and also show the expected result.

[Anonymous]

@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]

@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]

@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]

@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]

@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.

[Vvelarde · Accepted solution]

@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]

Neither change succeeded.

Assuming I followed your instruction properly.

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

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

[Vvelarde]

@Anonymous

Ok.

Replace ALL ; with , Dont Mixed.

And Use a Modeling - NewTable Not a New Column.

Regards

Victor

[Anonymous]

@Vvelarde

So I think what you're suggesting: 

1.  Modeling>New Table

2.  Add Dax Statement: 

Table =  SUMMARIZECOLUMNS(analytics_clickstream_201802[visid_comb]),"LastDate",MAX(analytics_clickstream_201802[date_time]))

3.  Then what?

Thanks.

[Vvelarde]

@Anonymous

This will create you a new Table with the data.

Regards

Victor

[Anonymous]

Thank you.