Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DreDre
Helper II
Helper II

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],
)
)
 
 
2 ACCEPTED SOLUTIONS
TheoC
Super User
Super User

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 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

DreDre
Helper II
Helper II

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!

View solution in original post

4 REPLIES 4
DreDre
Helper II
Helper II

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
Super User
Super User

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 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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 !

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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors