Is it possible to use multiple field joins in a table relationship?

[brianpetersen]

Currently it appears that the Power BI Desktop only supports a single field in the join criteria between tables. For example, I may have a Customer table with fields for Company, Customer No, and Name. A sample would be:

Company       Customer No           Customer Name
Western         123                          Acme West
Eastern          123                          Acme East


In this case I have two customers with the same customer number but they are different customers because the record is made unique by Company + Customer No.

In the Power BI Desktop when I select the Customer No field for the join it gives me a message about there being duplicate values and I can't create the join. I can create a custom field that joins the two values together into a single field and then use this as the composite key to join this table to another table (such as a Sales Transactions table) but the whole process seems a bit unnecessary. Having tables that require a join on more than one field is a pretty fundamental design concept so I was a bit surprised that it didn't appear to be currently supported. Maybe I'm missing something but does anyone else have any experience with this outside of creating the custom field to join the other two fields together?

[andre · Accepted solution]

@brianpetersen you should create a new column, you can call it CustomeriD as CustomerID = [Customer Name] & [Customer No].  You have to do it in both tables you are trying to link and then create a relationship based on these new fields. Relationships based on composite keys are not supported.

[brianpetersen]

Hi everyone,

Thanks for the feedback! I was referring specifically to using the "Manage Relationships" functionality in the Power BI Desktop. I didn't think there was a way to set a composite key there without first joining mutliple fields together to make a unique field and you guys confirmed that. I appreciate the clarification!

[ashley95e]

You have to make a unique list from the column you would like to search

ColumnA  has names or data that are repeated several times - from ColumnA you have to derive a list that is unique- no duplicates, then you'll be able to create a relationship.

[reza_rad]

Where do you mean?

In power Query/ Query Editor?

or in Relationship tab of Power BI?

In Power Query/Query Editor you can choose as many fields as you want with the order you want, just press ctrl key and choose them in the right order.