Cannot create relationships - Power BI finding duplicates that do not exist

[mahawkins3]

Hi all,

I am trying to create a relationship between two tables.

Table 1 ('CLTV') contains all transactions, including the User_ID for the customer making each transaction.

Table 2 is a calculated table created using DISTINCT(CLTV[User_ID]), so by definition each item in that table must be distinct.

Firstly, when I try creating a relationship in the Relationships page by dragging and dropping, Power BI crashes (the "SQLDUMPER.EXE" issue that has been raised elsewhere).

However, when I try creating a relationship between the two tables using the "Manage Relationships" button on the ribbon as advised in the thread above (with User_ID as the relationship column in both tables), it gives me the following error:

This makes no sense to me - as I mentioned above, I used the DISTINCT function to create the column in Table 2 that I am using for the relationship, so there is no way that the column in Table 2 does not have unique values. I also tried using VALUES, which didn't work either.

I have created relationships in the past using exactly the same logic and have never had a problem - has anybody else come across this error?

Thanks

[v-chuncz-msft · Accepted solution]

@mahawkins3,

You may use the formula below.

Table =
FILTER ( DISTINCT ( CLTV[User_ID] ), NOT ( ISBLANK ( CLTV[User_ID] ) ) )

[mahawkins3]

Thanks for the replies - both are correct; accepted @v-chuncz-msft as it's a more direct solution.

[WolfBiber]

Hey,

look if you have (Null) value and maybe an empty value '' in your table. As far I can remember both are treated equal for an relationship. So you have to filter them out