Many to one relationship

[aodwyer]

Hi,

I have a report with multiple data sources. I am trying to connect “Datasource – ActiveAdditionsReport” to “Datasource – WonOpportunities” using the fields Company Rec_ID. 

The file called “Datasource – ActiveAdditionsReport” shows all client agreements and has multiple duplicate rows, as one client could have 5 different agreements with us.

The file called “Datasource – WonOpportunities” had multiple duplicate rows when I imported the database but using PowerQuery M with sorted rows, I have removed all duplicates from this report and left only the newest dated won opportunity per client. 

However, when I try to connect these two data sources together, I can’t do a 'many to one' relationship as I get a cardinality error message. 

Error message

Does anyone know what’s causing this error?

I need the “Datasource – ActiveAdditionsReport” to filter the won opportunities report, otherwise any client who doesn’t have a won opportunity against their name won’t appear on my list. 

Any help you can offer me would be greatly appreciated, thanks.

[Watsky · Accepted solution]

Check Datasource – WonOpportunities doesn't have any blanks. Multiple blanks would be considered duplicates.  

[aodwyer]

This worked for me. Thanks a million for your help. I didn't think to check the blanks. 

[Watsky]

@aodwyer I had the same issue before. I just had to keep telling myself that Power BI is looking at the data in a litteral sense. If two values match no matter if it's an actual value or the inverse, then it's a duplicate. As a best practice you should check this on all of your key columns when you start and remove them. Since you're using them as a key column you would want to remove them.

[amitchandak]

Does company Rec Id is a unique key in Won Opportunity.

TO check but this on Table and drag it again and take count and sort descend on the count. There should not be any two values.

[aodwyer]

Thanks, I checked this but it was duplicate blank rows that were causing the problem.