Relationship error when splitting column into rows

[Anonymous]

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

[MFelix]

Hi @Anonymous,

This error is giving because you have the relationship active so the change you made are not passing to the final model.

You should create a new table with unique I'd of opportunities and them relate this new table with the other to tables so this dimension table will act as the connector on relating the other two tables.

In summary you would have 3 tables
Opportunities
Proposals
IDoppprtunities (only unique values)

And 2 relationship one to many between the IDopportunities and the other two tables.

Regards,
MFelix

[Anonymous]

Hi @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!

[MFelix]

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

[Anonymous]

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

[MFelix · Accepted 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

[Anonymous]

Thank you @MFelix! That resolved the error. I appreciate your help.