Count Number of scores 0-6 but exclude blanks

[BINewbie1]

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

[AntrikshSharma]

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

[BINewbie1]

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

[AntrikshSharma]

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.

[BINewbie1]

@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

[BINewbie1]

@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

[AntrikshSharma · 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

[BINewbie1]

Thanks @AntrikshSharma Works perfectly!

[BINewbie1]

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.