Forum Discussion

alice11987's avatar
alice11987
Helper I
2 years ago
Solved

Way to match exceptions mapping table

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?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.