Cannot create relationship

[GCGradwell]

Hi all,

Having trouble creating a relationship between 2 columns which persists after trying all the normal fixes I have.

I'm trying to create the relationship between Customer Name fields in 2 different tables - the original sales summary table and a second table which was duplicated from the sales summary and the grouped by customer name.

The sales summary has multiple sales under each customer, so this will be the 'many' side to the relationship. The grouped table is grouped by customer name, so should only have 1 entry under each customer name.

The solutions I have tried that normally work and have not this time:

1. Checked data types are the same (both text).

2. Removed blank rows from BOTH columns.

3. Removed duplicates from grouped column.

I get the error message 'You can't create a relationship between these two columns because one of the columns must have unique values'

Thanks for any help in advance,

Greg

[kevhav]

This is happening to me, too. I created a new table, containing one column (a composite key), by appending data from two other tables. Then, in the query, I added a "Remove Duplicates" step to get rid of duplicate composite keys. 

Still, when I try to create a relationship in Power BI with this column, it says "You can't create a relationship between these two columns because one of the columns must have unique values."

I exported the table to Excel, and verified in Excel that there are no duplicates.

Help!

[kevhav]

Actually, in the Query Editor, it did not have all unique values. Some were the same except for differences in case. When it got into the Power Pivot/DAX model in Power BI, somehow the "duplicates" (same but different case) had disappeared and there were fewer items in the table. That is weird.

Solution: change my composite keys, which contain text, to "UPPERCASE" or "lowercase"

[v-qiuyu-msft]

Hi @GCGradwell,

In your scenario, how did you get the table grouped by the customer name? Please make sure the table is only grouped by customer name. If the table also grouped by other columns, it's possible that the customer name column values in the new table are not distinct.

Please create a new table use the DAX like below:

New Table = SUMMARIZE('sales_summary ',[customer_name],"Count",COUNTA([Other_Fields]),"Total",SUM([Other_Nemuric_Fields]),...)

Then create a relationship between this new table with sales summary table. If the issue persists, please share us .PBIX to analyze the issue if possible.

Best Regards,
Qiuyun Yu

[jcarville]

Try setting the cross filter direction as single

[GCGradwell]

It does not give me this option - it says that one of the two columns must have unique values. I have removed duplicates and checked this many times but no luck!

Any ideas?

[Seth_C_Bauer]

@GCGradwell This can be extremely frustrating, but I haven't come across this scenerio yet, where there wasn't a duplicate. Have you searched for and eliminated any hidden characters? Carriage returns, etc? Sometimes a simple action to remove blank spaces will ignore these in the database, causing major headaches.

Unless the dataset is small enough that you've manually gone through and checked them all, then ignore me 🙂

But in my experiance, it is something like this and there is one duplicate in the bunch.

[KHorseman]

For really stubborn sets where you're sure there are no duplicates but Power BI insists that there are, you can write a measure to check.

Duplicates = COUNTA(TableName[ColumnThatShouldn'tHaveDuplicates])

...assuming it's a text column. If it's a numeric column use COUNT instead of COUNTA. Anyway the important part is do not use DISTINCTCOUNT. Now, put that column on a table or matrix visual and put the [Duplicates] measure with it, sort descending by [Duplicates] and the duplicated values from that column will be on the top.

[KHorseman]

Are there already other relationships between these tables and other tables in your dataset?

[majdkaid22]

@GCGradwell it could be your Grouped table has some blank cells that are not being deleted when you're performing delete blank rows.

If the source file is Excel? try to shape the original file and refresh. I had a similar situation and this solved it

[GCGradwell]

It was not generated in Excel - it is from MySQL Query to a database. Any ideas?