Forum Discussion
Helper IVSlice two tables by multiple common columns
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.
6 Replies
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.
DJBAJGTell 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?
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.
- Ashish_Mathur
Super User
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.
