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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.