What is the best way to group by

[EaglesTony]

I have a table as follows:

Parent ID   Child ID   Child Status  Child Points

1                1              Open            2

1                2              Open            1

1                5              Closed          5

2                8              Dev               null

2                3              Review          1

I need this table in tact, but I need a new table as follows (I am eventually a group by):

Parent ID  # of Children  # of Child Points

1                 3                  8

2                 2                  1

[BA_Pete]

Hi @EaglesTony ,

If you're using Power BI, then you may not need to physically create the second table (depending on your requirements).

The second table can be visualised based on the first table with the following measures:

// If [Child ID] is always unique per [Parent ID]
noofChildren = COUNTROWS(Table1)

// If [Child ID] is NOT always unique per [Parent ID]
noofUniqueChildren = DISTINCTCOUNT(Table1[Child ID])

noofChildPoints = SUM(Table1[Child Points])

Use these measures along with your [Parent ID] column in a table/matrix visual and it will output your second table.

Pete

[HotChilli]

Yes, use the 'Group By'.  What are you having difficulty with?

[EaglesTony · Accepted solution]

I think I may have figured it out, I had to:

1) Duplicate the table (since I need to original details)

2) On the Duplicated table use the Group By with multiple conditions