Displaying data resulting from OUTER JOIN

[HeinSwift]

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?

[nilendraFabric · Accepted solution]

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 this

Total 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

[HeinSwift]

Yes that's it! Thank you very much!