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

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.

 

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

 

Is there a preferred way of doing this?

1 ACCEPTED SOLUTION

Accepted Solutions
Vvelarde Super Contributor
Super Contributor

Re: Dedupe ID - Keep most recent record

@irnm8dn

 

(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

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




12 REPLIES 12
Super User
Super User

Re: Dedupe ID - Keep most recent record

Hi,

 

Share your data and also show the expected result.

irnm8dn Member
Member

Re: Dedupe ID - Keep most recent record

@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 Super Contributor
Super Contributor

Re: Dedupe ID - Keep most recent record

@irnm8dn

 

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

 

 

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

Regards

VIctor

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




irnm8dn Member
Member

Re: Dedupe ID - Keep most recent record

@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 Super Contributor
Super Contributor

Re: Dedupe ID - Keep most recent record

@irnm8dn

 

Yes, this should be Work.

 

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

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




irnm8dn Member
Member

Re: Dedupe ID - Keep most recent record

@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 Super Contributor
Super Contributor

Re: Dedupe ID - Keep most recent record

@irnm8dn

 

(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

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




irnm8dn Member
Member

Re: Dedupe ID - Keep most recent record

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 Super Contributor
Super Contributor

Re: Dedupe ID - Keep most recent record

@irnm8dn

 

Ok.

 

Replace ALL ; with , Dont Mixed.

 

And Use a Modeling - NewTable Not a New Column.

 

Regards

 

Victor

 

 




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 6 members 3,486 guests
Please welcome our newest community members: