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.
I have a variety of products, 14 lines and the campaigns last only some days, therefore, I have many entries for campaigns, and some products repeat themselfs on the same line duing the year. Therefore, I needed something like I explained:
- Relate the occurence to a campaign using the variables -> line, product and start date. (Pulling data from table 2 into table 1)
or
- Relate the campaign to multiple occurrences and sum the duration value given in table 1, using the variables -> line, product and the occurrence date, that should be recognized as between start and end date of the campaign (Pulling data from table 1 to table 2)
I have line and product on both tables, on table 1 I have the occurence date and duration; and on table 2 I have the start and end of the campaign.
Don't know if I made my problem clear or If any other information could help.
Hi ojferreira ,
I think I understand your issue. Have you tried to create a column to act as an identifier?
You can CONCAT(Line,product) on each table and use that as a key to join your tables. If needed you can also concat your sections or other fields as well that you need to create one column of all information.
Then you can use that newly created field to act as a field for relationship between the two tables. This will help you relate to multiple fields per your requirement.
If this doesn't help, can you share a screenshot of what your ideal result would look like?
Thanks!