COUNTROWS is BLANK

[McCow]

Hello!

How it is possible, that calculated column with such DAX query return blank values?

Qnty = CALCULATE(COUNTROWS('Table');ALLEXCEPT('Table';Table[ID]))

Error samplle is here

And here is the PBIX

[McCow]

@Anonymous, Thanks!
Your are right! It calculated well. It is my mistake, I must be calculate for 'Table (2)', and NOT for 'Table', as in my example. Sorry for inconvenience.

But the mysterious behavior of Power Query connection ist still. It was be tested with both Oracle and ODBC. All of this returns some duplicate rows.
Will test more.

Best regs!

[v-qiuyu-msft]

Hi @McCow,

After looking into your pbix file, I don't find any blank values in calculated column Qnty. The DAX returns 1 or 2 value in the column. 

When you visualize this column data in a card visual, the SUM aggregation is used which calculates 1 and 2, so it returns different value as the right count value. After change SUM to Count, it returns correct value. See: 

Best Regards,
Qiuyun Yu 

[Anonymous · Accepted solution]

It calculates as expected. But it can be confusing if you expect 0 for no records (as in SQL)

Whenever there are no rows to aggregate, the function returns a blank. However, if there are rows, but none of them meet the specified criteria, the function returns 0. Microsoft Excel also returns a zero if no rows are found that meet the conditions.

https://msdn.microsoft.com/en-us/query-bi/dax/countrows-function-dax?f=255&MSPPError=-2147217396