Forum Discussion

berta2b's avatar
berta2b
Helper I
9 years ago
Solved

Power BI Desktop Many to Many Relationships

Hi there guys, have an issue with typical many-to-many relationships in Power BI desktop. Here's my scenario: Bookings (30 columns)        BookingsLink                       BookingsEMails (20 ...
Need Help
  • berta2b's avatar
    berta2b
    9 years ago

    Hi Xiaoxin, sorry for not getting back to you earlier.

     

    Yes that is the case, the two outer tables have multiple records and not single entries so I agree Power BI won't support this.

     

    I've remodelled the data tables to insteadof trying to have multiple records in a many-to-many relationship that all the columns from these tables are added to one table which is a work around.

     

    Many Thanks Xiaoxin for all your help with this issue.

     

    Regards

    Bert.

berta2b's avatar
berta2b
Helper I
9 years ago

Hi Xiaoxin, I'm preparing this for you now.

 

Many Thanks

Bert

berta2b's avatar
berta2b
Helper I
9 years ago

Hey Xiaoxin, I have the sample file prepared for you. How can I get this sent as I can't add attachments to this message?

 

Many Thanks.

Bert

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi berta2b,

     

    You can upload it to onedrive and paste the link here.

     

    Regards,

    Xiaoxin Sheng

  • berta2b's avatar
    berta2b
    Helper I
    9 years ago

    Hi Xiaoxin, will load to OneDrive location and send link.

     

    Before I do, the file will be password encrypted so how can I best send this password to you?

     

    Thanks

    Bert

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi berta2b,

     

    This is fine, but when I try and create a report using for example BookingsLink.BookingReference, BookingDate AND THEN BookingsEMails.EMailSent I get the 'Can't determine relationships between the fields' error although the many-to-many relationships exist.

     

    As you mentioned, if you use the unique table key to link column which contains in multiple side, it will be works normal?

    If this is a case, It seems like power bi not support direct mapping the multiple records to multiple records.

     

    I'd like to suggest you use the unique key as the route to mapping those records.

     

    Regards,

    Xiaoxin Sheng

  • berta2b's avatar
    berta2b
    Helper I
    9 years ago

    Hi Xiaoxin, sorry for not getting back to you earlier.

     

    Yes that is the case, the two outer tables have multiple records and not single entries so I agree Power BI won't support this.

     

    I've remodelled the data tables to insteadof trying to have multiple records in a many-to-many relationship that all the columns from these tables are added to one table which is a work around.

     

    Many Thanks Xiaoxin for all your help with this issue.

     

    Regards

    Bert.