Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
@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:
Best Regards,
Qiuyun Yu
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |