Forum Discussion

McCow's avatar
McCow
Icon for Resolver III rankResolver III
8 years ago
Solved

COUNTROWS 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 i...
Data Modeling
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
McCow
Icon for Resolver III rankResolver III
8 years ago

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's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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