Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I
Resolver I

The cardinality you selected isn't valid for this relationship

I am attempting to create a relationship between two tables.  The trouble is, I have a relationship that should be 1 to 1.  In the SharePoint site from which both tables are pulled, each list is set to "force unique values" on the Leader ID and Employee Name ID fields.  So, there should never be a "Many" scenario. 




 However, when I try to set the relationship to 1 to 1, I get the following error.

Cannot Select 2.png


I even attempted to set the "many" side of this to "remove duplicates (despite the fact that SharePoint should not be allowing duplicates).


What am I missing?  Why would this not be allowed?  Thanks so much for your help!

Advocate I
Advocate I

For anyone stumbling on this. Good practice to do the following for any fields that will provide a key for a 1:1 relationship. In Power Query editor:


1. Transform --> UPPERCASE / lowercase / Capitalize - choose one depending on your context. Power Query is case sensitive (remove duplicates will not identify duplicates with different case), however the link in the data model is not case sensitive (it will identify duplicates with different case as duplicates).


2. Transform --> TRIM. In my case, data is often stored in fixed character strings.


3. Remove Duplicates


4. Filter out (null) values


Even if you do not currently have these issues with your dataset, it is still a good idea for non-static datasets.

Impactful Individual
Impactful Individual

Hi @ttinsley ,

In my case, same issue, I had an empty (Null) record.



Regular Visitor

I had the same problem, but the issue for me was duplication of values. I had removed duplicates, but I didnt realize until later that "remove duplicates" in the query editor is case-sensitive. I was able to work around the issue by applying Text.Lower to the column first. Then when I removed duplicates on that, the duplicates were actually removed and the problem went away. Hope this helps. 

Frequent Visitor


I faced the same issue, I was sure, that I have all values unique, I checked it with the conditional formatting in excel, then I included Remove duplicates in the Power Query Editor, I also created the Group By column in Power Query Editor to display count of values - each row contained 1.

Finally I created the Card visual based on the measures TotalRows and DistinctRows - what a surprise - TotalRows<>DistinctRows!

How to find the duplicate values -> delete rows by rows in Excel and check the cards. After 10 minutes I found the problem - the space character after the string value


space after string.png


It makes of course unique value for conditional formatting, unique value for Power Query Editor, but not for the Edit relationship dialog!

After deleting this space character, the duplicate value appeared everywhere, so by deleting the duplicate row I could create the 1 to many relationship ...

Not applicable

Thanks, Your method sloved my smilar issue.

Regular Visitor

I've been struggling too with this issue. I was trying to establish a 'many to one' relationship but was getting the same error. I found a solution though.

I went to "Edit Queries" and selected the table where the non-duplicated values resided (the 'one' side of the relationship). I right clicked on the column containing the key values and selected "remove errors". Doing this then allowed me to create the 'many to one' relationship.

Strangely though - there were no errors to correct. I checked this using the measures (Count row and Distinct Count) in the comments above and also using excel to confirm there were no duplicates or blanks.

Anyway - this might help someone!

@cockadoodledo - Your solution worked for me as well!  The only difference is that I used "remove errors" and "remove duplicates" on the 'many' side of the relationship.  There were no errors or duplicates in the source table, but Power BI thought otherwise.  🙂 


I see their point that the tool should prevent making a 1 to many relationship when it has already determined there are reasons not to, but it's too strict about making that determination since many of us have had this issue even though the data has no duplicates or errors.  That's why I thought I'd post here and let others know of your fix that is nowhere to be found in the documentation.  Thanks!

@cockadoodledo , removing errors, even with nothing to remove, solved my relationship 1:many. Thanks!

I had a similar issue due to the output csv file from a source system having an additional blank row of null values after the data

Filtering this out at the query stage fixed the issue for me

I could have deleted the row but the outputs were refreshed automatically from the source system on a certain date



Not applicable

I had this issue and at first there was a data problem, but after the data problem was corrected I still could not change the relationship from one to many -> one to one.  I deleted the relationship and recreated it and everything was fine. Seems like a minor Power BI bug. 

Advocate I
Advocate I

I was having this issue as well. Deleting the relationship and recreating it solved it for me.

Advocate I
Advocate I

I have the same issue when trying to use the related function on one table that only had one column.

I verified in Excel with a pivot and by trying to remove duplicates.  There were none.

I took the suggestion from the other post and found that there was one more counted with count than distinct count (even though I edited the query only for distinct).


The difference was a single blank.  I do not know where the blank comes from, but I added a step to the query which was removes the blank.


Then I set my relationship "one to one" and my related function worked. 


Edit: for spelling and grammer.

Not applicable

nice work around, what interim step did you do to kill off the blanks? 


my hunch is that this clunky drag-n-drop / click-n-join alterantive to old fashioned, hand typed SQL is going to end up being a dead end waste of time.  entire companies exist to solve this problem, like Alteryx (NYSE:$AYX).  Even tableau, which has a much better interface than power bi, doesn't really work for drag-n-drop joins as well as a good old fasioned pre-written sql query. 


BUT --- if you can make it work in this interface --- then way  more users in a given company of non-programmers can get to work.  So, what is this work around? 





I used the "replace value". I replaced spaces with nothing. 

Not applicable

The work around is to fix the underlying data (probably via an updated SQL query), delete the relationship, and then recreate the relationship. 

Community Support
Community Support

Hi @ttinsley,


I would suggest you create two measures to double verify whether [LeaderID] values are distinct in LeaderStandardWork table.



DistinctRows= DISTINCTCOUNT('LeaderStandardWork'[LeaderID])


After create those two measures, please place them in two card visuals, if results are different, it means there are duplicate [LeaderID] values in LeaderStandardWork table.


In my opinion, if the [LeaderID] really has duplicate values, we can't set the relationship as 1-1, this could be the reason why the error throws out.


Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Not applicable

Nice job- this solution saved me a lot of time!

@v-qiuyu-msft I don't agree. What should I do if I have one table with Documents/Lengths and one table with Documents/Classifications, and I want a bar chart with average length per classification? A Document can have either none, one or multiple Classifications and each Classification is shown as a row.















What I want is to plot this:





Super User
Super User

That's strange, you wouldn't have some blanks in there or something? The only way that I could replicate this is when I had a blank cell in one of my tables.

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors