Relationships filter out unmapped rows

[p0lskiy]

Hello Community,

My model contains 4 tables as on the screenshot below: 

Each product can be assigned to multiple categories and also some products might not be assined to any category.

In the example below PRODUCT J has sales of 150 and not assigned to any Category. When I build a visual [Category Name - Sales], I can see sales by each category, where PRODUCT J sales are missing and only included in the total line.

Question: how to make visible BLANK category line with sales for unmapped product?

Thank you!

[some_bih]

Hi @p0lskiy on relationship choose Edit Relationship and Single.

Create measure with zero (not best practice but works on small dataset), like

mSalesWithZeroAdd = sum(Sales[SALES])+0

I tried to recreate your datamodel with some dummy names and numbers, adjust to your table names and measures.

The last picture below shows that "product" named 11-Name is with sales 0 .

Did I answer your question? Kudos appreciated / accept solution!

[p0lskiy]

If you change relationship to 'single' as you mentioned you will not be able to see right data as filter context will not be flowing down from category table to sales table. So in this case you will see sales for all products repeated in each category which is wrong.

[some_bih]

Hi @p0lskiy you realy want BOTH type of relationship? How you going to interpret results? Are you ok with it? If yes, please share example with expected output as my example is not close to yours.

[p0lskiy]

Hello @some_bih ,

It would be great to share the pbix file but I can't find the option here.

I do not see anything strange using both directions in relationships. Below is the representation which is pretty logical:

Desired result is to have blank line with reported sales for the products which do not belong to any of the categories. In this case it is product J with 150 sales but it does not belog to any category (missing in table 'ProductToCategory'):

Thanks

[some_bih]

Hi @p0lskiy if you have 365 account share link or via gmail.

Without model, I already show you my approach.

[some_bih]

Hi @p0lskiy did you try to remove PRODUCT_NAME from your second table?

Or try feature, show items no data on link

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data 

[p0lskiy]

Sorry but it did not help:

1. PRODUCT table is required with all other dimensions in the table (PRODUCT NAME is only one as an example).

2. If you try 'show items with no data' it all products but still without sales.