Lookup value based on slicer

[tim10]

Hello,

I have 2 tables with a relationship as marked below with a red line. The green table has some special exchange rates. The second table has exchange rates by month. 

ExchRate is simply a column with the formula =RELATED('Green Table' [ERspecial]).

Basically, for each ERYear in table 2, I am attaching this special Exchange Rate called ERSpecial.

No problem so far

A new ask has been introduced:

a) Add a slicer with values 2022 and 2023

b) If the user selects 2023 in the slicer, attach ERspecial with ERyear 2023 to every single record in table 2.

c) If the user selects 2022 in the slicer, attach ERspecial with ERyear 2022 to every single record in table 1.

I am struggling to make this work. Could you please guide me? Ideally, i want this to be a column. Thank you

[amitchandak]

@tim10 , Create a measure

maxx(filter( Rate, Rate[COde] = max(Table[Code])  && Rate[ERYear] = [selected Year]) , [ERSpecial])

[tim10]

Thank you for your reply. I did what you said but it only returns values for the selected year in the slicer. The other years are filtered out. Please see below. I am expecting that yearly exchange rate to be attached to every single row in the monthly exchange rate table, based on the slicer selection.

Basically, if selected value in the slicer is 2023, every single row in the monthly exchange rate should get the ERspecial for 2023 and so on. have a nice day