DAX formula includes filtered rows from a page filter

[Anonymous]

I have created an approximation of the model that I am dealing with filled with dummy data. Basically we have the following formula:

DistinctBuyersCountWithBlanks = 
var result = CALCULATE(DISTINCTCOUNT(ShopperProduct[Shopper]), ShopperProduct[Bought] = 1)
RETURN result

This works great; however it returns blank if there are 0 shoppers that satisfy the filters. This is an expected behaviour from the DISTINCTCOUNT function.

If I update that formula this way:

DistinctBuyersCount = 
var result = CALCULATE(DISTINCTCOUNT(ShopperProduct[Shopper]), ShopperProduct[Bought] = 1)
RETURN IF(ISBLANK(result), 0, result)

Now the DAX returns 0s instead of BLANKs. 

But, now the problem is that the DAX would show up data that we have filtered out with a page level filter and that data will have 0. We don't want to show that data at all. The page level filter comes from a different table. You can see the PBIX file that I have created to demonstrate my problem. You can see that the page filter is set to "ProjectName" = "Second". The table on top with blanks shows correct data but with blanks instead of 0, the table on the bottom shows 0s but includes filtered data as well.

https://www.dropbox.com/s/4yghvdxo0i8egts/TestDax.pbix?dl=0

How can I create a DAX formula that returns 0 instead of blank, but doesn't include the whole dataset? 

Thanks.

[v-yuta-msft]

Hi gorgiRankovski,

Based on my test, i'm afraid it's difficult to realize. Could you merge your three tables 'ShopperProduct', 'Project' and 'Product' into one single table? If you can, this thread may be helpful to you: https://community.powerbi.com/t5/Desktop/How-to-replace-blanks-with-0-for-a-measure-when-sliced/td-p....

Regards,

Jimmy Tao

[Anonymous]

Thanks for the reply @v-yuta-msft. Unfortunately it is not possible to merge those tables. The model I'm working with is quite complex and is organized as facts and dimensions, where Product and Project are dimensions and shopper product is a fact.

Let's see if someone else can propose a solution.