cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ttinsley Regular Visitor
Regular Visitor

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. 

 

Cardinality.png

 

 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!

12 REPLIES 12
Super User
Super User

Re: The cardinality you selected isn't valid for this relationship

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Moderator v-qiuyu-msft
Moderator

Re: The cardinality you selected isn't valid for this relationship

Hi @ttinsley,

 

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

 

TotalRows=COUNTROWS('LeaderStandardWork')

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.
blm001 Frequent Visitor
Frequent Visitor

Re: The cardinality you selected isn't valid for this relationship

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 foud that sure enough there was one more counted with count than distinct county (even though I edited the query only for distinct).

 

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

 

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

Lewistrick Frequent Visitor
Frequent Visitor

Re: The cardinality you selected isn't valid for this relationship

@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.

 

Document;Length

doc1;50

doc2;60

doc3;70

doc4;100

 

Document;Classification

doc1;class1

doc1;class2

doc2;class2

doc2;class3

doc4;class1

 

What I want is to plot this:

class1;avg(Length)=75

class2;avg(Length)=60

class3;avg(Length)=60

class4;avg(Length)=NaN

bvach Frequent Visitor
Frequent Visitor

Re: The cardinality you selected isn't valid for this relationship

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

andrewt Regular Visitor
Regular Visitor

Re: The cardinality you selected isn't valid for this relationship

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. 

Anonymous
Not applicable

Re: The cardinality you selected isn't valid for this relationship

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? 

 

Thanks! 

 

S

andrewt Regular Visitor
Regular Visitor

Re: The cardinality you selected isn't valid for this relationship

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

cockadoodledo Occasional Visitor
Occasional Visitor

Re: The cardinality you selected isn't valid for this relationship

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!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 277 members 3,140 guests