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

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

 

 

berta2b's avatar
berta2b
Helper I
9 years ago

Hi Xiaoxin, here's the correct design again

 

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

 

So the BookingsLink table has only one columns 'Booking Reference' which is unique and I'm trying to select 'Booking Date' and 'EMail Sent' for the same tabular report and then the issue.

 

Thanks and sorry for the confusion again.

Bert

  • berta2b's avatar
    berta2b
    Helper I
    9 years ago

    Hey Xiaoxin, just seen your post to check the bi-directional option.

     

    I'll give this a try and get back to you.

     

    Many Thanks.

    Bert

    • berta2b's avatar
      berta2b
      Helper I
      9 years ago

      Hi Xiaoxin, I tried your suggestion but this didn't work I'm afraid :(

       

      I set the 'Cross Filter direction' to BOTH on my 'Many to One' relation 'Bookings' -> 'BookingsLink' and 'BookingEMails' -> 'BookingsLink' and still had the same issue.

       

      Bert

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi berta2b,


        >>I set the 'Cross Filter direction' to BOTH on my 'Many to One' relation 'Bookings' -> 'BookingsLink' and 'BookingEMails' -> 'BookingsLink' and still had the same issue.

        Can you provide the sample file to test? It is hard to troubleshooting without sample file.

         

        Regards,

        Xiaoxin Sheng