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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
heinz_stecher
New Member

Can't create one to one relationship

I have two tables. First table (A) has the email address and other information from people of the organization. Second table (B) has the email address of people who participated in a survey.

 

I want to know the % of people who have participated in this survey. I wanted to the very precise so my idea was to create a new column in table A using the related function. However I can't do this because Power BI has created a Many to One relationship from B to A. 

 

What is going on here? Why can't I alter the relationship? I KNOW both tables have unique email addresses because I even removed duplicates just to be sure.

 

My data model only consists of these two tables.

 

Please help, out of ideas.

1 ACCEPTED SOLUTION
rommel20
Resolver I
Resolver I

1. Merge Queries (Table1 left outer to Table 2 via email)

2.  Create measure Count all record and Count all record with  Table(2) email2 has data

View solution in original post

6 REPLIES 6
bjh497
Frequent Visitor

I also can't create a one-to-one relationship between tables with a simple key (PRC Name) which means I can't use "Related" to reference data between them.

Also, because the tables are not from uploaded data (they are created using Summarize) I also can't merge them.

This is driving me crazy, as I need to combine this data from different tables.

This is the code to create one of the tables. It generates a row with a blank/null PRC Name, which is all I can think of is preventing a one-to-one relationship.

 

Capture.PNG

bjh497
Frequent Visitor

Actually, yes, denoting the null PRC Names as "No PRC" allowed a one-to-one relationship.

 

Probably Database 101.... if I'd done the course!

rommel20
Resolver I
Resolver I

1. Merge Queries (Table1 left outer to Table 2 via email)

2.  Create measure Count all record and Count all record with  Table(2) email2 has data

Hey Rommel, thank your for your response!

 

This actually works as an easy fix. I must note that this still doesn't resolve the mystery of why I couldn't create the one to one relationship, but it's a practical fix and the one I'm going with since I can't waste any more time on this matter.

 

Regards!

Anonymous
Not applicable

1. Try create a measure (not a column):

Survey_PCT = DIVIDE(COUNTROWS(B),COUNTROWS(A))

It should give you the answer

2. If you still want to change the relationship to One-to-One then go to "Manage Relationships" --> choose the relevant relationship and click "Edit"   ---> under Cardinality you should see "One-to-One"

 

Michael

Hi Michael, thank you for your input.

 

The reason why I didn't created a measure is because, besides knowing my % of participation, I also want to know who hasn't participated. that's why I need the column and can't deal just in absolutes.

 

I have already tried changing the relationship manually and the program refuses due to a cardinality issue. It says cardinality isn't valid. This is where I'm confused, since both tables have unique key values (email address).

 

I'm don't think it's relevant but one table has more rows than the other. A > B

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.