Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ivandgreat
Helper II
Helper II

Tree Calculation/Aggregated Values Matrix

im struggling to create a table parent and child tree relationship, i have a parent and child table, i wanted to calculate the values with respect to their root parents like a family tree

 

ChildParentChild Value
Site1Site210
Site2Site320
Site3Site415
Site5Site45
Site4Site620
Site6 (No Parent or Parent column is null value)


The output should be coming from the Parent Column for column Child item

 

ChildParentChild ValueTree RelationCalculated Values
Site1Site210Site1,Site2,Site3,Site4,Site610+20+15+20+0(null)=65
Site2Site320Site2,Site3,Site4,Site620+15+20+0(null)=55
Site3Site415Site3,Site4,Site615+20+0(null)=35
Site5Site45Site5,Site4,Site65+20+0(null) =25
Site4Site620Site4,Site620+0(null) =20
Site6  Site60(null)=0


I don't know if possible to create this using DAX or m function . Thanks in advance

1 ACCEPTED SOLUTION

Tree Relation=CONCATENATEX(FILTER(Data,PATHCONTAINS(PATH(Data[Child],Data[Parent]),EARLIER(Data[Child]))),Data[Child],"|")

Calculated Values formula keeps no change.

View solution in original post

8 REPLIES 8
dufoq3
Super User
Super User

Hi @ivandgreat, I finaly did it in Power Query, but it took me more than an hour... (I'm glad for this experience).

Both versions will work only if there are no duplicates in [Child] column.

 

Result:

dufoq3_0-1709419655986.png

 

v1 (slower)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs4sSTVU0gHTRkDa0EApVicazgXRxkDaCCFsDBU2Aak2hQubIgkjRE2gomaoZoC4IBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Child = _t, Parent = _t, #"Child Value" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Child Value", type number}}),
    // This step is also mandantory!
    RenamedColumns1 = Table.RenameColumns(ChangedType,{{"Parent", "Parent1"}}),
    Buffer = Table.Buffer(Table.SelectColumns(RenamedColumns1, {"Child", "Parent1", "Child Value"})),
    ParentsList = List.Buffer(List.Distinct(List.Select(Buffer[Parent1], (x)=> x <> null and Text.Trim(x) <> ""))),
    ParentsHierarchy = Table.AddColumn(Table.FromList(ParentsList, Splitter.SplitByNothing(), {"Parent1"}), "Parents", each List.Accumulate( 
    { 0..List.Count(ParentsList) -2 },
    [ Parent1 = [Parent1], Parent1 Value = try Buffer{[Child = Parent1]}[Child Value] otherwise null, Parent1 Parent = try Buffer{[Child = Parent1]}[Parent1] otherwise null ],
    (s,c)=> s & (if (try Buffer{[Child = Record.Field(s, "Parent" & Text.From(c+1) & " Parent")]}[Parent1] otherwise null) = null
                then [] else
                Record.FromList( {  try Buffer{[Child = Record.Field(s, "Parent" & Text.From(c+1))]}[Parent1] otherwise null,                 //Parent
                                    try Buffer{[Child = Record.Field(s, "Parent" & Text.From(c+1) & " Parent")]}[Child Value] otherwise null, //Parent Child Value
                                    try Buffer{[Child = Record.Field(s, "Parent" & Text.From(c+1) & " Parent")]}[Parent1] otherwise null      //Parent's Parent
                                 }, { "Parent" & Text.From(c+2), "Parent" & Text.From(c+2) & " Value", "Parent" & Text.From(c+2) & " Parent" } ) ) )    //Headers
  , type list)
,
    StepBack = RenamedColumns1,
    MergedQueryItself = Table.NestedJoin(StepBack, {"Parent1"}, ParentsHierarchy, {"Parent1"}, "ParentsHierarchy", JoinKind.LeftOuter),
    ExpandedParentsHierarchy = Table.ExpandTableColumn(MergedQueryItself, "ParentsHierarchy", {"Parents"}, {"Parents"}),
    Ad_TreeRelation = Table.AddColumn(ExpandedParentsHierarchy, "Tree Relation", each
     try Text.Combine(List.Select(Record.ToList(Record.SelectFields([Parents], List.Select(Record.FieldNames([Parents]), (x)=> not Text.Contains(x, " ")))), (y)=> y <> null and Text.Trim(y) <> ""), ", ") otherwise null,
  type text),
    Ad_CalculatedValues = Table.AddColumn(Ad_TreeRelation, "Calculated Values", each
     try List.Sum({[Child Value]} & { List.Sum(Record.ToList(Record.SelectFields([Parents], List.Select(Record.FieldNames([Parents]), (x)=> Text.Contains(x, "Value"))))) }) otherwise null,
  type number),
    RemovedColumns = Table.RemoveColumns(Ad_CalculatedValues,{"Parents"})
in
    RemovedColumns

 

 

v2 (faster)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs4sSTVU0gHTRkDa0EApVicazgXRxkDaCCFsDBU2Aak2hQubIgkjRE2gomaoZoC4IBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Child = _t, Parent = _t, #"Child Value" = _t]),
    ReplacedValue = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Child", "Parent"}),
    ChangedType = Table.TransformColumnTypes(ReplacedValue,{{"Child Value", type number}}),
    ParentsList = List.Buffer(List.Distinct(List.Select(ChangedType[Parent], (x)=> x <> null and Text.Trim(x) <> ""))),
    ParentsHierarchy = Table.AddColumn(Table.FromList(ParentsList, Splitter.SplitByNothing(), {"Parent"}), "Parents", each List.RemoveNulls(List.Accumulate(
    { 0..List.Count(ParentsList) -1 },
    {[Parent]},
    (s,c)=> s & { if s{c} = null then null else try ReplacedValue{[Child = s{c}]}[Parent] otherwise null } 
  )), type list),
    StepBack = ChangedType,
    MergedQueryParrentsHierarchy = Table.NestedJoin(StepBack, {"Parent"}, ParentsHierarchy, {"Parent"}, "ParentsHierarchy", JoinKind.LeftOuter),
    ExpandedParentsHierarchy = Table.ExpandTableColumn(MergedQueryParrentsHierarchy, "ParentsHierarchy", {"Parents"}, {"Parents"}),
    Ad_TreeRelation = Table.AddColumn(ExpandedParentsHierarchy, "Tree Relation", each try Text.Combine({[Child]} & [Parents], ", ") otherwise null, type text),
    ExpandedParents = Table.ExpandListColumn(Ad_TreeRelation, "Parents"),
    MergedQueryStepBack = Table.NestedJoin(ExpandedParents, {"Parents"}, StepBack, {"Child"}, "StepBack", JoinKind.LeftOuter),
    #"Expanded StepBack" = Table.ExpandTableColumn(MergedQueryStepBack, "StepBack", {"Child Value"}, {"Parrent Value"}),
    GroupedRows = Table.Group(#"Expanded StepBack", {"Child", "Parent", "Child Value", "Tree Relation"}, {{"Parent Calculated Values", each List.Sum([Parrent Value]), type nullable number}}),
    Ad_CalculatedValues = Table.AddColumn(GroupedRows, "Calculated Values", each [Parent Calculated Values] + [Child Value], type number),
    RemovedColumns = Table.RemoveColumns(Ad_CalculatedValues,{"Parent Calculated Values"})
in
    RemovedColumns

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

wdx223_Daniel
Super User
Super User

if Child Code is unique, Path Function can resolve it easily. 

with duplicated Child, DAX can not deal with this kind of recurrsion problem.

i modify my table to be able to have a unique child. can you help me to have a solution its either on DAX or power query. Thanks in advance

wdx223_Daniel_0-1698623963111.png

 

wdx223_Daniel_1-1698623976481.png

 

@wdx223_Daniel , Great, this works fine. May i know if its possible as well to get all the total for all grand child/children

 

ChildParentValueTree RelationCalculate Grand Child
Site1Site210Site110
Site2Site320Site2,Site120+10=30
Site3Site415Site3,Site2,Site115+20+10=45
Site5Site45Site55
Site4Site620Site4,Site5,Site3,Site2,Site120+5+15+20+10=70
Site6 100Site6,Site5,Site4,Site4,Site2,Site1100+20+5+15+20+10=170

 

Thanks again.

Tree Relation=CONCATENATEX(FILTER(Data,PATHCONTAINS(PATH(Data[Child],Data[Parent]),EARLIER(Data[Child]))),Data[Child],"|")

Calculated Values formula keeps no change.

the relation is right, but the calculated value is wrong.

 

the formula i have is below but shows wrong ouptut.

CalValue = SUMX(FILTER('ParentChild',PATHCONTAINS(EARLIER('ParentChild'[Tree]),'ParentChild'[Child])),'ParentChild'[Value])

Thanks . but i got a wrong computation on the calculated values.. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors