How to create a DAX measure that references two different tables

[hibarrbm]

I have a transaction fact table with just Date, Sales, and Units by WIC (product ID). Then I have a second dimension product table that contains item cost for each WIC. I want to create a measure that will lookup the item cost from the DIM table for each WIC in the Fact table without having to Merge or create a calculated column. 

When I merge the two tables and bring in item cost from the DIM table and create a sumx measure to calculate units * item cost, the totals are correct. However, I want to avoid merging the tables due to memory usage. Below is a screenshot of both results. Total Cost is using the merge table then the sumx measure, however, I can't get the same results without merging the tables. The results are almost double (column "Total Prod Cost $). Please help!

[Jihwan_Kim · Accepted solution]

Hi, @hibarrbm 

If you have the model like below, please try similar to the below measure ( try to use RELATED function in the measure).

Costs =

SUMX(FactSales, FactSales[Unit] * RELATED( DimProducts[UnitCost]))

The link down below is the sample pbix file that I created for this question.

https://www.dropbox.com/s/deubgncq1s8t1yw/sample%200331.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[hibarrbm]

It worked! Thanks again for your help!

[hibarrbm]

This is the measure i tried using to avoid merging the tables 

Total Prod Cost $ = CALCULATE(SUMX(Fact_Fulfilled_Sales_MarketplaceODD, Fact_Fulfilled_Sales_MarketplaceODD[UNITS] * AVERAGE(DIM_Product[Average Cost])))