topic Re: Count how many times a sum measure returns blank in DAX Commands and Tips

Count how many times a sum measure returns blank

Anonymous — Tue, 29 Nov 2022 14:44:19 GMT

Hi,

I have this measure that returns the sum of hours spent on a project.

AC No. Hours = SUM('Hours Real'[QTY])

How do I create a measure that returns the number of projects that have a sum of hours spent that is returned as (blank)?

Re: Count how many times a sum measure returns blank

FreemanZ — Tue, 29 Nov 2022 15:05:19 GMT

try something like this,

0ProjectCount =

COUNTROWS(

FILTER(

VALUES('Hours Real'[Project]),

[AC No. Hours]=BLANK()

)

Re: Count how many times a sum measure returns blank

Anonymous — Tue, 29 Nov 2022 19:19:13 GMT

Hi,

Thank you for the help! It returns only one row, so it is not quite what I am looking for. The AC No. Hours measure sums the QTY so I can have the total QTY for each project ID.

Is there a way to count how many times the AC No. Hours measure returns blank or 0 because there are no QTY values for a given project ID?

Re: Count how many times a sum measure returns blank

Anonymous — Wed, 30 Nov 2022 05:23:03 GMT

Hi Anonymous ,

I have created a simple sample, please refer to it to see if it helps you.

Create a measure.

Measure = CALCULATE(COUNTROWS('Hours Real'), FILTER(ALL('Hours Real'), [AC No. Hours]=0||[AC No. Hours]=BLANK()))

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count how many times a sum measure returns blank

Anonymous — Wed, 30 Nov 2022 10:07:03 GMT

Hi Anonymous,

Great, this seems to do the trick! Do you know if there is a way for the filter on the page to apply to this measure as well? For example if I want to filter on projects in the last 12 months or with a specific person responsible.

Thank you in advance!