how do i get a count of children

[EaglesTony]

Hi,

I have a parent table called Feature and a child table called Issues.

I was able to do a Merge Query on the child table to do a left join to the parent table to get the parents primary id.

Now what I need to do on the Parent table is to get a column called "# of Children" based on the child table.

Parent table:

Primary id 1

Primary id 2

Child table:

Child id 123  Primary id 1

Child id 456  Primary id 1

Child id 789  Primary id 2

Thus Parent table would result in:

Primary id 1 CountofChildren=2

Primary id 2 CountofChildren=1

Thanks

[dufoq3]

Hi @EaglesTony 

Result:

let
    Primary = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMlKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Primary id" = _t]),
    Child = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcsnPSwVShkqxOtFKJqZm6ELmFpZAtmeeQkBRfnpRanExXMbQANkAI6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Child id" = _t, ChildStatus = _t, #"Primary id" = _t]),
    #"Merged Queries" = Table.NestedJoin(Primary, {"Primary id"}, Child, {"Primary id"}, "Child", JoinKind.LeftOuter),
    #"Added #ofChildren" = Table.AddColumn(#"Merged Queries", "#ofChildren", each Table.RowCount([Child]), Int64.Type),
    #"Added #ofChildrenDone" = Table.AddColumn(#"Added #ofChildren", "#ofChildrenDone", each Table.RowCount(Table.SelectRows([Child], (x)=> x[ChildStatus] = "Done")), Int64.Type),
    #"Added #ofChildrenNotDone" = Table.AddColumn(#"Added #ofChildrenDone", "#ofChildrenNotDone", each [#"#ofChildren"] - [#"#ofChildrenDone"], Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added #ofChildrenNotDone",{"Child"})
in
    #"Removed Columns"

[Anonymous · Accepted solution]

Hi @EaglesTony ,

Please check the file:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[EaglesTony]

I got part of it:

NbrOfChildren =

IF (

    CALCULATE(COUNTROWS(ChildTable),FILTER(ChildTable,ChildTable[Primary Id]=Parent[Primary Id])) = BLANK(),

    0,

    CALCULATE(COUNTROWS(ChildTable),FILTER(ChildTable,ChildTable[Primary Id]=Parent[Primary Id]))

    )

[EaglesTony]

I want to change this slightly to:

Parent table:

Primary id

1

2

Child table:

Child id     ChildStatus    Primary id 

123            Done             1

456            Done             1

789            In Progress    1

1023          Done              2

Thus Parent table would result in:

Parent table:

Primary id  #ofChildren   #ofChildrenDone   #ofChildrenNotDone

1                 3                    2                            1

2                 1                    1                            0

[lbendlin]

Repeating the same data in the same table  is a design red flag.  I would recommend you use a  matrix visual instead which comes with built-in and clearly identified Totals areas.

[EaglesTony]

There is no repeating of data, just counting children within the parent

[lbendlin]

Your column #ofChildren is a redundant repetition of the data in the other two columns.

[EaglesTony]

#of Children is the total number of children attached to the parent

# of ChildrenDone is a subset breakdown of those Children where there status is done

# of ChildrenNotDone is a subset breakdown of those Children where there status is not done

So a parent could have 3 children and of those 3 children, 2 are done and 1 is not done

[lbendlin]

My recommendation is to use the Matrix visual. I hope someone else can help you further.

[EaglesTony]

What would the syntax look like ?

[lbendlin]

Child table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMlSK1YlWMjE1g7PNLSyBbCOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Child id" = _t, #"Primary id" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Child id", Int64.Type}, {"Primary id", Int64.Type}})
in
    #"Changed Type"

Parent table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMlKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Primary id" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Primary id", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Primary id"}, Child, {"Primary id"}, "Child", JoinKind.LeftOuter),
    #"Expanded Child" = Table.ExpandTableColumn(#"Merged Queries", "Child", {"Child id"}, {"Child id"}),
    #"Grouped Rows" = Table.Group(#"Expanded Child", {"Primary id"}, {{"CountofChildren", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

[lbendlin]

- do an inner join from the parent to the child table

- group by Primary id with aggregation "count of rows"