Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

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 = 



                        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 : 



                                                               [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?



Super User
Super User

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)


If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!



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 😞




Super User
Super User


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!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

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

Okay, a new idea mirroring your original idea: 

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

Test data:


End result:




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

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

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helpful resources

Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors