Creating self join in a Measure to count the child rows

[Anonymous]

I have the following table structure

Key      IssueType      Parent      Status

K001    EPIC

K002    EPIC

K003    Task                K001       Open

K004    Task                K002       Closed

K005    Task                K001       Open

K006    Bug                 K002       Open

I am trying to:

1. count the total number of child (Task+Bugs) of each EPIC
2. Count the total number of "Closed" child of each EPIC

I was able to do it as a COLUMN using the EARLIER  DAX method but I am unable to use the same in a MEASURE.

Any help or guidance is appreciated.

[Icey]

Hi @Anonymous ,

Is this problem sloved?

If it is sloved, could you kindly accept it as a solution to close this case?  

If not, please let me know.  

Best Regards

Icey

[Icey · Accepted solution]

Hi @Anonymous ,

You can create your measures like so:

IF (
    MAX ( 'Table'[Parent] ) <> BLANK (),
    CALCULATE (
        COUNT ( 'Table'[Key] ) + 0,
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Parent] ), 'Table'[IssueType] <> "EPIC" )
    )
)

total number of "Closed" child of each EPIC =
IF (
    MAX ( 'Table'[Parent] ) <> BLANK (),
    CALCULATE (
        COUNT ( 'Table'[Key] ) + 0,
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Parent] ), 'Table'[Status] = "Closed" )
    )
)

Best Regards,

Icey

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

[Anonymous]

Thanks Icey,
Really appreciate your help on this one.