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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
GiangLe
Helper I
Helper I

composite primary key

Hi everyone, 

 

I'm quite new to Power BI and it's everyday story that i stumble upon something new which is good.

My today question is about data modelling. In many to many relationships, it is a must to create a key to connect 2 tables. today i come accross the concept of composite primary which is a key made up from many collumn. However, i'm struggle to understand in which case i need create this composite primary key?

and how many methods i can do this? i've seen people do with with concatenate syntax

thanks a lots

 

1 ACCEPTED SOLUTION
GeorgeBuster
Advocate III
Advocate III

Hi GiangLe,

 

As you know, in Power BI you need a single column to link two tables (you can't define a key with two columns as you can in common databases). This column has necessarily to have unique values on one side of the relationship because Power BI doesn't work directly with a "many to many" relationship. In conclusion,  you will need to create a key using two columns with CONCATENATE or create a table with unique values between tables with non-unique values.

Example: You have two fact tables with the sales of your employees and other with the wages of them, both have two columns with the name and surname of the employees. Firstly, you can define a primary key concatenate their name and surname and secondly, you need two create a table between them with the unique values of each employee. In that way, you will have a many (sales table) to one (employee table) relationship and a one (employee table) to many (wages table) relationship and PowerBI will work perfectly.

View solution in original post

2 REPLIES 2
GeorgeBuster
Advocate III
Advocate III

Hi GiangLe,

 

As you know, in Power BI you need a single column to link two tables (you can't define a key with two columns as you can in common databases). This column has necessarily to have unique values on one side of the relationship because Power BI doesn't work directly with a "many to many" relationship. In conclusion,  you will need to create a key using two columns with CONCATENATE or create a table with unique values between tables with non-unique values.

Example: You have two fact tables with the sales of your employees and other with the wages of them, both have two columns with the name and surname of the employees. Firstly, you can define a primary key concatenate their name and surname and secondly, you need two create a table between them with the unique values of each employee. In that way, you will have a many (sales table) to one (employee table) relationship and a one (employee table) to many (wages table) relationship and PowerBI will work perfectly.

GiangLe
Helper I
Helper I

help pls 😛

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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