Cannot create relationship between two table with primary key in psql

[Anonymous]

I have three tables, making a many-to-many relationship between products and releases (it is an enterprise solution, and we have releases for the whole package, thats why we have multiple products with the same release). I have a table for products, releases and and association table with two foreign key columns. I have easily made the product - association relationship, but the other is always failing with "You can't create a relationship between these two columns beacouse one of the columns must have unique values.", which is strange as the ID I am connecting with is a primary key in the release table with unique constraint. Can anybody suggest something?

[v-huizhn-msft]

Hi @Anonymous,

Right click your table->Edit Query, you will go to Query Editor, click ID column->remove the duplicates as follows, and check if it works fine.



Best Regards,
Angelia

[Anonymous]

Hello,

I have also tried that, and it did not help too. And of course it did not remove any row, as there is a unique constraint on that column in the database. The error still persist 😞

Best Regards,

Szabolcs

[DAX0110]

Hi @Anonymous, you could try checking if the "One side" has any null or blank values?  That could trigger the error you saw.

[Anonymous]

Hello,

Unfortunatelly I have done that, I have checked both in DB and in PBI. There was one value with the string "-", which I suspected that it may be interpreted as null by PBI, so I removed that. Despite that, the error is still there.

Br,

Szabolcs

[DAX0110]

Hi @Anonymous, you could try reducing the problem by loading just 1 row from each table - then try relate them and see what happens.

[Anonymous]

Hi,

It has worked, almost :(! At least I was able to make the relationship when I reduced the query to the first 20 rows. However, it is not a solution as now I am not able to use the full table, because when I remove the step of keeping the first 20 rows, then I cannot apply the change as it says that there is a duplicate value. One addition, that it at least tells me what is duplicated. However, I have checked in the database, that there is only one such value:

SELECT count(*) FROM releases WHERE id='<name of release>';
count
-------
1
(1 row)

Br,

Szabolcs

P.S.: I really appretiate the fast responses! Thanks all!

[v-huizhn-msft]

Hi @Anonymous,

When you get data from psql, please add SQL Statement below in the SQL statement box. Then create the relationship and check if it works fine.

SELECT DISTINCT(A.ID) from release A

Best Regards,
Angelia

[Anonymous]

Hello @v-huizhn-msft,

Good idea, but still the same results...

Br, 

Szabolcs

[Anonymous · Accepted solution]

Hello,

It turned out that Power BI is mostly case sensitive (Table.Distinct), except when creating relationships. In that case it is case insensitive, so it will detect duplicated values if, for example, 'Some ID' and 'some id' is present too.

Szabolcs