Filter many-to-many relationship based on row value in the current table

[jacinto]

I am struggling to create measure to calculate value for each code in Sales table using Exchange table columns. The two tables are many-to-many related, I can't add column or create table because those tables come from diffent composite models. 

What I would like is just to calculate sales value to common currency (EUR) therefore, I have to grap corresponding multiplier for a given currency IF Sales[date] >= Exchange[rate date] AND date <= Exchange[rate date next]

I tried the following but I get error. Thanks in advance

ToEUR =

VAR vCurr =

SELECTEDVALUE( 'Sales'[currency])

VAR vRefDat =SELECTEDVALUE ('Sales'[date])

VAR vExRate =

CALCULATE (

AVERAGE ( 'Exchange'[multiplier]),

FILTER (

'Exchange ',

'Exchange '[currency] = vCurr

&& 'Exchange '[rate date] <= vRefDat

&& 'Exchange '[rate date next] >= vRefDat

)

)

VAR Result = IF(vCurr="EUR", 1, vExchangeRate)

RETURN Result

Sales table
code	date	volume	price	currency
3322	22/2/2016	5	1	EUR
3436	22/1/2017	8	2	USD
1322	22/02/2018	10	3	AUD
9436	12/11/2019	3	2	EUR

Exchange
currency	multiplier	rate date	rate date next
AUD	2,0	22/02/2018	23/02/2018
USD	1,1	22/1/2017	23/1/2017
EUR	1,0	22/2/2016	22/2/2016

[SIH007_1]

How do you expect SELECTEDVALUE( 'Sales'[currency]) to work in a many to many relationship? If you want to use SELECTEDVALUE you have to make sure that the outcome is unique.

[v-zhangti]

Hi, @jacinto 

Where is it wrong? What should your desired outcome look like?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more.

[lbendlin]

Add a composite key "CurrencyMonth" to both tables and link via that key. It will be 1:M from the FX table to the sales table.