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, 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

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 🙂 

5 Replies

  • ray_aramburo's avatar
    ray_aramburo
    Icon for Super User rankSuper User
    2 years ago

    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] <> "")

     

  • 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 🙂