Power Pivot Relations - Many-to-One-Unique but Still Error

[ajones]

I have a series of tables that i was trying to create some relations for. It seems like no matter what I do I get the error "The relationship cannot be created because each column contains duplicate values. Select at least one column that contains unique values."

I am making the relation off calculated columns where in each table i concatenate two columns to one. This forces one of the tables to have all unique values and the other table to match up. However I am still getting the above error. I tried making the relationship "both ways" starting from the "many" and going to the "one" and the other way around. But still get the error.

Then I tried making other relations, but everything seem to give that same error.

Am I missing something?


thanks for any and all suggestions

Alan

 

[miguel.escobar]

Is there any way that we could have access to the file to see what's going on?

 

[ajones]

Miguel.escobar,

thanks for the reply.... i just figured it out..... there were 2 duplicates of screwy data in what what was supposed to be unique data... I am going to adjust my concatenation to fix the issue. There were about 5,000 rows so i did pivot on just that data then had copy the data so i could filter on the count.... and found the one that had 2. now thinking of easier ways to find the few lines that may be duplicate in related data.

Now if PowerPivot just had an option to allow for multiple relations, from one table to another table. I have two columns in one table that both relate independently back to another table.

 

[miguel.escobar]

Well, you can but one of them (relationships) will remain inactive and when you try to create measures or pivot you'lll need to use this
USERELATIONSHIP Function (DAX)

 

[ajones]

wow interesting function... i will have to play with that.

 

[miguel.escobar]

Reading again, I believe that you were talking about 2 columns that are related between 2 tables...the formula USERELATIONSHIP is used when you have a column in a table that can be related to multiple tables but just one of them(relationships) would remain active. Just wanted to clarify that