Using not equal Blanks in DAX not returning correct total

[jeben]

Hello,

I am using this DAX statement for a measure:  

n value = CALCULATE(COUNTROWS('Donor NPS'),FILTER('Donor NPS',[NPS]<>BLANK()))

I then use "n value" as the Value in a matrix, with no rows, but "Year-month" as the columns for the matrix.  Below is a screenshot of the matrix and the values, with August, 2023 circled:

Yet when I go into the table view of the "Donor NPS" table, and filter on "Year-month" = 2023-08, & filter on "NPS" = all values checked except Blanks, it counts 3513 filtered rows.  See screenshot below.  

Why using the same criteria, does the DAX statement return a value of 3475 and filtering the table view counts 3513?  

John

[jeben]

Thanks, I tried your first suggestion with the same result.  The second gave me the error below in display of the visual:  

We have some sensitive data in the application so I don't think I can share the whole pbix (which is 9MB).  Can I share a portion of it with you?  Would that help?  

Any other suggestions?

John

[jeben]

Hi again, 

I tried one other thing, your suggestion to use:  n value1 = CALCULATE(COUNT(Donor NPS[NPS]), Donor NPS[NPS] <> BLANK())   except I took out the filter and now it matches what I had in the data table view.  I'm thinking that the COUNT function maybe doesn't include blanks for a whole number field?

John

[ray_aramburo · Accepted solution]

The COUNT will iterate only on the specified column, while COUNTROWS considers the whole table. That can make the difference to narrow down the problem. Glad it worked for you 🙂 

[gmsamborn]

Hi @jeben 

Maybe try replacing 'Donor NPS'[NPS] <> BLANK() with  NOT ISBLANK( 'Donor NPS'[NPS] )

[ray_aramburo]

Is it possible for you to share your .pbix file? 

In the meantime, other suggestions would be to try the following measures:

n value1 = CALCULATE(COUNT(Donor NPS[NPS]), Donor NPS[NPS] <> BLANK())

or

n value1 = CALCULATE(COUNT(Donor NPS[NPS]), Donor NPS[NPS] <> "")