topic Re: Parent Child Merge Calculation in DAX Commands and Tips

Parent Child Merge Calculation

Anonymous — Sat, 24 Feb 2024 08:34:27 GMT

I have a dax code the get the relationship for parent and child

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

But unable to get the calculate sum values.

Pls. kind support to get the calculated values same as Relation logic. but should be sum calculation.

Below should be same as the results column values.

Child	Parent	Value	Relation	Results
2001	2002	10	2001	10
2002	2003	15	2001\|2002	25
2003	2004	20	2001\|2002\|2003	45
2004	2005	25	2001\|2002\|2003\|2004	70
2005	2006	30	2001\|2002\|2003\|2004\|2005	100
2006	2007	35	2001\|2002\|2003\|2004\|2005\|2006	135
2007	2008	40	2001\|2002\|2003\|2004\|2005\|2006\|2007	175
2008	2009	10	2001\|2002\|2003\|2004\|2005\|2006\|2007\|2008	185
2009		15	2001\|2002\|2003\|2004\|2005\|2006\|2007\|2008\|2009	200
2010	2011	20	2010	20
2011		25	2010\|2011\|2012\|2013\|2014\|2015\|2016	245
2012	2011	30	2012\|2013\|2014\|2015\|2016	200
2013	2012	35	2013	35
2014	2012	40	2014	40
2015	2012	45	2015\|2016	95
2016	2015	50	2016	50

Re: Parent Child Merge Calculation

lbendlin — Sat, 24 Feb 2024 15:34:40 GMT

P is a calculated column

P = path('Table'[Child],'Table'[Parent])

Res is a measure

Res = var c = SELECTEDVALUE('Table'[Child]) var a= SUMMARIZE(ALLSELECTED('Table'),[Value],[P]) var b= ADDCOLUMNS(a,"inc",IF(PATHCONTAINS([P],c),[Value])) return sumx(b,[inc])

Re: Parent Child Merge Calculation

Anonymous — Sun, 25 Feb 2024 03:08:21 GMT

lbendlin , Awesome. It works as expected. Thanks.

Re: Parent Child Merge Calculation

Anonymous — Sun, 25 Feb 2024 06:10:20 GMT

lbendlin , hi again, i use the same code to count the values

ResCount =

var c = SELECTEDVALUE('Table'[Child])

var a= SUMMARIZE(ALLSELECTED('Table'),[Value],[P])

var b= ADDCOLUMNS(a,"inc",IF(PATHCONTAINS([P],c),[Value]))

return COUNTX(b,[inc])

can you help me to count only the values that is more than zero or not null values on the column Value.

Re: Parent Child Merge Calculation

lbendlin — Sun, 25 Feb 2024 13:11:58 GMT

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Parent Child Merge Calculation

Anonymous — Sun, 25 Feb 2024 17:27:51 GMT

lbendlin hi again, below should be the output table in column Result_Count and Result_Sum

I also have an issue, error showing, if i change the child and parent value as text.

Is there's a way that instead of showing in column Relation as Child/Parent as number should be the child/parent name instead. Thanks again.

Re: Parent Child Merge Calculation

lbendlin — Sun, 25 Feb 2024 17:50:02 GMT

Can you please post in a usable format? I can't work with screenshots.

Re: Parent Child Merge Calculation

Anonymous — Mon, 26 Feb 2024 03:00:22 GMT

lbendlin , pls. see shared link.

parent-child

Re: Parent Child Merge Calculation

lbendlin — Tue, 27 Feb 2024 00:32:22 GMT

you can get rid of the errors by replacing missing parent names with nulls.

I don't understand the logic behind your expected output columns. Please elaborate.