Error message with the calculate function

[Touaibia]

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

[VahidDM]

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]

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 

[ValtteriN]

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]

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

[ValtteriN]

Okay, a new idea mirroring your original idea: 

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

Test data:

End result:

[Touaibia]

My "on target / below target" is a measure not a calculated column soo i can't try this solution 😞

[ValtteriN]

In that case your problem should be identical to the one I had yesterday 🤔. Maybe the problem is then with SUMX table. You mentioned that you used a table with "sales rep" maybe the measure works if you change the dax like so:
SUMX('Sales rep',[Helper measure])