DAX Help - calculated column multiple related tables

[JamieHornstein]

Hello all, I think what I want to do is possible, but I am not an expert in DAX.

I have 3 tables:

 I am trying to create a new column in the LocationList table called "Dynamic Capacity".  This should be the [Pallets Wide]*[Pallets Deep]*MAX([Item Master][Stacking Factor])).

I don't understand how to use DAX to traverse through two relationships to get to the item master.  LocationList is related to HJInventory by the WHLocation field and HJInventory to Item Master by ItemSite.

My current DAX expression is this:

Dynamic Capacity = LocationList[Pallets Deep]*LocationList[Pallets High]*CALCULATE(max(ItemMaster[stacking_factor]),ItemMaster[ItemSite]=RELATED(HJInventory[ItemSite]))

I get an error that says 'The column 'HJInventory[ItemSite]' either doesn't exist or doesn't have a relationship to any table available in the current context.'

Any ideas?

[JamieHornstein]

I've been experimenting with USERELATIONSHIP and seem to have got closer.

On the LocationList Table, I have created a calculated column:

Dynamic Stacking Factor = CALCULATE(max(ItemMaster[stacking_factor]),USERELATIONSHIP(HJInventory[WHLocation],LocationList[WhLocation]),USERELATIONSHIP(ItemMaster[ItemSite],HJInventory[ItemSite]))

I am trying to nest the relationships, but it's not working.

So, I created two other calculated columns, one on LocationList table, and another on the HJInventoryTable:

Dynamic Stacking Factor2 = CALCULATE(max(HJInventory[Stacking Factor]),USERELATIONSHIP(LocationList[WhLocation],HJInventory[WHLocation]))

Stacking Factor = CALCULATE(max(ItemMaster[stacking_factor]),USERELATIONSHIP(HJInventory[ItemSite],ItemMaster[ItemSite]))

This returns the intended result, but when I try to use either of those in another calculated column on the LocationList table, I get a circular dependency error:

Dynamic Capacity = LocationList[Pallets Deep]*LocationList[Pallets Wide]*LocationList[Dynamic Stacking Factor2]

A circular dependency was detected: LocationList[Dynamic Stacking Factor2], LocationList[Dynamic Capacity],LocationList[Dynamic Stacking Factor 2].

This doesn't make sense to me, because [Dynamic Stacking Factor 2] doesn't appear to me to use the [Dynamic Capacity] function.

Help?