Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

finding values not zero

Greetings,   This simple DAX formula is eluding me.   I have a report filtered by Supplier, and I wish to count the number of customers who have purchased some of their product in 2017 (Gross > 0...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

=CALCULATE(COUNTA(Data[user_id]),Data[Sales 2017]>0)

 

Does this work?

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Sorry no.

 

Dealer_Count = CALCULATE(COUNTA(gross_sales[user_id]),gross_sales[Sales 2017] > 0)  creates this message:

 

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

 

Thanks for your ongoing assistance.

 

Frank

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    It works absolutely fine.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Greetings again, 

       

      Again thank you for working through this issue with me.

       

      I have isolated the issue down to my use of a measure with a measure, "Sales 2017" is itself a measure "active = CALCULATE(counta(gross_sales[user_id]),gross_sales[Sales 2017]>0)" resulting in the error message, "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed." My data is "gross_sales".

       

      I have replicated your Data table, where I have individual values in the "Sale 2017" column, not a measure. In the Data table your formula works as designed.

       

      I have attached a sample of my report to this reply.

       

      Is there a way to make my measure "Sales 2017" work within this scenario? Alternates?

       

       

      https://e2fe3c147248bbb3-my.sharepoint.com/:f:/g/personal/fklatecki_indeal_org/EsKlQdulUCZEhdUkla6S3g0BBlGbchV6Efe_x9Ka6kmE1Q?e=hlauj3

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        I am now completely confused.  You are now bringing another table.  All this while you were talking about only 1 table.  Now there are two.  Please explain the question very clearly.  Do not be concerned with any formulas.  First explain the business problem clearly.