topic Re: Error message with the calculate function in DAX Commands and Tips

Error message with the calculate function

Touaibia — Wed, 08 Dec 2021 08:30:07 GMT

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

Re: Error message with the calculate function

ValtteriN — Wed, 08 Dec 2021 08:54:47 GMT

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!

Re: Error message with the calculate function

Touaibia — Wed, 08 Dec 2021 09:19:12 GMT

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

Re: Error message with the calculate function

ValtteriN — Wed, 08 Dec 2021 09:35:33 GMT

Okay, a new idea mirroring your original idea:

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

Test data:

End result:

Re: Error message with the calculate function

Touaibia — Wed, 08 Dec 2021 10:06:40 GMT

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

Re: Error message with the calculate function

ValtteriN — Wed, 08 Dec 2021 10:16:39 GMT

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

Re: Error message with the calculate function

VahidDM — Wed, 08 Dec 2021 10:30:39 GMT

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/

Re: Error message with the calculate function

Touaibia — Wed, 08 Dec 2021 15:53:05 GMT

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