Forum Discussion
Calculate and filter using multiple tables
This calculated column formula in the Campaigns table works
=CALCULATE(SUM(Occurences[Duration]),FILTER(Occurences,Occurences[Line]=EARLIER(Campaign[Line])&&Occurences[Product]=EARLIER(Campaign[Product])&&Occurences[Date]>=EARLIER(Campaign[Start date])&&Occurences[Date]<=EARLIER(Campaign[End Date])))Hope this helps.
This calculated column formula in the Campaigns table works
=CALCULATE(SUM(Occurences[Duration]),FILTER(Occurences,Occurences[Line]=EARLIER(Campaign[Line])&&Occurences[Product]=EARLIER(Campaign[Product])&&Occurences[Date]>=EARLIER(Campaign[Start date])&&Occurences[Date]<=EARLIER(Campaign[End Date])))Hope this helps.
Ashish, Thank You!
But I'll need to filter by unique values on a column of the occurences table, how could I do that inside filter? I didn't antecipate that problem, was only focusing in the bigger one.
(explanation: these occurences can happen in one of the 3 parts of the machine, or in 2 of these 3, or in all of them, but it generates an entry for each part anyway) as bellow:
Table 1 of occurrences (image and table):
With the "duplicates" in yellow above.
| Line | Date | Product | Duration | Section | Cavity | Identifier |
| 11 | 04/03/2022 17:05 | 4945 | 5 | 2 | A | 112-44624,7118055556 |
| 11 | 04/03/2022 17:05 | 4945 | 5 | 2 | B | 112-44624,7118055556 |
| 11 | 04/03/2022 18:30 | 4945 | 2 | 3 | A | 113-44624,7708333333 |
| 12 | 04/03/2022 18:30 | 567244 | 3 | 10 | A | 1210-44624,7708333333 |
| 12 | 04/03/2022 18:30 | 567244 | 3 | 10 | B | 1210-44624,7708333333 |
| 12 | 04/03/2022 18:30 | 567244 | 3 | 10 | C | 1210-44624,7708333333 |
| 11 | 04/03/2022 19:00 | 4945 | 5 | 5 | A | 115-44624,7916666667 |
| 13 | 04/03/2022 19:10 | 532277 | 10 | 1 | A | 131-44624,7986111111 |
| 11 | 05/03/2022 06:02 | 5051 | 7 | 3 | A | 113-44625,2513888889 |
| 11 | 05/03/2022 06:02 | 5051 | 7 | 3 | B | 113-44625,2513888889 |
| 13 | 05/03/2022 06:05 | 532277 | 2 | 4 | A | 134-44625,2534722222 |
I can't delete those rows because it's important to know which cavities were affected.
But to sum the duration, I want only the duration for each section.
I used an identifier to count the distinct occurences, but for the sum of duration I don't know how to do it.
Can I use values or distinct in this case?
=CALCULATE(SUM(Occurences[Duration]),FILTER(Occurences,Occurences[Line]=EARLIER(Campaign[Line])&&Occurences[Product]=EARLIER(Campaign[Product])&&Occurences[Date]>=EARLIER(Campaign[Start date])&&Occurences[Date]<=EARLIER(Campaign[End Date])&&Values(Occurrences[Identificator])))
It doesn't seem to work or I don't know how to apply it.
Cannot understand your requirement at all.
I need another filter for unique values in the table occurences, I have an identifier column that I created with concat. (&) on the occurences table.
Can you help me?Had i understood your question, i would have answered it earlier itself.
How may I help you understanding? I don't know how else to explain it 😕
(There is also a language barrier where some information might be getting lost)