Way to match exceptions mapping table

[alice11987]

I have 2 tables: 

1. Mapping exceptions - this table contains 4 columns, [Level 02], [Level 03], [Level 04] and [Max Layer].

2. Employee Report - this table contains 3 columns, [Level 02], [Level 03], [Level 04] and I would like to add [Max Layer] column into this table.

Mapping Logic:

    Only the first row need to have all Level matches, the max layer = 9 ;

    For second row, as long as first 2 level matches, the max layer = 9 ;

    For last row, as long as the first level matches, the max layer = 9;

    if it doesn't match, default max layer should be 8.

Any good way to match/merge?

[v-junyant-msft · Accepted solution]

Hi @alice11987 ,

First add an indexed column to the table 'Mapping exceptions':

Then create a new column using the following DAX:

MAX Layer = 
VAR row1_col1 = LOOKUPVALUE('Mapping exceptions'[LEVEL 02], 'Mapping exceptions'[Index], 1)
VAR row1_col2 = LOOKUPVALUE('Mapping exceptions'[LEVEL 03], 'Mapping exceptions'[Index], 1)
VAR row1_col3 = LOOKUPVALUE('Mapping exceptions'[LEVEL 04], 'Mapping exceptions'[Index], 1)

VAR row2_col1 = LOOKUPVALUE('Mapping exceptions'[LEVEL 02], 'Mapping exceptions'[Index], 2)
VAR row2_col2 = LOOKUPVALUE('Mapping exceptions'[LEVEL 03], 'Mapping exceptions'[Index], 2)

VAR row3_col1 = LOOKUPVALUE('Mapping exceptions'[LEVEL 02], 'Mapping exceptions'[Index], 3)

RETURN
IF(
    ('Mapping Logic'[LEVEL 02] = row1_col1 && 'Mapping Logic'[LEVEL 03] = row1_col2 && 'Mapping Logic'[LEVEL 04] = row1_col3) ||
    ('Mapping Logic'[LEVEL 02] = row2_col1 && 'Mapping Logic'[LEVEL 03] = row2_col2) ||
    ('Mapping Logic'[LEVEL 02] = row3_col1),
    9,
    8
)

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