Relationship and Unique Values

[klammymaster]

Hi there,

I have a 2 files with many entries trying to relate them together via email addresses. When I try relating the 2 together, I get an error "You can't create a relationship between these two columns because one of the columns must have unique values".  1 file has over 40,000 rows and the other has about 7000.

So far I've done: Removed errors, trimmed text, removed blanks, removed nulls, lowercased cells, tried both CSV and XLSX file formats, and created a new query with all of the emails to make it a middle of the relationship.

 I've been stuck with this issue for 2 days now and can't figure out what I'm doing wrong. 

[Anonymous]

This seems to be a very common frustration, so I have posted some additional info in a similar thread: https://community.powerbi.com/t5/Desktop/Can-t-create-a-relationship-between-two-columns-because-one...

[zenmemo]

@klammymaster so you created a bridge table (removing the duplicate emails) and still you weren't able to connect to the original tables as one-many relationship?

[klammymaster]

Hi @zenmemo,

This is the video that I followed: https://www.youtube.com/watch?v=vAvQ8pCnWDk

I was not able to connect anything to the tables with the new table.

[zenmemo]

@klammymaster, well if your bridge table is void of dupl or null values, it should work. here's an example.

[klammymaster]

Hi @zenmemo,

Yes that's exactly what I'm trying to do but I'm not sure why it's not working. Attached are the steps I've done to remove the blanks, errors, null, and etc.

 

[zenmemo]

@klammymaster, I don't see that you filtered against null values or removed dupl. That would also contribute to the non-unique value error.

[Phil_Seamark · Accepted solution]

Hi @klammymaster

Here is a DAX Soluction for creating a slicer table that will have unique values that you can create relationships too.

Then use the field in this table for your slicers

Slicer Table = 
    DISTINCT(
        UNION (
            ALL('biibuserdata'[biibEmail]) ,
            ALL(Intune[Email address])
            )
            )

[klammymaster]

Hi @Phil_Seamark,

Where do I put the DAX formula? I tried creating a new measure and column and that didn't work. I also tried to create a Slicer Table but I'm not sure where to put the formula. 

[Phil_Seamark]

HI @zenmemo

On the Modeling Tab of Power BI Desktop, you should see a New Table button.  Click this and paste my code into the formula bar

This should create a table that will be visible in the Relationship View where you can create relationships to it from your existing tables.

[klammymaster]

Hi @Phil_Seamark,

You're amazing. Thank you so much for your help 🙂 Appreciate it a lot.

[Phil_Seamark]

HI @klammymaster

Are you expecting the email addresses to be unique in one of the files?  Or can the same email naturally occur more than once in the tables?

[klammymaster]

Hi @Phil_Seamark,

The same emails will occur more than once in the smaller file. The smaller file contains eletronic device assignments and 1 user can have multiple devices. The bigger file is a gigantic list with all users. Please let me know if I can give you more detail.

[Phil_Seamark]

Hi @klammymaster

WHat is the name of your two tables, including the names of the columns that have the email addreses?

I can give you a formular for a calculated table that you can use as a slicer.

[klammymaster]

Hi @Phil_Seamark,

Table 1 is "biibuserdata" with the column name "biibEmail". Table 2 is "Intune" with the column name "Email address".