Forum Discussion
Creating new columns from another table based on two criteria
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.
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-queryOr try column in table 1 like
maxx(filter(Table2, Table2[ID] = Table1[ID] && Table2[EX] ="E1") , Table2[Min])
2 Replies
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-queryOr try column in table 1 like
maxx(filter(Table2, Table2[ID] = Table1[ID] && Table2[EX] ="E1") , Table2[Min])
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.
