cancel
Showing results 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 =

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

7 REPLIES 7
Super User

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

Try this:

``````Measure =
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.

Frequent Visitor

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

Super User

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!

Proud to be a Super User!

Frequent Visitor

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

Super User

Okay, a new idea mirroring your original idea:

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

Test data:

End result:

Proud to be a Super User!

Frequent Visitor

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

Super User

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

Proud to be a Super User!

Announcements

#### Power BI Community Changes

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

#### Power BI May 2023 Update

Find out more about the May 2023 update.