Measure - Combine data from to tables

[depple]

Hi,

Power BI newbie here. My question is probably pretty basic.

I would like to build a matrix where values from two tables are presented.

The simplied table structure is like this:

"Warehouse Inventory" 
Warehouse|ProductID|Current Stock

"Deliveries Last Period" 
Warehouse|Product ID|Delivered

In the Matrix I would like to present

Warehouse   Product ID   Current Stock    Delivered

I am guessing that a measure for this would be appropriate, but I have not been able to find a solution that I can convert into my use (or understand!).

It would be deeply appreciated if anyone could point me in the right direction.

/depple

[AlB]

Hi @depple

You can do it with a measure but I believe a better solution would be to merge both tables in the query editor or by adding a a calculated column to one of the two tables. If you really want to go the measure way:

1. Set  'Warehouse Inventory'[Warehouse] and 'Warehouse Inventory'[|ProductID] and 'Warehouse Inventory'[Current Stock] in the rows of a matrix visual

2. Set this measure in values of the matrix:

Measure =
LOOKUPVALUE (
    'Deliveries Last Period'[Delivered],
    'Deliveries Last Period'[Warehouse], SELECTEDVALUE ( 'Warehouse Inventory'[Warehouse] ),
    'Deliveries Last Period'[ProductID], SELECTEDVALUE ( 'Warehouse Inventory'[ProductID] )
)

[Stachu · Accepted solution]

looking at your example I would:
1) Create a table that contains all the Warehouses, do join with Inventory and Deliveries on WarehouseIDs

2) create a table that contain all product Ids,, do join with Inventory and Deliveries on Product Ids

3) create a visual with warehouse name from table 1), product from table 2), and deliveries and stock from respective tables

alternatively you could join the two tables and get the data from there