One slicer for two datasets

[PHanges]

Hi all,

I'm having what I think is a simple problem and can't seem to find a solution.  I have two Excel datasets which contain marketing lead data and built them out into a matrix in PowerBI.  Each table has an "Asset/Activity" field, and I want to create a slicer which will filter both sets of data.  When I try to build a relationship between the two tables on the aformentioned field, I get an error: "You can't create a relationship between these two columns because one of the columns must have unique values"

Any help is appreciated! 

[kcantor · Accepted solution]

@PHanges

You will need to create a lookup table with distinct values to join the fact tables. Then use the lookup table to populate your slicers. It is not difficult and can be completed using the built in query editor. Check this link for more information.

http://www.powerpivotpro.com/2014/03/creating-dynamic-lookup-tables-with-unique-values-using-power-q...

[jbaynham]

You might also be able to create the lookup table using a calculated table and the SUMMARIZE function.