Skip to content
Advertisement

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

enter image description here

Table B

enter image description here

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

Expected Output:

enter image description here

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!!

Advertisement

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;
2 People found this is helpful
Advertisement