Forum Discussion
DAX Help - calculated column multiple related tables
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?