Relationships between tables when both have duplicates

[brent_excel]

Hi,

I am trying to create a relationship between two tables that both have duplicates.  In my case, I have two tables:  a Sales Data Table and a Price Data Table, both which contain a Product column with duplicate values.  To fix this, I link them each to another Products Table that contains only the unique prouduct values.  The relationship is created, but I still get the message "relationships between tables may be needed" and the dollar sales is the same across all products.

My Sample File

https://we.tl/t-xxEDDE2CS9

Interesting Links

https://www.youtube.com/watch?v=vAvQ8pCnWDk

[kentyler]

I created a table with a measure that gets a list of products from each data source then gets only the distinct values

This table can set up relationships to both of your tables.

And they they can be combined in a visual

[brent_excel · Accepted solution]

Hi Kentyler,

Thank you for the response.  No doubt your solution works with Power BI, but I am using PowerPivot with Excel.  After playing around with it for a little bit, I was able to get this to work a couple of different ways.

1) From the Sales data and Price data tables, I was able to create a unique Product table and a unique Time table.  Then I linked both the Sales table and Price table to the unique Product and Time tables separately (pictured below).  Then in my pivot table, I created a measure that calculates the dollar sales.

=CALCULATE([Sum of Dollar Sales], 'Price Data')

2) Alternatively, I was able to achieve the same result by inserting a column within the PowerPivot data model and combining the Product and Time columns on each of the Product and Time tabs.  Then I linked the Sales and Product tables via the Product_Time columns.  In my pivot table, I simply added the dollar sales field to the pivot table.  I prefer this solution because I no longer get the "relationships may be needed between tables" message, and the calculated measures are much easier to create.