Forum Discussion

DreDre's avatar
DreDre
Helper II
4 years ago
Solved

Calculated column error for duplicate value but no reference

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],
)
)
 
 
Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    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's avatar
    DreDre
    4 years ago

    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!

4 Replies

  • DreDre's avatar
    DreDre
    Helper II
    4 years ago

    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's avatar
    TheoC
    Community Champion
    4 years ago

    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's avatar
      DreDre
      Helper II
      4 years ago

      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's avatar
      DreDre
      Helper II
      4 years ago

      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.