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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        I apologize for causing confusion. Per my BI sample, I am working with the “gross_sales”. I am looking to determine the number of Suppliers (manufacturer_id) with purchase made in 2017 by Dealers(user_id), this would determine the number of “Active Dealers”.

         

        I created the measure “Sales 2017” to isolate sales in 2017, and then filter by Supplier, by Dealer, and then counting the number of rows greater than zero.

         

        Best