cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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.

7 REPLIES 7
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 Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors