Power BI 1 to many Relationship gives out wrong results

[saxenaa]

I have two simple tables

they are linked like this

if I create simple table visualization I see this 

I see that the top row under column Items is an empty record, these records are those which are present in Rate but not in items, which i assume can only happen in right join, where I force records from Rate to be included,

I thought that default relationship is always inner join which should have listed only related ones, so why do I see non-related records from Rate table here? and how to avoid them,

I imported these records to MS access and used the same join and results were perfect, I only get to see the related ones, unless I change the Join to right join then I see the same results what I see here,

what is this diffence.

thanks

[Phil_Seamark]

Hi @saxenaa

The relationships aren't exactly the same as SQL type joins.  In your case if you'd like to get rid of the blank row, you'll need to apply a filter to your visual in either of the Report, Page or Visual level filter.

If you'd like to see the missing values from your Items table you'll need to right click item field in the Values area and turn on the "Show items with no data" option.  You can still achieve what you need, you just have to go about it a slightly different way.  And you have lots of options 🙂

[saxenaa]

the option you are suggesting is show/hide null but related data, 

my concen is toward the row which sum up the unrelated data, 

I would like to understand how the realtionship is being evaluated,any pointer on this would really help

[Phil_Seamark · Accepted solution]

HI @saxenaa

Think of it more as a full outer join that allows you to show all three options

Data on the left hand that has no matching data on the right.

Data on the right that has no matching data on the left

Data that matches on both the left and right (inner join)

You can then use your filtering to decide what you'd like to display.

[saxenaa]

so you are suggestiong that I need to soft hide all what I dont want to see, if I do that and write a measure for Price 

like Price = sum(items[Qty])*SUM(Rate[Rate]) I can get the Price for each item, but Total is wrong as you can see in screenshot below, how can I correct it? I want total to be 31520+60000=91520 but what I see is 50*3576

,

[saxenaa]

I overcame this issue using SUMX