Calculated column error for duplicate value but no reference

[DreDre]

I am running into an issuse with a report I created and I would appreciate any feedback. 

I have been working with a subset of my data to reduce refresh time as I built a new report. With my relationships / pages already set I expanded out to my full dataset. Once I did this I am getting the following error for one of my calculated columns:

"Column '' in Table '' contains a duplicate value '40' 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."

I've spent some time trying to find this issue but it does not tell me which collum / table has the issue. I audited the columns referenced but do not see any duplicates. Is this a common error? How do I identify where the error is occuring if the error provided by PBI is specific about the duplicated value but not the location. 

If I change my calculated column's function TOPN to be a different number I get the following:

"A table of multiple values was supplied where a single value was expected."

Bank_Key (calculated column with the error) =

CALCULATE(

VALUES(Key_Banks[Index]),

TOPN( 1,

FILTER(Key_Banks,Key_Banks[ROW_EFF_DT] <= Key_Schedule[Schedule_Start]),

Key_Banks[ROW_EFF_DT],

)

)

[TheoC · Accepted solution]

Hi @DreDre 

From my understanding, the error is saying that there is duplicate value in the Table[Column] you're referring to and that particular column is being used as the relationship to another Table2[Column2]. And because you are using Table[Column] as the relationship (the "one" in the one to many), Power BI is pulling an error.

Although it's not best practice, can you go to your Data Model and switch the relationship from One to Many to Many to Many and see what happens?  

Theo 🙂

[DreDre · Accepted solution]

This issuse was actually a problem with my data. The relationship needed to be many to many, but I was missing the secondary relationship that also needed to be adjsuted. Once I changed both relationships this fixed my issue! Thank-you for those that answered, I appreciate the help!

[TheoC · Accepted solution]

Hi @DreDre 

From my understanding, the error is saying that there is duplicate value in the Table[Column] you're referring to and that particular column is being used as the relationship to another Table2[Column2]. And because you are using Table[Column] as the relationship (the "one" in the one to many), Power BI is pulling an error.

Although it's not best practice, can you go to your Data Model and switch the relationship from One to Many to Many to Many and see what happens?  

Theo 🙂

[DreDre]

I was able to find the error in excel and it is something that is needed by design. But I missed the second releationship associated to this column! I switched both of these to many to many and it works! Than-you for the suggestion @TheoC !

[DreDre]

That is what I am guessing as well, but the error code PBI is giving does not inlcude a table/column name. I tried switching to many to many and the error persisted. I am combing through the data again to make sure it is not something that is being pulled in.