cancel
Showing results for
Did you mean:
Frequent Visitor

## Dax multiply column from differents tables

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?

1 ACCEPTED SOLUTION
Super User

@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

Super User

@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

Announcements