Forum Discussion
Relationships between tables when both have duplicates
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.
Solution SageI 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
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.