Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Touaibia
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
VahidDM
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)
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/

 

 

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
Super User
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!





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:

ValtteriN_0-1638956107120.png


End result:

ValtteriN_1-1638956124167.png

 

 





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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors