Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
Solved! Go to Solution.
hi, @Anonymous
From the screenshot:
1. try the conditional as below for AND part, for example:
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 not
relationships
hi, @Anonymous
From the screenshot:
1. try the conditional as below for AND part, for example:
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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
65 | |
63 | |
52 | |
37 | |
36 |
User | Count |
---|---|
79 | |
67 | |
60 | |
45 | |
45 |