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.
Here are some examples of the tables, can't provide exactly the data, but these examples are similars.
I expect to insert the campaign code (some kind of identifier as the example bellow) in a new column on table 1, this way I can then add the durations to the campaigns. And even do more connections after.
OR
Create a new column on table 2 already adding the duration of occurrences from table 1 of that campaign.
Dates in the format - dd/mm/yy hh:mm
Table 1 of occurrences:
Table 2 of campaigns:
You can see in the example a campaign change, but other things could happen that should be considered, products being made ate different lines, even at the same time. This is why I think that a identifier with the combination of #LINE, #product and #startdate will be used...
I hope this make my problem clearer, it's my first time posting here.
Thanks for your time and help!
Hi,
Share the 2 tables in a format that can be pasted in an MS Excel file.
Here.
Table 1 of occurrences:Line Date Product Duration 11 04/03/2022 17:05 4945 5 11 04/03/2022 18:30 4945 2 12 04/03/2022 18:30 567244 3 11 04/03/2022 19:00 4945 5 13 04/03/2022 19:10 532277 10 11 05/03/2022 06:02 5051 7 13 05/03/2022 06:05 532277 2 Table 2 of campaigns:
Line Product Start date End Date Sum of duration 11 4945 24/02/2022 05/03/2022 12 567244 26/02/2022 15/03/2022 13 532277 26/02/2022 06/03/2022 11 5051 05/03/2022 07/03/2022 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.