Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ojferreira
Frequent Visitor

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, line and product (I'll call it table 2)

 

I need to sum the amount of time those occurrences lasted by campaing.

 

I tried assigning for each occurrence a code for it's campaing ("line & product code & startdate"). But couldn't make it work, needed to check the line and product combination and if the date of the occurrence was in the interval of the campaign. Another way was to filter by this two conditions directly at the campaign table on a new column.

 

But still, I don't know how it could be done.

Could somebody help me with this problem, pls? 

Thanks!

 

1 ACCEPTED SOLUTION

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

14 REPLIES 14
ojferreira
Frequent Visitor

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:

LineDateProductDuration (Min)Campaign code
114/3/22 17:0549455"some kind of identifier"
114/3/22 18:3049452"ex: 11-4945-03/03/2022 06:00"
124/3/22 18:305672443?
114/3/22 19:0049455?
134/3/22 19:1053227710?
115/3/22 6:0250517?
135/3/22 6:055322772?

 

Table 2 of campaigns:

LineProductStart dateEnd dateSum of duration
11494524/2/22 9:005/3/22 6:00?
1256724426/2/22 8:0015/3/22 6:00?
1353227726/2/22 8:006/3/22 9:00?
1150515/3/22 6:007/3/22 6:00?


You can see in the example a campaign change, but more complex things could happen, 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! 

 

dhruvinushah
Responsive Resident
Responsive Resident

Hi, It seems your second table has the product and line fields. All you need to do is create a relationship between table 1 and table 2 on the common fields like Line or Product. 
Doing so will allow your fields from tablw 2 to be related to table 1. 
After creating the relationship you can create a table with Campaign, Product and Sum of [how long occurences lasted] to get your result. 









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!









Hi,

Share some data and show the expected result. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

ojferreira_0-1653272669185.png

 

Table 2 of campaigns:

ojferreira_1-1653272704397.png


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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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):

ojferreira_0-1653321099066.png

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.

Cannot understand your requirement at all. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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)

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.