Forum Discussion
New column calc - based on a parent record value
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
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 AddColumnthis 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
5 Replies
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?
Jimmy801Community Champion
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 AddColumnthis 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