Using CALCULATE function on a calculated field - syntax error

[Anonymous]

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.

[Anonymous]

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 notrelationships

[v-lili6-msft · 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

[Anonymous]

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!