Keep getting unique values error when making a relationship

[Veles]

Hi all,

I've been trying to create a relationship between two tables and keep getting the "You can't create a relationship between these two columns because one of the columns must have unique values." error.

I've removed all duplicate values and using a table visual for my lookup list, there are no IDs with a count greater than 1. I've even made a table using =values method and then linked the two together and still get my error.

[v-shex-msft]

Hi @Veles,

I think your tables may contains duplicate records(duplicate records include blank records). For you scenario, you can merge key columns of these tables and use distinct function to remove duplicate records. Then create relationships between this table and above tables.

Sample:

New Table= DISTINCT(UNION(ALL(TableA[Column]),ALL(TableB[Column])))

In addtion, you can also try to remove these duplicate records in power query.

Regards,

Xiaoxin Sheng

[xuri]

Hi,

I think there is another way to prove if the values are actually unique or the view in Power BI makes you believe: go to the "Data" view (where you can see the content of your tables) and "Copy Table" via right click on the table you want to prove. Then open an empty Excel file and paste what you have copied. In Excel you can check duplicates with a marking condition. Do this separately for both tables.

What you can also do is to create a table and create a relationship to both tables.

DISTINCT('YourTableName'[YourColumnName])

I hope this is a help for you.