Matrix show missing values as zero - pbix attached

[MarkSL]

Hi,

It seems that this is a common question, but even from reading the many posts I am still stuck so I would really appreciate some assistance!

My model has:

2 dim tabes: Date, Products.

2 fact tables: Sales, Purchases.

I create a Matrix with ProductName from Products on the Row and then SalesQty and StockQty from Sales and Purchases as my values:

This all looks ok, except I have blanks for where no sales or purchase were found:

So from reading up on this issue, I see that the ISBLANK() function can be used, but it needs to be on a measure.  So I created a measure over SalesQty:

SalesQtyMeasure = IF(ISBLANK(SUMX(SALES, Sales[SalesQty])), 0, SUMX(SALES, Sales[SalesQty]))

However, when I add this to my Matrix, it changes the blanks to zeros, BUT it also brings in rows where I have no purchase or sales - eg: Oranges.  

The above is purely sample data for the purpose of this demonstation.  For my actual report this is a big problem as it is adding thousands of rows to the matrix which I do not want to see.  But I do need zeros instead of blanks, 1 for the readability of the report but also for 2, I want to do conditional formatting where SalesQty is between 0 and 5 for instance.

The pbix file can be found here:   https://www.dropbox.com/s/fi175a301annhpj/FruitDemo.pbix?dl=0

Any assistance here greatly appreciated!

Mark

[Anonymous · Accepted solution]

Hey @MarkSL,

I added a stockqty measure and a hasdata measure and filtered based on hasdata.

StockQtyMeasure = IF(ISBLANK(SUMX(Purchases, Purchases[StockQty])), 0,SUMX(Purchases, Purchases[StockQty]))

HasData = IF(AND(Sales[SalesQtyMeasure]=0,[StockQtyMeasure]=0),"No","Yes")

[MarkSL]

Hi @Anonymous

That did the trick!  Many thanks, really appreciate it 🙂

Cheers

Mark