Forum Discussion
Displaying data resulting from OUTER JOIN
I created a semantic model with a relationship between two tables:
Sales
| sale_id | product_id | amount |
| 1 | 1 | 3 |
| 2 | 1 | 4 |
| 3 | 2 | 5 |
Products
| product_id | name |
| 1 | apple |
| 2 | banana |
| 3 | avocado |
The relationship connects both tables via the column product_id.
When displaying the total amount sold for each product name, PowerBI will only display
| name | sum(amount) |
| apple | 7 |
| banana | 5 |
leaving out the avocado. I would like to display the avocado as well with an amount of 0. Kind of an OUTER JOIN or LEFT JOIN instead of INNER JOIN.
How can I achieve that using the Fabric Semantic Model and PowerBI?
Hello HeinSwift
Please give it a try.
Use the Productsname column in your visual, not from Sales.
Right-click the field and pick “Show items with no data”.
create a measure like thisTotal Amount = COALESCE(SUM(Sales[amount]), 0)
for reference check this video:https://m.youtube.com/watch?v=nTiAaY7waMU
Please accept the answer if this is helpful.Thanks
2 Replies
Hello HeinSwift
Please give it a try.
Use the Productsname column in your visual, not from Sales.
Right-click the field and pick “Show items with no data”.
create a measure like thisTotal Amount = COALESCE(SUM(Sales[amount]), 0)
for reference check this video:https://m.youtube.com/watch?v=nTiAaY7waMU
Please accept the answer if this is helpful.Thanks
Yes that's it! Thank you very much!
