Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to Solution.
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]) ) )
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...
@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?
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.
@klammymaster, well if your bridge table is void of dupl or null values, it should work. here's an example.
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.
@klammymaster, I don't see that you filtered against null values or removed dupl. That would also contribute to the non-unique value error.
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]) ) )
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.
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.
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?
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.
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.
Hi @Phil_Seamark,
Table 1 is "biibuserdata" with the column name "biibEmail". Table 2 is "Intune" with the column name "Email address".
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
107 | |
106 | |
81 | |
37 | |
35 |
User | Count |
---|---|
167 | |
107 | |
72 | |
66 | |
55 |