Retrieve a value from a prior level

DAX DAX Calculations
1

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

2

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.

3

ProdCostLowerLevel.pbix (42.5 KB)

Simple example. Thanks.

4

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

image
image694×389 6.36 KB

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

image

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

image
image835×423 15.6 KB

Check out this video on TREATAS for more info