Using value from a parent row in a measure

[Anonymous]

Hi All,

I'm a complete noob with DAX (come from SQL background) so apologies if my query is a bit basic.

I have a bunch of rows to store quantities by date. I also have a header row for these rows to inherit the unit price from

I'm trying to create a calculated column which multiplies QTY * (unit_price where ID = parent_id). If parent ID is blank the calculated column should be blank

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

Column = 
VAR UnitPrice_ =
    LOOKUPVALUE ( 'Table'[Unit Price], 'Table'[Id], 'Table'[Parent Id] )
RETURN
    [Qty] * UnitPrice_

Measure = 
VAR UnitPrice_ =
    LOOKUPVALUE (
        'Table'[Unit Price],
        'Table'[Id], SELECTEDVALUE ( 'Table'[Parent Id] )
    )
RETURN
    SUM ( 'Table'[Qty] ) * UnitPrice_

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Anybody have some other suggestions? The suggested answer is not working 

Or should I be looking for a different solution?

[amitchandak]

@Anonymous , Create a new column like

new column =
var _1 = [parent_id]
return
maxx(filter(Table, [Id] =_1),[Unit Price]) * [qty]

[Anonymous]

Hi,

Thank you very much for the response @amitchandak .

That query fails for me, I can't create the VAR as parent_id as it's not a unique number. If I add a bit more of my dummy data you see I have multiple parent rows spread all throughout the data set