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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Uli_from_German
New Member

Relationships and modelling data

I have a problem with modelling Data-tables :

Table One :   Buildings (Definition of houses) 

                      Column :  Building_ID  120

                                       Building_ID  140

                                       Building_ID  250

 

Table Two:     Offices and rooms in Buildings

                       Columns:  Office_Building_ID  120 and Office_No_ID  01 (like for Wprkingroom)

                                        Office_Building_ID  120 and Office-NO_ID  02 (like for Meetingroom)  

                                        Office_Building_ID  120  and Office_No_ID  03 (like Cafeteria)

                                        Office_Building_ID  140  and Office_No_ID  01 (like for Workingroom)

                                        Office_Building_ID   140  and Office_No_ID  02 (like for Meetingroom)

                                         Office_Building_ID  140  and Office_No_ID  03 (like for Cafeteria)

Table Three:   Cost-Records

                       Columns:   Cost_Buidling_ID  120  and Cost_Office_ID  01 

                                         Cost_Buidling_ID 140 and Cost_Office_ID 02

 

therefore: I need two Key element to connect. And I do not know which is correct and possible. What must I do ?

with tableau there is no problem : I define 2 Keys in relationship.  But I will work with power BI and think its better tool.

Please help. thanks and kind regards uli 

5 REPLIES 5
Uli_from_German
New Member

Thank you very much:  comments and help were succesful and I was positiv suprised about quick answers.  it makes fun to work with BI.  kind regards uli 

@Uli_from_German

 

You can mark the right answer as solution if you have your problem get solved. So that other users who have the same problem can figure out the solution quickly. Smiley Happy

 

Best Regards,

Herbert

v-haibl-msft
Employee
Employee

@Uli_from_German

 

In PBI, we cannot have two active relationships between two tables. We can define the relationship as below so that there will be only one active relationship between two tables.

You can take a look at this official document to understand more about the relationship in Power BI.

Relationships and modelling data_1.jpg

 

If you want to have two relationships between two tables, you can inactive one of the relationship as below. And use USERELATIONSHIP Function (DAX) to specify the relationship to be used in a specific calculation when you need it.

Relationships and modelling data_2.jpg

 

Best Regards,

Herbert

alexanderg
Advocate II
Advocate II

Hi @Uli_from_German

 

In Power BI the relationships between two tables must use only one field. If you want to create a relationship based on two or more fields, that is a compound key; For this you have to create a new column concatenating the key fields. You must do the same process in the other table.. and then you can establish the relationship.

 

Regards,

ImkeF
Super User
Super User

Hi Uli,

of the 3 tables you provided only one table has 2 keys. So if you want to connect tbl1 with tbl2 on one key and tbl3 with tbl2 on a different key, that's no problem at all. Or are there other tables to connect?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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