Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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?
Thanks in advance

 

 

  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , 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

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , 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