How to Filter rows of a table from a table that is not linked with relationship

[Hermann01]

Hi, here the problem, can't solve it despite lots of tries, I am struggling in filtering rows of a table from another one that is not linked.

If anybody can find the solution:

I have 4 tables:
- One is named ProductLocationData (ProductID, Country, CycleDate, LocalAttribute)
- The second is named Sales (ProductID, Country, Month, Sales value).
- Both tables are linked to the two tables: Product (ProductID, GlobalAttribute) by the field ProductID and CountryHierarchy (Country, CountryAttribute) by the field Country

My issue is:
I would need to add a column to the table ProductLocationData that sums the Sales values of the table Sales, which correspond for each row to the same product ID, the same Country and for which ProductLocationData[CycleDate] = Sales[month].

I tried multiple things, using Calculate with Sumx and filter, unfortunately have not been able to filter rows of the table Sales from the table ProductLocationData because they are not linked. And I don't want to link them, if possible.

Any idea?

Many thanks

 

[Hermann01]

ok found it:CALCULATE(SUMX(Sales;Sales[Sales]);Filter( Sales; ProductLocationData[KeyPlans]=Sales[KeyPlans]))
Whre KeyPlans is the combination of Global ProductID + Country + Date