topic Re: Count Number of scores 0-6 but exclude blanks in DAX Commands and Tips

Count Number of scores 0-6 but exclude blanks

BINewbie1 — Fri, 14 Jun 2024 19:00:34 GMT

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

Re: Count Number of scores 0-6 but exclude blanks

AntrikshSharma — Fri, 14 Jun 2024 20:26:00 GMT

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?

Re: Count Number of scores 0-6 but exclude blanks

BINewbie1 — Fri, 14 Jun 2024 20:37:48 GMT

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

Re: Count Number of scores 0-6 but exclude blanks

AntrikshSharma — Fri, 14 Jun 2024 20:42:28 GMT

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.

Re: Count Number of scores 0-6 but exclude blanks

BINewbie1 — Fri, 14 Jun 2024 21:12:05 GMT

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

Re: Count Number of scores 0-6 but exclude blanks

BINewbie1 — Fri, 14 Jun 2024 21:22:14 GMT

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

Re: Count Number of scores 0-6 but exclude blanks

AntrikshSharma — Sat, 15 Jun 2024 06:41:26 GMT

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

Re: Count Number of scores 0-6 but exclude blanks

BINewbie1 — Sat, 15 Jun 2024 08:59:15 GMT

Thanks AntrikshSharma Works perfectly!

Re: Count Number of scores 0-6 but exclude blanks

BINewbie1 — Sat, 15 Jun 2024 10:41:17 GMT

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.