cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JacobHC
Frequent Visitor

Count how many times a sum measure returns blank

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

1 ACCEPTED SOLUTION
v-polly-msft
Community Support
Community Support

Hi @JacobHC ,

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

vpollymsft_0-1669785759656.png

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.

View solution in original post

4 REPLIES 4
v-polly-msft
Community Support
Community Support

Hi @JacobHC ,

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

vpollymsft_0-1669785759656.png

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.

Hi @v-polly-msft,

 

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
Community Champion
Community Champion

try something like this,

0ProjectCount = 
COUNTROWS( 
    FILTER(
        VALUES('Hours Real'[Project]), 
        [AC No. Hours]=BLANK()
    )
)

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?

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors