How to sum with filter on related columns from multiple tables

[rhoelzle]

Appologies if this has already been covered in another post. I've searched with so many keyword combos, but haven't yet found a post that covers what I'm trying to do. Please link if you think this has already been covered.

In short, I'm trying to create a set of measures which sum a column from one table (TableA) based on a filter applied to a different different table (TableB) which is related to the first through an intermediate metadata table (TableMD). I'll ultimately be doing this same thing on several different sets of interlinked tables, but all with the same basic filtering structure.

I'll lay out one as an example. Here, TableA is timestamped net profits associated with a number of different units, TableB is the timestamped regional market price that those units operate in, and TableMD links units to regions.

TableA

DATETIME	UNIT	NET_PROFIT
28/03/2023 12:00:00 AM	A	390.45
28/03/2023 12:00:00 AM	B	547.87
28/03/2023 12:00:00 AM	C	962.47
28/03/2023 12:00:00 AM	D	303.17
27/03/2023 11:00:00 PM	A	-102.91
27/03/2023 11:00:00 PM	B	707.78
27/03/2023 11:00:00 PM	C	596.35
27/03/2023 11:00:00 PM	D	876.05
27/03/2023 10:00:00 PM	A	313.89
27/03/2023 10:00:00 PM	B	437.14
27/03/2023 10:00:00 PM	C	639.51
27/03/2023 10:00:00 PM	D	516.13
---	---	---
---	---	---
---	---	---
---	---	---
01/01/2015 01:00:00 AM	A	203.12
01/01/2015 01:00:00 AM	B	797.69
01/01/2015 01:00:00 AM	C	228.12
01/01/2015 01:00:00 AM	D	852.00

Table B

DATETIME	REGION	MARKET_PRICE
28/03/2023 12:00:00 AM	R1	-41.08
28/03/2023 12:00:00 AM	R2	148.84
27/03/2023 11:00:00 PM	R1	-40.08
27/03/2023 11:00:00 PM	R2	9.4
27/03/2023 10:00:00 PM	R1	21.88
27/03/2023 10:00:00 PM	R2	40.22
---	---	---
---	---	---
01/01/2015 01:00:00 AM	R1	89.05
01/01/2015 01:00:00 AM	R2	81.05

TableMD

UNIT	REGION
A	R1
B	R2
C	R1
D	R1

In my model, TableA[UNIT] links to TableMD[UNIT], TableB[REGION] links to TableMD[REGION], and TableA[DATETIME] and TableB[DATETIME] both link to an independent Calendar table with DATETIME, DATE, YEAR, etc.

I want to create a measure in TableA that sums NET_PROFIT for all rows (DATETIMEs) where the associated regional MARKET_PRICE in TableB is less than 45 (ie, how well do we perform when the market price is low?).

I'm quite new to power bi and can't work out the syntax, but I'm guessing it's some sort of multilayered filtering like:

Measure =
   CALCULATE(
      SUM(TableA[NET_PROFIT]),
      FILTER(
         TableA,
         FILTER(
            TableMD,
            FILTER(
               TableB, TableB[MARKET_PRICE] < 45
            )
         )
      )
   )

The end goal is to feed these set of measures into a couple of different matrix visualizations. Thanks for any and everythign that helps me work this out!