Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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
How to put dynamic role level security on this case?
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
Hi @berta2b,
Did you check the 'cross filter direction' option at relationship setting? If it has setting to 'single', the relationship only works on one side, you can modify it to 'both' to work through it.
Regards,
Xiaoxin Sheng
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 . . .
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
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
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
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
Hi Xiaoxin, I'm preparing this for you now.
Many Thanks
Bert
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
Hi @berta2b,
You can upload it to onedrive and paste the link here.
Regards,
Xiaoxin Sheng
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |