Help! Duplicate error when expanding custom column to new rows

[Gingerjeans88]

Hi all, 

I am building a visual that shows the sum of all sales opportunities by month - aka. A £12k opportunity would be 12 x £1k monthly instalments, and my visual is the sum of that value for ALL open and won opps.

usually I do this with a revenue month column that adds 11 additional rows to each opportunity in my table (using 'expand to new rows'), then I assign a 12th of the value (custom column ([weightedvalue]/12) to each of those rows. 

However, when I apply the changes I get a 'duplicate values blah blah one to many relationship' error. I presume it's to do with having many rows with the same primarykey (opportunity ID - it's CRM data)?

I don't understand, because I have used this exact method before with no issue. Is this logic sound and perhaps it's an issue with a relationship I have? Perhaps to customer. 

I can provide the error if needed, but I'm mostly interested in knowing if my above method seems logical and is still supported?

[v-xiaoyan-msft]

Hi@Gingerjeans88,

According to the error message, the expanded two columns contain duplicate values.
In order to create one to many relationships between tables, you need at least one column to contain only unique values and you need to eliminate duplicate values, otherwise it will not work.

Please show more details without sensitive Information ，we will be better able to help.

Hope it helps.

Best Regards,

Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

I'd have to see your query dependencies in Power Query to really help, but what is happening is your DIM table for your opportinities no longer has a unique value in the primary key. It is not necessary in opportunity fact table that has data by month. That is ok to be a many. But the opportunity DIM  table must only have unique opportunity IDs on the 1 of the 1:Many Relationship.