Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

How to count cases with no data DAX

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.

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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.