Help with Parent Child group by on conditions of another tab;e

[EaglesTony]

Hi,

  I have a table1 as follows:

Issue   ChildKey     Component

1578   1000           On-Hold

1578   1001           On-Hold

1579    2000          Q4

1579    2001          Q4

2173    <null>       On-Hold

2173    <null>       Q4

What I want is the result of :

Issue   ChildKey     ParentOnHold       ParentQ4

1578   1000            Y                            N

1578   1001           Y                             N

1579    2000          N                            Y

1579    2001          N                            Y

2173    <null>       Y                             Y

2173    <null>       Y                             Y

Thanks

[v-rzhou-msft · Accepted solution]

Hi @EaglesTony ,

Please try code as below to create calculated columns.

ParentOnHold = 
VAR _ComponentbyIssue = CALCULATETABLE(VALUES('Table'[Component]),ALLEXCEPT('Table','Table'[Issue]))
RETURN
IF("On-Hold" in _ComponentbyIssue,"Y","N")

ParentQ4 = 
VAR _ComponentbyIssue = CALCULATETABLE(VALUES('Table'[Component]),ALLEXCEPT('Table','Table'[Issue]))
RETURN
IF("Q4" in _ComponentbyIssue,"Y","N")

Result is as below.

Best Regards,
Rico Zhou

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

[mickey64]

For your reference.

Step 1: I add two caliculated columns to the table and make a matrix.

    ParentOnHold = IF([ChildKey] = BLANK(),"Y",IF([Component] = "On-Hold","Y","N"))

    ParentQ4 = IF([ChildKey] = BLANK(),"Y",IF([Component] = "Q4","Y","N"))

Step 2: I make an new table.

[EaglesTony]

I'm not sure that will work, as it will create 2 columns for each record so 

Issue    ChildKey    ParentOnHold    ParentQ4

2173    <null>       Y                         N

2173   <null>        N                        Y

Since both these records have the same parent and both On-Hold and Q4, I want both records to represent

this as follows:

Issue    ChildKey    ParentOnHold    ParentQ4

2173    <null>       Y                         Y

2173   <null>        Y                        Y

[mickey64]

I have corrected my previous post.

[EaglesTony]

Looks like that worked, now I just need to get more columns from the Parent table in my Table that has Issue, ChildKey,ParentOnHold,ParentQ4..I'm thinking I can merge the two.

[mickey64]

I think you can simply add some calculated columns to the table1.