Skip to main content
cancel
Showing results for 
Search instead 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

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

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

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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