Forum Discussion
Merging 2 different tables
- 2 years ago
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.
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.