cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ndris
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?
Thanks in advance

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
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

View solution in original post

1 REPLY 1
amitchandak
Super User
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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.