Retrieve a value from a prior level

I have two tables ProdOrder and ProdTotal. The ProdOrder table has ProdKey and ParentKey on it. The Prod Key is the key to the ProdOrder and ProdTotal tables. I need to retrieve a value from the ProdTotal table from the ProdTotal from previous level where the lower level’s ParentKey is equal to current ProdKey and i have not been able to get this to work. This is simple in SQL. Any Ideas?

I have tried this

`Prev Material = CALCULATE(Sum(ProdTotal[Material]), USERELATIONSHIP(ProdOrder[ParentKEY],ProdTotal[ProdKey]))`

Will need some images here. Just too many variables to try and understand. Images of the model, relationships, data column and then the results you are seeing in a table is a good start. Then can iterate through a few examples from there.

ProdCostLowerLevel.pbix (42.5 KB)

Simple example. Thanks.

Ok this is not really where you want to be in your model.

Needs to be changed around here to follow some best practices around data modeling.

Have you had a chance to go through this course yet?

Here’s how I believe the model should be

Then the trick here is to use TREATAS to create a virtual relationship instead of a physical one.

This formula does the trick

``````Prev Material =
VAR ParentKey = SELECTEDVALUE( ProdOrder[ParentKEY] )

RETURN
IF( HASONEVALUE( ProdOrder[ProdKey] ),
SUMX( FILTER( ALL( ProdTotal), ProdTotal[ProdKey] = ParentKey ), [Total Material] ),
CALCULATE( [Total Material], TREATAS( VALUES( ProdOrder[ParentKEY] ), ProdTotal[ProdKey] )))
``````