get all child nodes count

[ak77]

Hi All

I have a db model as below 

parent 101 has 3 child (102,103, 104), 102, has 2 ,103 has 1 and 104 has 1

i need a column or a measure 'child count' where i should get all child counts of a parent : 7

Is this possible?

[Anonymous · Accepted solution]

Hi @ak77 

I think I understand what you need. In your case, the number of descendants of 101 should return 7, 102 returns 2, and 103 and 104 return 1. You need a measure to get these results.

Please follow below steps, i hope it helps.

1. Create a new table to store all parents and use the new table parent field as a slicer.

Note: Do not create a relationship between this new table and your data table.

Parent Selection = DISTINCT('Table'[parent])

 2. Create a measure with the follow DAX:

TotalChildCount = 
VAR CurrentParent = SELECTEDVALUE('Parent Selection'[parent])
VAR CurrentChild = CALCULATETABLE(VALUES('Table'[child]), 'Table'[parent] = CurrentParent)
VAR ChildCount =
CALCULATE(
    COUNTROWS(
    FILTER(
        'Table',
        'Table'[parent] = CurrentParent
    )
    )
)
VAR GrandChildCount =
CALCULATE(
    COUNTROWS(
    FILTER(
        'Table',
        'Table'[parent] IN CurrentChild
    )
    )
)
RETURN
ChildCount + GrandChildCount

Here is my test result:

 Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ak77]

hi Thanks for reply. i need all the child , grand child of a parent..there are 10 parents .. how will the calculation work for each parent to get child and its grand child. Please let me know

[parry2k]

@ak77 why not simple row count would work until I'm missing something;

Count = COUNTROWS ( Table )