topic Re: Relationships filter out unmapped rows in DAX Commands and Tips

Relationships filter out unmapped rows

p0lskiy — Fri, 13 Oct 2023 08:50:33 GMT

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!

Re: Relationships filter out unmapped rows

some_bih — Fri, 13 Oct 2023 09:46:15 GMT

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

Re: Relationships filter out unmapped rows

p0lskiy — Fri, 13 Oct 2023 11:46:44 GMT

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.

Re: Relationships filter out unmapped rows

some_bih — Fri, 13 Oct 2023 13:59:37 GMT

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!

Re: Relationships filter out unmapped rows

p0lskiy — Mon, 16 Oct 2023 05:05:06 GMT

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.

Re: Relationships filter out unmapped rows

some_bih — Mon, 16 Oct 2023 09:12:41 GMT

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.

Re: Relationships filter out unmapped rows

p0lskiy — Mon, 16 Oct 2023 09:37:03 GMT

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

Re: Relationships filter out unmapped rows

some_bih — Mon, 16 Oct 2023 12:07:12 GMT

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

Without model, I already show you my approach.