User Variable from Table to specify column lookup

[Jabe2015]

Hi All, 

DAX newb here. Spent the better part of the day searching for an answer to this. 

Desired Result:

To create a column in table 1 sourced from table 2 where: 

1. product number matches

2. order date falls inside of the effective dates

3. the correct rate pulled from the correct client column. 

As you may realize there are different rates based on client, product and effective dates. 

Table 1: Client Orders
Order Date	Product Number	Client	New Column Client Rate
2/10/2021	1871	Client 1	750
2/10/2021	1871	Client 2	625
2/10/2021	1871	Client 3	500
5/6/2022	4580	Client 1	2500
5/7/2022	4580	Client 2	2375
5/8/2022	4580	Client 3	2250

Table 2: Client Rates
Effective Start	Effective End	Product Number	Client 1	Client 2	Client 3
1/1/2022	12/31/2022	4580	2500	2375	2250
1/1/2021	12/31/2021	4580	750	625	500
1/1/2022	12/31/2022	1871	2500	2375	2250
1/1/2021	12/31/2021	1871	750	625	500
1/1/2022	12/31/2022	1870	2500	2375	2250

Many thanks in advance for assistance. 

[amitchandak · Accepted solution]

@Jabe2015 , You have to create a column like

maxx(filter(Table2, Table2[Product Number] =Table1[Product Number] && Table1[Order Date] >= Table2[Effective Start] && Table1[Order Date] <= Table2[Effective End] ), Table2[Client1])

or

maxx(filter(Table2, Table2[Product Number] =Table1[Product Number] && Table1[Order Date] >= Table2[Effective Start] && Table1[Order Date] <= Table2[Effective End] ), calculate(Maxx({Table2,[Client1],[Client2], [Client3]} , [Value])) )

[Jabe2015]

Thank you @amitchandak for the prompt response. How would I use client id variable derived from Table 1 to select the correct client column in table 2 to look up the rate? Reason is, new clients will be added frequently, and would like to reduce the need to update code each client.