get all child nodes count

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.