New column calc - based on a parent record value

[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 of the referenced parent row). If parent ID is blank the calculated column should be blank

[Anonymous]

If you're doing this on Power Query try the following:
1.  Select the Unit Price columns and go to "Transform" then "Fill down"

2. Create a custom column and input =if [Parent Id] = null then null else [Qty]*[Price]

[Anonymous]

Thanks @Anonymous 

Wouldn't that only work if my data was neatly grouped by parent ID and in a convenient order for me? Fill down would give everything the value of the top row wouldn't it?

My actual data is 5000 rows and the parent rows are going to be spread throughout the data, not at the top and not necessarily anywhere near the child records that need to inherit the unit price

For instance, if I jumble up the dummy data to be more representative. With 2 parents at the top and the child records in a non consecutive order underneath there would be no way to use fill down?

[Jimmy801 · Accepted solution]

Hello @Anonymous 

you can add a new colum using this formula. Be aware that for this exact solution the ID-column needs to be maintained as primary key

        (add)=> 
        let 
            GetPrice = AddKey{[ID= add[Parent ID]]}[Unit Price]
        in
            if add[Qty]= null then null else add[Qty]*GetPrice

.  Here the complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIiI1MQHasTrWQEETCECxgDGQY65qZQcbCYCRYxUxQxI6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Qty = _t, #"Unit Price" = _t, #"Parent ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Qty", type number}, {"Unit Price", Int64.Type}, {"Parent ID", Int64.Type}}),
    AddKey = Table.AddKey(#"Changed Type", {"ID"}, true),
    AddColumn = Table.AddColumn
    (
        AddKey,
        "Calculated",
        (add)=> 
        let 
            GetPrice = AddKey{[ID= add[Parent ID]]}[Unit Price]
        in
            if add[Qty]= null then null else add[Qty]*GetPrice 

    )
in
    AddColumn

this is the outcome

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Thanks @Jimmy801 

That looks like the correct result. I'm not sure where to put it though, copy / pasting into the add column dialog doesn't work for me. ("The name 'AddKey' wasn't recognised")

ID is already the PK in the database so will definately be unique

[Jimmy801]

Hello @Anonymous 

you have to substitute AddKey with your previous step. However to make this formula work you have to adapt also you column names (replay Qty with QTY) and you have to apply to the columns ID the primary key, otherwise the reference I used in this formula wont work

BR

Jimmy