topic Re: COUNT/COUNTROWS return 0 instead of blank in DAX Commands and Tips

COUNT/COUNTROWS return 0 instead of blank

afbraga66 — Fri, 15 Jul 2022 09:33:29 GMT

Hello,

I am currently have an issue with count/countrows. When they evaluate and return nothing it shows blank instead of 0.

I have 2 tables, a calendar table and a fact table with 10 rows.

I am trying to calculate the percentage of colum ORIT = "Y".

Lines ORIT2 = 
var _orit = CALCULATE(COUNTROWS(dataVN),dataVN[ORIT]="Y")

return if(ISBLANK(_orit),0,_orit)

The result is this. I don't want those extra lines that mean nothing.

The manufacturing data field comes from the calendar table.

How can I avoid this situation and get the desired result?

The .pbix file is in this link if you want to try - https://we.tl/t-bHGwtdUf5d

Thank you.

André

Re: COUNT/COUNTROWS return 0 instead of blank

tamerj1 — Fri, 15 Jul 2022 10:10:23 GMT

Hi afbraga66
Please try https://www.dropbox.com/t/Fm7RwOsIaqz2Duog

Lines ORIT2 = IF ( NOT ISEMPTY ( dataVN ), COALESCE ( CALCULATE ( COUNTROWS ( dataVN ), dataVN[ORIT] = "Y" ), 0 ) )

Re: COUNT/COUNTROWS return 0 instead of blank

afbraga66 — Fri, 15 Jul 2022 10:28:32 GMT

HI tamerj1 , It works thank you so much! Still, I'm intrigued on what is the role of ISEMPTY() and why without it, it doesn't work. Could you explain, please? Thank you.

Re: COUNT/COUNTROWS return 0 instead of blank

tamerj1 — Fri, 15 Jul 2022 10:33:08 GMT

afbraga66

If the table itself is empty in the current filter context then you need to keep the result blank. You need to have it zero if only the filtered table by CALCULATE is blank. Just pay attention to the difference between the original table and the filtered table.

Re: COUNT/COUNTROWS return 0 instead of blank

vapid128 — Sat, 16 Jul 2022 00:40:39 GMT

CALCULATE(COUNTROWS(dataVN),dataVN[ORIT]="Y") + 0

Re: COUNT/COUNTROWS return 0 instead of blank

admdc01 — Tue, 22 Oct 2024 09:59:46 GMT

This is by far the fastest and cleanest solution so far.

Re: COUNT/COUNTROWS return 0 instead of blank

joaotinti — Sat, 02 Nov 2024 15:48:07 GMT

Genius