cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
prakash11440278 Advocate II
Advocate II

How to make relationship between two tables both having duplicate records

Hi All,

Can some body please explain how to make relationship between two tables, that two tables all contain duplicate records. Which it is not possible to relate due to duplicate records presents in both tables. Please explain how to relate the both tables in relationship tab.
1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: How to make relationship between two tables both having duplicate records

Hi @prakash11440278,

 

In order to create this visualisation now you should use the "customer" field from the new table you created.

 

 

Best,

Kris

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Re: How to make relationship between two tables both having duplicate records

Hi @prakash11440278,

 

In order to create your relationship you should first create a table that contains all the unique values from the columns that you wish to create the relationship on. I've assumed both tables have a column called Customer. Create a new Table using the measure

 

Customers = DISTINCT(UNION
     (SELECTCOLUMNS(Table1,"Customer",[Customer]),
     SELECTCOLUMNS(Table2,"Customer",[Customer])))

 

This measure will create a union of the 2 customer columns and then remove all duplicates leaving you with all your distinct customers. You can then create a relationship in the relationship window as follows

 

Capture33.PNG

 

Alternatively (this would be my prefered method), if you wished to do this in the query editor, you could append the 2 tables that you require to relate (as a new query) and keep only the column you wish to create a relationship on.  A quick removal of duplicates and you have your distinct customer table. You can then create the relationship as above.

 

Hope this helps.

 

Regards,

Kris

prakash11440278 Advocate II
Advocate II

Re: How to make relationship between two tables both having duplicate records

Hi kriscoupe,

 

Thanks for your reply.

 

I created new a table, as suggested below. Still the issue remains same. Please find the attached screen shot.

 

image.pngimage.pngimage.png

Anonymous
Not applicable

Re: How to make relationship between two tables both having duplicate records

Hi @prakash11440278,

 

In order to create this visualisation now you should use the "customer" field from the new table you created.

 

 

Best,

Kris

View solution in original post

prakash11440278 Advocate II
Advocate II

Re: How to make relationship between two tables both having duplicate records

Hi kriscoupe,

 

This worked. Thanks....:)

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors