How to count cases with no data DAX

[Anonymous]

Dear all,

I am having a problem with DAX identifying where there is no data. I can understand why PBI might want to return blanks. But I want 0!

I have a matrix of Months by Fruit and I want to count the number of fruit in each month.

So I calculate Number of Fruit = COUNTROWS(My Table)

Then I create a matrix and I have the columns as months and the rows as fruit. Where there is data I get a count, but where no fruit were sold I just get blanks, where I would prefer 0.

I have tried all varities of

IF(ISBLANK(My Table), 0, COUNTROWS(My Table))

COUNTROWS(My Table) + 0

Can someone tell me what I am missing?

Thank you.

[Anonymous · Accepted solution]

OK. I have found the solution. You get this issue when your dimensions are in your fact table, if you make sure your dimensions are in Dimension tables and select on the dimenions (show items with no data) then you don't have the same problem.

[Greg_Deckler]

Something you might try might be to convert the number to text using FORMAT, including a text "0", that might prevent the matrix from eliminating those rows. But, in general, that just sort of how matrices work.