DAX - Measure to get Count of child Id's for a given Parent

[Anonymous]

Hello Team,

We have a DB table with following fields, that have Parent-Child hierarchy. 

Source TableOur reporting requirement is to come up with following tabular visualization, to display only parent Id Categories (C1,C2,C3) with count of child_id’s associated to my Parent. Something as follows:  

Final Output

So, when I select Id -1, the measure should provide the count of child_id’s for it, which is 2 in this case.

I’m SQL guy and very new to DAX, this can be done using a self-join for Id to ParentId in SQL.

Can anyone please provide a DAX expression for the same?

Thanks,
Sundeep

[Zubair_Muhammad]

@Anonymous 

Please see attached file as well with your sample data and results

[Zubair_Muhammad · Accepted solution]

hi @Anonymous 

Try this method

First a calculated column

PATH =
PATHITEM ( PATH ( TableName[Id], TableName[ParentId] ), 1, INTEGER )

Now this MEASURE

No. of Children =
VAR myvalue =
    SELECTEDVALUE ( TableName[Id] )
VAR mycategory =
    SELECTEDVALUE ( TableName[Category] )
RETURN
    CALCULATE (
        COUNT ( TableName[PATH] ),
        TableName[PATH] = myvalue,
        TableName[Category] <> mycategory,
        ALL ( TableName )
    )

[Anonymous]

Thank you Zubair, this worked. Thanks a bunch for attaching the sample report as well.