Dax multiply column from differents tables

[ndris]

Hello please, I need your help. I have two tables: CashFlow and ExchangesRates
The relationship between the tables is many to many cashflow(exchangeKey) : exchangerates(exchangeKey)   ;(I know it's not recommended so if you have a tip to suggest don't hesitate).

Cashflow

Amount

Opertaiondate

Source_currency

exchangeKey(Opertaiondate_Source_currency)

ExchangeRates

Date

Source_currency

Targetcurrency

Rates

exchangeKey(Date_Source_currency)

i have create this following measure but it put many time 

measure=SUMX(

SUMMARIZE(

CashFlows

, OperationDate[Date]

, CashFlows[Currency]

, "CommitmentCCY"

, CALCULATE(

SUM(CashFlows[Commitment]) * SUM(ExchangeRates[Rates])

, CashFlows[OperationDate] <= SELECTEDVALUE(ReportingDate[ReportingDate])

, CashFlows[IsInvestorCurrency] = TRUE()

)

)

,[CommitmentCCY]

)

I want from my cashflow table to create a measure that will make me calculate for each operationdate multiply amount by the rates(of corresponding exchangeRates, depending on the day and also the targetcurrency selected in my slicer) . there are only 3 targetcurrency (Eur,USD,CHF) and there are several source_currency (AED,EUR,USD,CAD,COP,DEM) .
I could have a solution please?
Thanks in advance

[amitchandak · Accepted solution]

@ndris , A better idea would be have a column or use SUMX and force calculation in expression like a column

example

sumx(CashFlows, CashFlows[Commitment] * maxx(filter( ExchangeRates, ExchangeRates[Date] = CashFlows[Date] && ExchangeRates[Currency] = CashFlows[Currency]  ) , ExchangeRates[Rates] )

Add another filter as per need.

But you should create a column, as this will slow down