Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Using not equal Blanks in DAX not returning correct total

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...
Need Help
  • ray_aramburo's avatar
    ray_aramburo
    2 years ago

    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 🙂 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    ray_aramburo
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
    gmsamborn
    Icon for Super User rankSuper User
    2 years ago

    Hi Anonymous 

     

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