Forum Discussion

Touaibia's avatar
Touaibia
Frequent Visitor
4 years ago

Error message with the calculate function

Hello everyone,

 

I have a date model with :

Sales table 

Product table 

Customer table 

In my company we have 24 products that we are pushing and I want to know how many customer purchased this 24 products.

I have a column in my product table which contains two values " Yes" , "No". Yes, refers to all these strategic products.

 

I tried this approach:   

At first I calculate the this measure     

NB 24 Refs = 

CALCULATE(

                       DISTINCTCOUNT(Sales_Table[IdProduct]), 

                        Product_table = "Yes")

 

With this formula when I set up a table with all my customers I can see how many products of my 24 strategic products each costumer had purchased at least one time. and all is OK

 

Now here my issue, in another page I want to set up a broader vision where each sales rep could see how many of his customers have purchased the 24 products.

 

Soo I tried this formula:   at the first shot I calculated the 24_status : if( [nb 24 refs] = 24 , "On target","Below target")  and it works perfectly.

 

Now the final formula : 

                                           CALCULATE(

                                                               DISTINCTCOUNT(Sales_Table[IdCustomer]),

                                                               [24_status]="On target")

And I get this 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.

 

I’m stuck on this issue and I can't find a solution, any advice or tip to solve this problem?

 

Tnx

 
 
CALCULATE
DAX
error
Filter

7 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    Hi,

    I had a similar issue yesterday on one of my projects. The workaround I came up with was to create an intermediate measure for calculating the status based on my condition and then using a SUMX function to calculate count.

    So in essence:
    [Helper measure] = IF(Sales_Table[24_status]="On target",1,0)
    [Final measure] = SUMX(Sales_Table,[Helper measure])


    Hopefully this helps and if it does consider accepting this post as a solution!

    • Touaibia's avatar
      Touaibia
      Frequent Visitor
      4 years ago

      Unfortunately it doesn't work i have 0 on each row when i put this measure in a table with my sales reps

      • ValtteriN's avatar
        ValtteriN
        Community Champion
        4 years ago

        Okay, a new idea mirroring your original idea: 

        Count of onTarget = COUNTROWS(filter(Target,Target[Column1]="OnTarget"))


        Test data:


        End result:

         

         

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi Touaibia 

    It'd be better if you shared the sample of your data, right now it's not that clear.

    Try this:

    Measure = 
Var _A = filter(Addcolumns(Values(Sales_Table[IdProduct]),"No.24P",calculate(Countrows(Product_table),filter(Product_table,Product_table[Product_table]="Yes"))),[No.24P]=24)
return
COUNTX(_A,[No.24P])

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

    • Touaibia's avatar
      Touaibia
      Frequent Visitor
      4 years ago

      Ok so I have this data model

      Client table with : IdCustomer / SalesRep / Area / CustomerProfil

      ProductTable : IdProduct / ProductName / 24StrategicProduct  

      SalesTable : IdCustomer /IdProduct  / Sales 

       

      1 - I want to calculate for each customer the number of strategic product he had pruchased.

          i did this :  calculate ( distinctcount(SalesTable [IdProduct]), ProductTable[24StrategicProduct]="Yes")

      And it Works, when I set up a table with all my customers i do have the count of my stratgic products each clients had purchased. 

      2- Now I want to set up a table with my CustomerProfil ( wich contains only 3 values A B C ) and add a measure that return the number of customers who have purchased at least one time all my 24 strategic products.

          and i'm stuck in this formula 😞

       

      Tnx