Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello, I am new to Power BI and learning as I go (mostly from this forum!). I have a two tables that are related. The first is opportunities (Salesforce)
Id Name Amount
8001... Opp1 $50
8002... Opp2 $75
8003... Opp3 $40
The second table is called Proposals. It has a many to one relationship with opportunities.
Proposal Id Opportunity Amount Countries
p019 8001... $50 Ghana; Nigeria
p020 8001... $75 Ghana; Nigeria; Zimbabwe
I am working on building a report that includes a slicer for country and a map visual. I think the right approach here is to split the countries column into multiple rows. However, when I do that, I get an error message that says:
"Proposal - Column 'Id' in Table 'Proposal' contains a duplicate value 'a001U0000064hRXQAY' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table."
This makes sense to me, because now the table has multiple rows of proposals with the same opportunity Id. I'm wondering if I need a different approach or if it's possible to create a separate table, just for countries, that has a relationship to proposals, rather than to opportunities, but I'm not sure how to do that.
I appreciate any help or ideas on where to find documentation or tutorials on this - I've found plenty of things about splitting columns but nothing about how to handle this relationship error as a result of creating multiple rows like this.
Thanks!
Alex
Solved! Go to Solution.
Hi @Anonymous ,
Looking at your mockup data and putting it on a PBIX file I got no error I created a one (Opportunities) to many (Proposals) relationship between both tables.
But reading your post again I detected that you wrote:
"Proposal - Column 'Id' in Table 'Proposal' contains a duplicate value 'a001U0000064hRXQAY' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table."
Believe that your issue is in the way you setup your relationship, you need to add a relation that links the Opportunity ID (Opportunity table) with Opportunity ID (Proposals Table) be aware that the one side is on the opportunity side and not on the proposal as you refer.
Check the PBIX file attach with your mockupdata.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsRegards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix Thank you for your reply. That makes sense, and I was able to create a new table with the distinct values from the Opportunity and set up those relationships. But, now I'm getting an error that says that it's "not allowed for columns on one side of many to one relationship". There are duplicate values in the proposal Id column, which is the primary key for that table.
I checked and there are no other relationships between the proposal table and the Idopportunities table. I guess the issue is that it's because the proposal Id is the primary key for that table, and now there are duplicates? Perhaps I should duplicate the entire proposal table so I can make that proposal Id column a foreign key?
The basic issue I have is that I have some fields in my data that are semi-colon separated values, and so I am trying to transform my data to work with them, ideally as separate rows.
I appreciate your help!
Hi @Anonymous ,
If you have a table with unique ID the error you should have no errors when connecting this table with the other two.
Can you share a sample of the file? If information is sensitive send it trhough private message or a mockup data will do.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
I believe the error is due to splitting the proposal table into rows, so there are now duplicates of the Proposal Id field in that table. Here is some mockup data.
I have a table of proposals which is related to opportunity via the opportunity Id field:
Proposal Id | Name | Opportunity Id | Countries | IsLatest? |
a001 | Proposal - 00480 | 00081 | USA; Ghana; Liberia | TRUE |
a002 | Proposal - 00481 | 00082 | Lithuania; France | TRUE |
a003 | Proposal - 00482 | 00083 | Australia; Japan | TRUE |
a004 | Proposal - 00483 | 00084 | France; Spain; Italy | TRUE |
a005 | Proposal - 00483 | 00084 | France; Spain | FALSE |
Opportunity Id | AccountId | Name | Amount |
00081 | 00013 | Opportunity 1 | $100,000 |
00082 | 00013 | Opportunity 2 | $4,000 |
00083 | 00014 | Opportunity 3 | $6,000 |
00084 | 00015 | Opportunity 4 | $25,000 |
I would like to split the countries field so that I can report on the individual values. When I split that field into new rows, the data looks like this:
Proposal Id | Name | Opportunity | Countries | IsLatest? |
a001 | Proposal - 00480 | 00081 | USA | TRUE |
a001 | Proposal - 00480 | 00081 | Ghana | TRUE |
a001 | Proposal - 00480 | 00081 | Liberia | TRUE |
a002 | Proposal - 00481 | 00082 | Lithuania | TRUE |
a002 | Proposal - 00481 | 00082 | France | TRUE |
a003 | Proposal - 00482 | 00083 | Australia | TRUE |
a003 | Proposal - 00482 | 00083 | Japan | TRUE |
a004 | Proposal - 00483 | 00084 | France | TRUE |
a004 | Proposal - 00483 | 00084 | Spain | TRUE |
a004 | Proposal - 00483 | 00084 | Italy | TRUE |
a005 | Proposal - 00483 | 00084 | France | FALSE |
a005 | Proposal - 00483 | 00084 | Spain | FALSE |
So I am wondering if the issue is that the Proposal Id now has duplicate values, and since it is the primary key for that table it is causing the error.
Hi @Anonymous ,
Looking at your mockup data and putting it on a PBIX file I got no error I created a one (Opportunities) to many (Proposals) relationship between both tables.
But reading your post again I detected that you wrote:
"Proposal - Column 'Id' in Table 'Proposal' contains a duplicate value 'a001U0000064hRXQAY' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table."
Believe that your issue is in the way you setup your relationship, you need to add a relation that links the Opportunity ID (Opportunity table) with Opportunity ID (Proposals Table) be aware that the one side is on the opportunity side and not on the proposal as you refer.
Check the PBIX file attach with your mockupdata.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português