cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Why is many to one relationship actually works as many to zero relationship?

I have two tables.

Table a: a list the vessel ID I am interested in. ID is the unique key

Table b:

unique_idunique key for each row
b_idvesselid 
b_namereport type name
b_cntreport count

 

These two tables has a many to one relationship. Each vessel id in table b can have more than one report.

relationship.png

 

Below I simple pull raw data from a and b for you to have a better understanding of these two tables.

raw data.png

Then in the left table below, I pull out id from both a and b. It is strange that for id in B that does not have a corresponding value in a, they are still shown. In right table below, I do a distinct count of id in a and b. Yes, there are 27 distinct ids in a and 84 in b. But they are joint now. There is a many to one relationship. The row in b that does not have a corresponding value in a should be filtered out. I am expecting both to show 27 in the right table below.

sum.png

 

What is many to one relationship? Many to one means, for each row in table b, there HAS to be 'one and only one' corresponding row in table a. Now the model in POwer BI actually works as a many to zero relationship, not many to one.

 

Also if I want to show only vessels that are in a, I need to add a filter saying 'id in a is not blank'?

sum2.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Why is many to one relationship actually works as many to zero relationship?

@yf569 

 

You may take a look at Relationship evaluation.

 

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

View solution in original post

3 REPLIES 3
Highlighted
Super User IX
Super User IX

Re: Why is many to one relationship actually works as many to zero relationship?

@yf569 , issue No very clear to me.

By Default is right outer. So if B has value A will start showing null, You have to suppress A's null value



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: Why is many to one relationship actually works as many to zero relationship?

I didn't not see any document from Microsoft saying it is right join though...

 

What is not clear? So if B has value, and A does not, but A still shows null, then it is zero to many relationship, not one to many.  

Highlighted
Community Support
Community Support

Re: Why is many to one relationship actually works as many to zero relationship?

@yf569 

 

You may take a look at Relationship evaluation.

 

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors