Create a Relationship Error

[guybrit]

Hello,

I'm trying to sum the total points from 2 different data sources. Base points + Bonus points. The issue is some Buyers have base points but no bonus points (they don't show up as 0 if they have no points - they just don't appear in the data), some have no base points but have bonus points, and some have both. I tried creating a relationship between the two sources using the Buyer name, but since not all buyers are on both sources, I get errors. I'm not sure how I can sum the points from the different sources into a new measure. Any help would be appreciated! Thank you.

From Source "A"			From Source "B"			New Measure
Buyer	Base Points		Buyer	Bonus Points		Buyer	Total Points (Base+Bonus)
A	5					A	5
B	6		B	3		B	9
C	4					C	4
			D	2		D	2
E	7		E	2		E	9
F	8					F	8

[v-fenling-msft · Accepted solution]

Thanks for az38's concern about this issue.

Hi, @guybrit 

I am glad to help you. 

According to the problem you described, first you need to connect Power BI to different data source and then open Power Query Editor:

Select one of the tables and click Append Queries as New, then select the corresponding two tables and you will get a new table:

Ctrl+click Base Points and Bonus Points, right click and select Replace value, replace null with 0:

Close&Reply.

New measure in the new table:

Total Points(Base + Bonus) =
SUMX ( AppendTable, AppendTable[Base Points] + AppendTable[Bonus Points] )

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

hi @guybrit 

you should create first dimension table Buyers that consists only of Buyer names from both columns. distinct from union of two tables.

then connect this dimension table to 2 data sources

it is difficult to provide more detailed answer without your data model details