Relationships and modelling data

Question

I have a problem with modelling Data-tables :Table One : &nbsp; Buildings (Definition of houses)&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Column : &nbsp;Building_ID &nbsp;120&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Building_ID &nbsp;140&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Building_ID &nbsp;250&nbsp;Table Two: &nbsp; &nbsp; Offices and rooms in Buildings&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Columns: &nbsp;Office_Building_ID &nbsp;120 and Office_No_ID &nbsp;01 (like for Wprkingroom)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Office_Building_ID &nbsp;120 and Office-NO_ID &nbsp;02 (like for Meetingroom) &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Office_Building_ID &nbsp;120 &nbsp;and Office_No_ID &nbsp;03 (like Cafeteria)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Office_Building_ID &nbsp;140 &nbsp;and Office_No_ID &nbsp;01 (like for Workingroom)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Office_Building_ID &nbsp; 140 &nbsp;and Office_No_ID &nbsp;02 (like for Meetingroom)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Office_Building_ID &nbsp;140 &nbsp;and Office_No_ID &nbsp;03 (like for Cafeteria)Table Three: &nbsp; Cost-Records&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Columns: &nbsp; Cost_Buidling_ID &nbsp;120 &nbsp;and Cost_Office_ID &nbsp;01&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Cost_Buidling_ID 140 and Cost_Office_ID 02&nbsp;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. &nbsp;But I will work with power BI and think its better tool.Please help. thanks and kind regards uli&nbsp;

imkef · Answer

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?

alexanderg · Answer

Hi&nbsp;Uli_from_German&nbsp;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.&nbsp;Regards,

v-haibl-msft · Answer

Uli_from_German&nbsp;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.&nbsp;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.&nbsp;Best Regards,Herbert

v-haibl-msft · Answer

Uli_from_German&nbsp;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.&nbsp;:smileyhappy:&nbsp;Best Regards,Herbert

uli_from_german · Answer

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

Forum Discussion

Relationships and modelling data

5 Replies

Related content

Ambiguity Error Managing Relationships in data model

Data Modelling and Relationships

Data Modeling

Data modeling: flip relationship direction

Relationship