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 columns)
========                       ============            ==============
Booking Reference               Booking Reference              Booking Reference
BookingDate                       EMail Sent
Brand                                  EMail Address
Amount
Customer First Name
Customer Last Name
Etc . . .

So Bookings & BookingsEMails tables have multiple rows for the same Booking Reference, hence the DISTINCT Booking Reference BookingsLINK table is required to JOIN Bookings to BookingsEMails.

The relationships exist between Bookings -> BookingsLink and BookingsEMails -> BookingsLink on Booking Reference (Many to One)

So I can create a tabular report selecting BookingsLink.BookingReference and then Bookings.BookingDate, Bookings.Brand and Bookings.Amount, and the same against BookingEMails, selecting BookingsLink.BookingReference and then BookingsEMails.EMailSent.

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.

I have many columns on Bookings and BookingsEMails that I want to report together on the same tabular report.

Please can you help!

Many Thanks.

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.

15 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    HI berta2b,

     

    Power bi not support many to many relationship, you need to use table which has the unique key to link to those tables.

     

    For example:

     

    Table(Many)    ——>     Table(one)     <——      Table(Many)

                       (Many to one)               (one to many)

     

    BTW, multiple blank records also will identified as the duplicate records, if your unique table contains them, you should remove them before create relationship.

     

    Regards,

    Xiaoxin Sheng

    • berta2b's avatar
      berta2b
      Helper I
      9 years ago

      Hi Xiaoxin, thanks so much for your reply.

       

      I have created this unique table, BookingsLink which has unique keys allowing the Many to one, One to Many between the two other tables.

       

      My problem is although this relationship has been setup within Power BI Desktop 'Relationships' where I would think would resolve this, I can't access columns from the Table(Many) tables in one tabular visualisation report.

       

      There are no blank keys, the Link table contains just the unique keys and no blanks.

       

      Thanks again.

       

      Bert

      • berta2b's avatar
        berta2b
        Helper I
        9 years ago

        Sorry I have to apologise, I have confused the issue somewhat, when I copied and pasted the tables structures the columns moved around a bit!

         

        Here is the correct design.

         

        Bookings (30 columns)        BookingsLink                       BookingsEMails (20 columns)
        ========                       ============            ==============
        Booking Reference               Booking Reference              Booking Reference
        BookingDate                                                                  EMail Sent
        Brand                                 
        Amount
        Customer First Name
        Customer Last Name
        Etc . . .