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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Using CALCULATE function on a calculated field - syntax error

Hi everyone!

 

I'm new to PBI, so sorry if the question is misplaced or something. I'm open to all sugestions.

I'm studying the Microsoft Learn PBI program and trying to build some dashbords for my new team on the go.

 

I'm building a dashboard to analyse the usage level of the printers of the whole company. Thus, we have a table (FT_PRINTJOBS) that registers all the print requests received, which, by the way, is related to other tables, such as users and printers (DM_PRINTERDEVICE).

 

All printers are registered in the table DM_PRINTERDEVICE, which has a calculated column called "alvo_minimo" (minimum_target). This field registers the minimum_target. This informations is the minimum ammount of monthly pages the printer has to print to be considered economically acceptable.

 

I created a table named "tblOcioso" (in excel, and than imported it to PBI) whith the following fields: "idOcioso", "SK_PRINTERDEVICE", "month", "year" and. The table contains all the company printers listed, for the months 6, 7 and 8 of 2019. This way, each printer is listed three times in this table.

 

Then, I created a calculated field in this table, called "ocioso".

 

The idea is to register, for each month, the status of the usage, as bellow:

 

If the printer printed, for instance, at least its minimum_target, in the month nr. 8 of year 2019 (august 2019), the "ocioso" field has to contain the value "ok". On the other side, if it doesn't hit its minimum_target of prints, the field will register "idle".

 

My idea is to use the CALCULATE function to sum all the print jobs of the month, but I'm probably missing something in the syntax I'm using. Here's what I tried (for the sum. If I can make the sum right, I'll just apply an If statement afterwards):

 
ocioso = CALCULATE([CopiasImpressoes]; tblOcioso[SK_PRINTERDEVICE]; AND(MONTH(FT_PRINTJOB[DATEPRINTED]) = tblOcioso[mês]; YEAR(FT_PRINTJOB[DATEPRINTED] = tblOcioso[ano])))

There's an error message that claims: "the expression has several columns, but only one column can be used in an expression true/false used as a table filter".

 

I think it has something to do with the comparsion 

 
FT_PRINTJOB[DATEPRINTED]) = tblOcioso[mês]

inside the formula.

 

So, what am I missing, and how can I get the result I desire? Is CALCULATE the right function?

 

I'll be happy with anny suggestions.

 

Thanks!

P.

1 ACCEPTED SOLUTION

hi, @Anonymous 

From the screenshot:

1. try the conditional as below for AND part, for example:

FILTER('Table',AND('Table'[Month]=8,'Table'[Year]=2013)) instead of AND('Table'[Month]=8,'Table'[Year]=2013)
 
2. there is a bridge table between FT_PRINTJOB[DATEPRINTED] and tblOcioso[mês], so you need to a expression instead of [DATEPRINTED]
 
and if you still have problem, please share your simple sample pbix file.
 
Best Regards,
Lin
Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Here are some prints to make it easier to understand:

 

tblOcioso - table I created - register, for each month, if the printer is considered idle or nottblOcioso - table I created - register, for each month, if the printer is considered idle or notrelationshipsrelationships

hi, @Anonymous 

From the screenshot:

1. try the conditional as below for AND part, for example:

FILTER('Table',AND('Table'[Month]=8,'Table'[Year]=2013)) instead of AND('Table'[Month]=8,'Table'[Year]=2013)
 
2. there is a bridge table between FT_PRINTJOB[DATEPRINTED] and tblOcioso[mês], so you need to a expression instead of [DATEPRINTED]
 
and if you still have problem, please share your simple sample pbix file.
 
Best Regards,
Lin
Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks a lot @v-lili6-msft !

 

I used the FILTER function, as you recommended, and got the expected result!

 

Just for better understanding: One of the arguments of the CALCULATE functions worked well, which was the foreign key ([SK_PRINTERDEVICE]). Does that worked because it was the foreign key? I want to conclude that everytime I have to pass an argument to CALCULATE which is not a key connector in my relationship, I have to use the FILTER function. Is that right?

 

I didn't understand if that's what you meant in your answer:


 
2. there is a bridge table between FT_PRINTJOB[DATEPRINTED] and tblOcioso[mês], so you need to a expression instead of [DATEPRINTED]
 

Thanks dude!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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