cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rjain12 Regular Visitor
Regular Visitor

Join tables based on multiple columns

Hi,

 

I have two tables . PowerBI does not let me join these tables as they do have unique values in one of the columns. In SQL I would join these tables based on two columns. 

 

Table 1

 

account_id    experiment_id

 

 

Table 2

 

account_id   experiment_id

 

 

In sql I would do the following

 

select * from t1,t2 where t1.account_id=t2.account_id and t1.experiment_id=t2.experiment_id

 

How do I create such a relationship in dax? 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Join tables based on multiple columns

Hey,

 

in the query editor

  1. duplicate the columns that you want to use for the join
  2. mark the duplicated columns you want to use for the join, choose "Merge columns" from the context menu.

Here is a screenshot:

2017-10-13_22-27-14.png

 

Repeat this for the 2nd table.

 

Now you are able to use this column to create a relationship between both tables

 

Don't forget to "hide" this column from report view.

 

Hopefully this answers your question

 

Regards

Tom

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
9 REPLIES 9
Super User
Super User

Re: Join tables based on multiple columns

Hey,

 

currently it's not possible to create a relationship in Power BI between tables that spans more than one column.

 

For this reason I concat both columns in the Query Editor in each table, and use this column to create the relationship.

 

Hopefully this gives you an idea

 

Regards

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
rjain12 Regular Visitor
Regular Visitor

Re: Join tables based on multiple columns

@TomMartens: Thanks for the reply. I did not understand it completely. Could you give me example and tell me how the output would look? Will the concatenated column have unique values?

 

Sorry I am new to this. 

Super User
Super User

Re: Join tables based on multiple columns

Hey,

 

in the query editor

  1. duplicate the columns that you want to use for the join
  2. mark the duplicated columns you want to use for the join, choose "Merge columns" from the context menu.

Here is a screenshot:

2017-10-13_22-27-14.png

 

Repeat this for the 2nd table.

 

Now you are able to use this column to create a relationship between both tables

 

Don't forget to "hide" this column from report view.

 

Hopefully this answers your question

 

Regards

Tom

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
rjain12 Regular Visitor
Regular Visitor

Re: Join tables based on multiple columns

@TomMartens: I am now able to create a relationship between the 2 tables but somehow the filtering is still not working. Any ideas?

gujames Frequent Visitor
Frequent Visitor

Re: Join tables based on multiple columns

I'm having a similar issue.

 

I'm trying to merge together the tables so that Ican use two layers in a ArcGIS Map in PowerBI.

 

Data is broken out per the below:

Table 1: Brand Index

Col1 - Brand

Col2 - Index

Col3 - Latitude

Col4 - Longitude

 

Table 2: Brand Locations

Col1 - Brand

Col2 - Latitude

Col3 - Longitude

 

I'd like to merge the datasets so that the result shows:

Col1- Brand

Col2- Type (Index vs Location)

Col3- Index

Col4- Latitude

Col5- Longitude

 

But when I try to merge the datasets, or append them, I end up with two columns for Latitude and 2 columsn for Longitude. Any advise here would be greatly appreciated.

Re: Join tables based on multiple columns

Unfortunately, this method doesn't work if your input tables are in DirectQuery mode.  When you try to merge, you'll get an error message and an option to transform the data input to Input mode.

MitieFred Regular Visitor
Regular Visitor

Re: Join tables based on multiple columns

So, new to Power BI but this is something I'm trying to achieve, using MSProject as input. How can I tell if the tables are "in DirectQuery mode" and how do you "duplicate" columns, is it simply a matter of adding a New Column and setting the formula to “DupCol = Col-I-want-to-join”

Highlighted
dchapman374 Occasional Visitor
Occasional Visitor

Re: Join tables based on multiple columns

What an excellent idea!

alfmos Occasional Visitor
Occasional Visitor

Re: Join tables based on multiple columns

What if in the two columns merged there are values 1 and 11, and the same in the columns merged in the second table? There will be an incorrect join for 1-11 and 11-1.