Creating new columns from another table based on two criteria

[ddalton]

I have the following tables. Note "..." represents similar data or arbitrary values etc.

TBL1

REF	STG_ID	ID	SMPL_ID	SMPL_NUM	E1	E2	E3	E4	E5	E6	E7
884-A	1	297	111	1	10	1	0.5	0	5	3	8
884-A	1	297	112	2	11	1	0.5	0	5	2	8
884-A	2	297	111	1	10	0.5	0.6	0	3	2	5
884-A	2	297	112	2	...	...	...	...	...	...	...
335-B	1	297	113	3	...	...	...	...	...	...	...
335-B	1	297	114	4	...	...	...	...	...	...	...
350-E	1	272	115	5	...	...	...	...	...	...	...
350-E	1	272	116	6	...	...	...	...	...	...	...
350-E	2	272	115	5	...	...	...	...	...	...	...
...

TBL2

ID	EX	MIN	MAX
297	E1	8	11
297	E2	0.5	1.5
297	E3	0	0.6
...	...	...	...
297	E7	6	7.5
272	E1	8	11
272	E2	0.5	1.5
272	E3	0	0.6
...	...	...	...
272	E7	6	7.5
...	...	...	...

I have created the following relationship between the tables:

TBL1

*:*

TBL2

The relationship currently filters in both directions. 

I would like to create new columns in TBL1 that correspond to the MIN and MAX values of the respective ID and EX in TBL2, i.e., an E1_min, E1_max, E2_min, E2_max column etc. similar to below

REF	STG_ID	ID	SMPL_ID	SMPL_NUM	E1	E1_min	E1_max	E2	E2_min	E2_max	...
884-A	1	297	111	1	10	8	11	1	0.5	1.5	...
884-A	1	297	112	2	11	8	11	1	0.5	1.5	...

The MIN and MAX values should not differ for rows with the same ID so some kind of a lookup for the ID and EX values should be sufficient. 

I've achieved similar things before but only based on a single criteria and using either a LOOKUPVALUE or a CALCULATE with FILTER etc. 

I'd be grateful for any recommendations on how to achieve it. 

[amitchandak · Accepted solution]

@ddalton , unpivot table 2 in power query and then merge

https://radacad.com/pivot-and-unpivot-with-power-bi

merge: https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Or try column in table 1 like

maxx(filter(Table2, Table2[ID] = Table1[ID] && Table2[EX] ="E1") , Table2[Min])

[ddalton]

The MAXX and FILTER expression seems to be doing the trick but I will try a merge later too, and come back with my preferred solution. Regardless, I'll mark your answer as the solution! 

Thanks.