Forum Discussion
Resolver IIICOUNTROWS is BLANK
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
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
3 Replies
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
- 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-msftCommunity Support
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
