cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saxenaa Frequent Visitor
Frequent Visitor

Power BI 1 to many Relationship gives out wrong results

I have two simple tables

 

image.pngimage.png

 

 

they are linked like this

image.png

 

if I create simple table visualization I see this 

image.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: Power BI 1 to many Relationship gives out wrong results

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

5 REPLIES 5
Phil_Seamark Super Contributor
Super Contributor

Re: Power BI 1 to many Relationship gives out wrong results

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 Smiley Happy

 

show items iwth no data.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

saxenaa Frequent Visitor
Frequent Visitor

Re: Power BI 1 to many Relationship gives out wrong results

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 Super Contributor
Super Contributor

Re: Power BI 1 to many Relationship gives out wrong results

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

saxenaa Frequent Visitor
Frequent Visitor

Re: Power BI 1 to many Relationship gives out wrong results

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

, image.png

saxenaa Frequent Visitor
Frequent Visitor

Re: Power BI 1 to many Relationship gives out wrong results

I overcame this issue using SUMX

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 28 members 911 guests
Please welcome our newest community members: