cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

 Child Parent Child Value Site1 Site2 10 Site2 Site3 20 Site3 Site4 15 Site5 Site4 5 Site4 Site6 20 Site6 (No Parent or Parent column is null value)

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

 Child Parent Child Value Tree Relation Calculated Values Site1 Site2 10 Site1,Site2,Site3,Site4,Site6 10+20+15+20+0(null)=65 Site2 Site3 20 Site2,Site3,Site4,Site6 20+15+20+0(null)=55 Site3 Site4 15 Site3,Site4,Site6 15+20+0(null)=35 Site5 Site4 5 Site5,Site4,Site6 5+20+0(null) =25 Site4 Site6 20 Site4,Site6 20+0(null) =20 Site6 Site6 0(null)=0

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

1 ACCEPTED SOLUTION
Super User

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

Calculated Values formula keeps no change.

8 REPLIES 8
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:

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"}),
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),
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),
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),
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),
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.

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.

Helper II

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

Super User

Helper II

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

 Child Parent Value Tree Relation Calculate Grand Child Site1 Site2 10 Site1 10 Site2 Site3 20 Site2,Site1 20+10=30 Site3 Site4 15 Site3,Site2,Site1 15+20+10=45 Site5 Site4 5 Site5 5 Site4 Site6 20 Site4,Site5,Site3,Site2,Site1 20+5+15+20+10=70 Site6 100 Site6,Site5,Site4,Site4,Site2,Site1 100+20+5+15+20+10=170

Thanks again.

Super User

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

Calculated Values formula keeps no change.

Helper II

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

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors