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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BINewbie1
Helper I
Helper I

Count Number of scores 0-6 but exclude blanks

Hello,

 

This should be simple but I'm struggling to understand what's going on.

 

I have a table, 'new_clientsurveys' with a field [bbbc_rategeneralfeelingofwellbeing_display] which contains whole numbers in the range 0-6, and a number of blank values.

 

I have a measure, that i'm using to count the number of valid survey responses for this survey question. I need this measure to count the number of surveys just excluding the blank values, but also counting the zeros. The measure also filters where the response is part of an initial survey.

 

No. Valid Initial Surveys =

VAR _ValidInitialSurveys = CALCULATETABLE('new_clientsurveys', 'new_clientsurveys'[new_of_master_outcome_selector_display] = "Initial Assessment",'new_clientsurveys'[bbbc_rategeneralfeelingofwellbeing_display] <> Blank ())

RETURN
CountRows(_ValidInitialSurveys)
 
It appears that the measure, as well as excluding the blank values, is also excluding the 0 values. I need the measure to count the 0 values.
 
I tried modifying the measure using 'new_clientsurveys'[bbbc_rategeneralfeelingofwellbeing_display] >=0, but this variation counts the blank values, which I need to exclude.
 
Any thoughts. Happy to upload a data model if needed, but I think its hopefully straightforward for someone with more experience.
 
I don't really understand why <> Blank would also remove the zeros/ 0s.
 
Any help appreciated. I messed up because when I built some test measures for this project I hadn't realised that the data included zeros and I didn't include them in my test data. Otherwise I would have caught on to this problem earlier. All good learning I guess.
 
Thanks,
 
Adam
1 ACCEPTED SOLUTION

@BINewbie1 Yeah when <> BLANK is used it is filtering out both NULL/BLANK & 0 so you can just use 

NOT ISBLANK ( 'new_clientsurveys'[bbbc_rategeneralfeelingofwellbeing_display] )

When NOT ISBLANK is used it only filters Nulls/Blanks

View solution in original post

8 REPLIES 8
AntrikshSharma
Community Champion
Community Champion

@BINewbie1 In the UI if you filter 'new_clientsurveys'[new_of_master_outcome_selector_display] = "Initial Assessment" does the column 'new_clientsurveys'[bbbc_rategeneralfeelingofwellbeing_display] show any row with 0?

Hi @AntrikshSharma , thanks for engaging.

 

You mean if i apply the filter in the Table View? Yes, if I filter by "Initial Assessment" on [new_of_master_outcome_selector_display], and then apply another filter by '0' on the [bbbc_rategeneralfeelingofwellbeing_display] then the row count shows up as 32 filtered rows. 

 

There's also 287 rows with Blank values which I don't want to count.

 

These are the surveys that the measure is currently missing.

 

Thanks,


Adam

Then it looks like there are other active filters in the report due to which 0s aren't counted, in your CALCULATETABLE add REMOVEFILTERS ( 'new_clientsurveys' ) to see if it includes 0s.

@AntrikshSharma Thanks for the suggestion. I've tried that and still getting the same results.

I did, REMOVEFILTERS(new_clientsurveys[bbbc_rategeneralfeelingofwellbeing_display]), as if I remove filters on the whole table then my table visualisation goes all wrong - but still with the wrong number being repeated across my visualisation.

 

I've checked all my filters and can't see anything that would cause this in terms of the filters in the visualisations.

 

I'm pretty sure that using the <> operator and Blank, removes zeros as well as Blanks. There's a suggestion at the link below that when counting Blanks to use a == operator for 'strictly equal to', so that zeros and Blank values are treated differently. But I need the negative of this - 'not strictly equal to'. Any ideas?

 

https://dax.guide/op/not-equal-to/ 

 

Thanks,

 

Adam

 

@AntrikshSharma 

The following DAX gives the right result:

 

No. Valid Initial Surveys (Test) =

VAR _ValidInitialSurveys = CALCULATETABLE('new_clientsurveys', 'new_clientsurveys'[new_of_master_outcome_selector_display] = "Initial Assessment", new_clientsurveys[bbbc_rategeneralfeelingofwellbeing_display] == 0 || new_clientsurveys[bbbc_rategeneralfeelingofwellbeing_display] = 1 || new_clientsurveys[bbbc_rategeneralfeelingofwellbeing_display] = 2 || new_clientsurveys[bbbc_rategeneralfeelingofwellbeing_display] = 3 || new_clientsurveys[bbbc_rategeneralfeelingofwellbeing_display] = 4 || new_clientsurveys[bbbc_rategeneralfeelingofwellbeing_display] = 5 || new_clientsurveys[bbbc_rategeneralfeelingofwellbeing_display] = 6)

RETURN
COUNTROWS(_ValidInitialSurveys)
 
But seems less than efficient?
 
Adam

@BINewbie1 Yeah when <> BLANK is used it is filtering out both NULL/BLANK & 0 so you can just use 

NOT ISBLANK ( 'new_clientsurveys'[bbbc_rategeneralfeelingofwellbeing_display] )

When NOT ISBLANK is used it only filters Nulls/Blanks

Thanks @AntrikshSharma Works perfectly!

Just to add for anyone else that's affected by this.

 

The issue with <> Blank() removing 0 as well as blanks only seems to happen when the field format is whole number. I've some similar question scales that are formated as text because they include a n/a  i.e. 0 - 4 + n/a. Here the <> Blank () seems to correctly remove the blanks leaving the 0.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.