Map values based on many to many relationship using DAX

I have a situation where I need to map supplier values from one table to other based on the combination of part and plant ID. The tables look like below-

Table A

Table B

Now based on Plant and Part Number ID I need to map the supplier values from Table A to Table B.

Expected Output:

I am trying to solve it in DAX as these both the tables are not base tables, they are derived as summarized tables. So taking help of Power Query won’t come into the picture.

I have not tried this in SQL till now, but if anyone can help me out with the solution with SQL too, that’d completely do.

Thanks in advance!!

Answer

If I understand correctly, you want direct matches. Then when things don’t match, you want to “align” the remaining rows. I would suggest using union all for this purpose:

select a.plant, a.partnumber, a.supplier, b.supplier
from a join
     b
     on a.plant = b.plant and
        a.partnumber = b.partnumber and
        a.supplier = b.supplier
union all
select plant, partnumber, max(supplier), max(supplier_b)
from ((select a.plant, a.partnumber, a.supplier, null as supplier_b
              row_number() over (partition by plant, partnumber order by supplier) as seqnum
       from a
       where not exists (select 1
                         from b
                         where a.plant = b.plant and
                               a.partnumber = b.partnumber and
                               a.supplier = b.supplier
                        )
      ) union all
      (select b.plant, b.partnumber, null, b.supplier,
              row_number() over (partition by plant, partnumber order by supplier) as seqnum
       from b
       where not exists (select 1
                         from a
                         where a.plant = b.plant and
                               a.partnumber = b.partnumber and
                               a.supplier = b.supplier
                        )
       )
      ) ab
group by plant, partnumber, seqnum;