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
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
Solved! Go to Solution.
Tree Relation=CONCATENATEX(FILTER(Data,PATHCONTAINS(PATH(Data[Child],Data[Parent]),EARLIER(Data[Child]))),Data[Child],"|")
Calculated Values formula keeps no change.
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"}),
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
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 , 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.
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.
Thanks . but i got a wrong computation on the calculated values..
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
62 | |
22 | |
18 | |
12 |