Forum Discussion
Create a Relationship Error
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 |
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.
2 Replies
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.
