Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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-rongtiep-msft
Community Support
Community Support

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

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-rongtiep-msft
Community Support
Community Support

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

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.

Anonymous
Not applicable

Hi @v-rongtiep-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
Super User
Super User

try something like this,

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors