topic How to create a DAX measure that references two different tables in DAX Commands and Tips

How to create a DAX measure that references two different tables

hibarrbm — Tue, 30 Mar 2021 22:15:27 GMT

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!

Re: How to create a DAX measure that references two different tables

hibarrbm — Tue, 30 Mar 2021 22:17:10 GMT

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])))

Re: How to create a DAX measure that references two different tables

Jihwan_Kim — Wed, 31 Mar 2021 07:40:19 GMT

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.

Re: How to create a DAX measure that references two different tables

hibarrbm — Wed, 31 Mar 2021 18:20:50 GMT

It worked! Thanks again for your help!