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

[yf569]

I have two tables.

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

Table b:

unique_id	unique key for each row
b_id	vesselid
b_name	report type name
b_cnt	report count

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

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

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.

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'?

[amitchandak]

@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

[yf569]

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.  

[v-chuncz-msft · Accepted solution]

@yf569 

You may take a look at Relationship evaluation.