Slice two tables by multiple common columns

[DJBAJG]

I have two tables; Table A and Table B.

The tables have 3 common columns and many other non-common columns.

The tables cannot be combined.

What I need is the ability to create 3 slicers for the 3 common columns and have the slicers filter both tables.

I can easily create a bridge table with unique values for say, common column #1 and create the appropriate relationships between it and the tables but when I try to do this same thing for the remaining 2 columns I get an error regarding introducing ambiguity and PBI won’t let me make the additional relationship active. I feel I’m going about this in completely the wrong way and that there is a simple piece of DAX that’s used for these instances as I assume they’re very common but being new to the language I’m at a loss for what that code is. Please help.

Thanks.

[Ashish_Mathur]

Hi,

The process of creating one bridge table for each common column is the correct approach.  I can help further if you share the link from where i can download your PBI file.

[Anonymous]

No need to create 3 different bridge tables. Just create one bridge table with 3 columns plus a key to the different combinations held in the bridge and the same corresponding keys in the 2 tables. All you need is use a bit of Power Query to reshape your data - get rid of the 3 columns in A and B, and instead add a column with a key to the bridge table's correct combination. Then connect the bridge to A and B on the new columns with keys and you're done.

[DJBAJG]

Tell me more about creating a key. Would I concatenate the 3 common values into a column on the fact tables and on the bridge table and us that column to create the relationship? 

[Anonymous]

@DJBAJG 

Why would you want to create a key that would be text? Keys are and should always be integers due to speed reasons (rarely GUIDS). If you only have unique combinations of 3 fields in a table (and they should be unique since it's going to be sort of a junk dimension), each such combination can be assigned an integer key. Then you should use this key in other tables. Creating such a table and assigning integer key, then placing them in the related tables is pretty easy in Power Query.

[DJBAJG]

Based on my previous response you can see that I'm new to the concept in general. Are you able to provide an small example of the query I would need to execute this so I can better understand the idea behind it and learn by expanding it to meet me needs? My DAX is weak and my M is non-existant. LOL 

[Anonymous]

Design Tip #113 Creating, Using, and Maintaining Junk Dimensions - Kimball Group