Get slicers to work between two tables with no unique key

[arock-well]

I built a report with one primary table "A" in a dataset being used for visuals and slicers. However, a second table "B" needs to be added where I need to lookup data in on particular column.  All the slicers use the data from table "A".

There is not a common unique key between the two tables. The data is many-to-many. However, there are three fields between the two that could be used to create a relationship (and the three fields are slicers). 

How do I create a relationship so when a table "A" slicer changes, the table "B" visual updates, too? What is the best option?

1. Will creating relationships in the dataset (in manage relationships) between the three fields cause the slicers to work correctly?

2. Will a new column in the dataset doing a lookup filtered on the three fields work? If so, what type of formula would that look like?

3. Similar to 2 above, will a new measure in the report itself doing a lookup filtered on the three fields work instead? If so, what type of formula would that look like?

4. Something else I'm completely missing?

Thank you.

[Idrissshatila · Accepted solution]

Hello @arock-well ,

You should start by building dimension tables that has unique values of the fields you want to filter with and link these dimension tables to both tables.

check this video for reference https://www.youtube.com/watch?v=7ciFtfi-kQs

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[arock-well]

Hi @Idrissshatila 

Thanks for the quick response. I will definitely look into that concept. However, is there a way to use the existing table structure and use lookup calculations to get the same results?