Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
brianpetersen
New Member

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

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?

1 ACCEPTED SOLUTION
andre
Memorable Member
Memorable Member

@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.

View solution in original post

4 REPLIES 4
andre
Memorable Member
Memorable Member

@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.

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
Helper I
Helper I

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
Advocate IV
Advocate IV

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.

Author, Consultant, Speaker
https://radacad.com

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.