Relationships and modelling data

[Uli_from_German]

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 

[Uli_from_German]

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 

[v-haibl-msft]

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

Best Regards,

Herbert

[v-haibl-msft]

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

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.

Best Regards,

Herbert

[alexanderg]

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]

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?