Matrix with measure ignore slicer

[jdusek92]

Hello,

I often get in trouble with slicers working properly on a matrix when I add a certain measure to "values".

Here is a dummy scenario that I made:

Could you please advise what is the best practise to this problem? To make the slicer work again

And also some explanation why is taht happening?

Thank you 

Jakub

[DataZoe]

@jdusek92 IF you change your HasFruit to blank() instead of FALSE(), it should work as expected.  Matrix will show all non blank values when they exisit. FALSE() is a non-blank value. 

[jdusek92]

Hello, thank you.

But what if I still wanted to display a value?

If I add this row to the "Show" table, so that "Marry" has Show=Yes, but has no records in "Items" table?

I still would like to show some values - Zeros for Apples and Oranges and HasFruit to "False/No"

[DataZoe]

@jdusek92 Is this what you were looking for:

I noticed that the TRUE() and FALSE() didn't behave as I thought they would, but when I simply used "True" it did. 

I also took out the BOTH direction relationships:

I've attached my PBIX file for you.

[jdusek92]

Hello,

I would actually like for Marry to display Apples=0, Oranges=0, HasFruid=FALSE

Thank you!

[DataZoe]

Sure @jdusek92, you can add that check in on the fruit counts too and then show 0 if they are to be shown and have no fruit. I've attached that PBIX.

Also, a simpler approach (I've attached this PBIX too) may be to just make one table instead of having three. This will simplify your measures quite a bit!  To do that you would merge the tables in Power Query (Transform Data) using Full joins.

Ultimately ending at this table:

Then your measures are pretty straightforward:

Apples =

COALESCE(

CALCULATE(COUNTROWS(Items), Items[Column1] = "Apple"),

0)

Oranges =

COALESCE(

CALCULATE(COUNTROWS(Items), Items[Column1] = "Orange"),

0)

HasFruit =

IF([Apples] + [Oranges]>0,"True","False")

And you have the functionality you were looking for:

[jdusek92]

Hello, thank you!

yes, I know that this could be solved using one table, but this was just a dummy scenario.

The last thing I would like to know - is there any good article about this behavior - "Matrix ignoring slicer"? I don't even know how to properly name this problem, but I would like to read an article about how to treat it generally.

Thank you

[DataZoe · Accepted solution]

@jdusek92 Ah, that's because this a snowflake schema, not a star schema. There was an excellent article on why these issues happen when you are doing the two dim jump to fact in a slicer, I'll see if I can find it.  It is an older article about power pivot but this one also tackles it on a performance side: Stars and Snowflakes and Bears, Oh My! | P3 Adaptive

But even if you just convert it to a star schema (merge Show with Users), you will no longer have the issues as well.
Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

I was trying to figure out why in DAX it's so important (to your question) and I did also find these articles The importance of star schemas in Power BI - SQLBI, and Understanding DAX Auto-Exist - SQLBI. I hope this helps!