count child hierarchy based on parent

[Anonymous]

Hi,

I have created hierarchy structure and count all the child of parent hierarchy.

Diagram:

          1
         /  \
       2     3
      / |\    /\
    4 5 6  7 8
          /\     /\
       9 10  11 12

From the above diagram I need if I select parent "2" then I need to count total Number of child below of "2"(Example: 4,5,9,10 and display count as  4. Note: don't select  parent "6" bacause parent "6" has child 9,10) .

Table structure:

Projectkey	ParentKey	Path	length
1		1	1
2	1	1|2	2
3	1	1|3	2
4	2	1|2|4	3
5	2	1|2|5	3
6	2	1|2|6	3
7	3	1|3|7	3
8	3	1|3|8	3
9	6	1|2|6|9	4
10	6	1|2|6|10	4
11	8	1|3|8|11	4
12	8	1|3|8|12	4

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you could refer to below stpes:

Calculate a calculated column:

Column 3 = PATHITEM('Table1'[Path],2,1)

Create below measure:

Measure 2 = CALCULATE(COUNT(Table1[ParentKey]),FILTER(ALL('Table1'),'Table1'[Column 3]=SELECTEDVALUE('Table1'[ParentKey])&&'Table1'[length]<=3))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you could refer to below stpes:

Calculate a calculated column:

Column 3 = PATHITEM('Table1'[Path],2,1)

Create below measure:

Measure 2 = CALCULATE(COUNT(Table1[ParentKey]),FILTER(ALL('Table1'),'Table1'[Column 3]=SELECTEDVALUE('Table1'[ParentKey])&&'Table1'[length]<=3))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He