Forum Discussion

ojferreira's avatar
ojferreira
Frequent Visitor
4 years ago
Solved

Calculate and filter using multiple tables

Hello,   I have two data tables, one (I'll call it table 1) for occurrences by date, product, production line, and how long it lasted. And another one with the campaign with start and end date, lin...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

Share the 2 tables in a format that can be pasted in an MS Excel file.

ojferreira's avatar
ojferreira
Frequent Visitor
4 years ago

Here.

Table 1 of occurrences:

LineDateProductDuration
1104/03/2022 17:0549455
1104/03/2022 18:3049452
1204/03/2022 18:305672443
1104/03/2022 19:0049455
1304/03/2022 19:1053227710
1105/03/2022 06:0250517
1305/03/2022 06:055322772

 

Table 2 of campaigns:

LineProductStart dateEnd DateSum of duration
11494524/02/202205/03/2022 
1256724426/02/202215/03/2022 
1353227726/02/202206/03/2022 
11505105/03/202207/03/2022 
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.

    • ojferreira's avatar
      ojferreira
      Frequent Visitor
      4 years ago

      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.


      LineDateProductDurationSectionCavityIdentifier
      1104/03/2022 17:05494552A112-44624,7118055556
      1104/03/2022 17:05494552B112-44624,7118055556
      1104/03/2022 18:30494523A113-44624,7708333333
      1204/03/2022 18:30567244310A1210-44624,7708333333
      1204/03/2022 18:30567244310B1210-44624,7708333333
      1204/03/2022 18:30567244310C1210-44624,7708333333
      1104/03/2022 19:00494555A115-44624,7916666667
      1304/03/2022 19:10532277101A131-44624,7986111111
      1105/03/2022 06:02505173A113-44625,2513888889
      1105/03/2022 06:02505173B113-44625,2513888889
      1305/03/2022 06:0553227724A134-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.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        4 years ago

        Cannot understand your requirement at all.