Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

DAX formula includes filtered rows from a page filter

 

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.

2 Replies