Count how many times a sum measure returns blank

[Anonymous]

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)?

[Anonymous · Accepted solution]

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.

[Anonymous]

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!

[FreemanZ]

try something like this,

0ProjectCount = 

COUNTROWS( 

    FILTER(

        VALUES('Hours Real'[Project]), 

        [AC No. Hours]=BLANK()

    )

)

[Anonymous]

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?