Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
McCow
Resolver III
Resolver III

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 is the PBIX

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
McCow
Resolver III
Resolver III

@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!

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: 

 

q2.PNGq3.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.