Forum Discussion

RafaelAri's avatar
RafaelAri
Helper III
2 years ago
Solved

Merging 2 different tables

Hello experts, Attached is an Excel file with an example of what I need: I have 2 data tables, 1) A list of items quantities in different warehouses 2) A list of purchase orders of items, each it...
  • jdbuchanan71's avatar
    2 years ago

    RafaelAri 

    You can create and Items table that will sit above Warehouse and PO and link to them both based on Item No.

    This is the DAX code for the new table.

    Items = 
    DISTINCT(
        UNION ( 
            SUMMARIZE('Warehouse Inventory','Warehouse Inventory'[Item No.],'Warehouse Inventory'[Item Name]),
            SUMMARIZE('PO Balance','PO Balance'[Item No.],'PO Balance'[Item Name])
        )
    )

    Then you link it to the other tables in the model:

    Then a couple of mesures to sum the amounts from Warehouse and PO.

    Qty. in WH's = SUM ( 'Warehouse Inventory'[Qty. in WH] )
    Qty. on Purchase = SUM ( 'PO Balance'[Qty. on Purchase] )

     

    Add the item number and name from the Items table and the meausres to get the counts then set the fitler on the table to [Qty. in WH's] = 0

    I have attached my sample file for you to look at.