group by in a measure

[Anonymous]

I have 2 problems about this table.

This is a heirarchy like this:

R (grand parent) > X (parent) > A (child), B (child)

S  (grand parent) > Y (parent) > C (child), D (child), E (child)

Problem 1: I need to find the latest date among all the children of a parent, grouped by parent. Meaning;

X   -  which child has the latest date? 1-Feb-18

Y - which child has the latest date? 1-May-18

Problem 2: I need to find total work done by all the children of a parent, grouped by parent. Meaning;

X   -  total work done by children? 5

Y - total work done by children? 9

If these can be new columns, it will be great.

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=IF(Data[Type]="Parent",CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Parent Name]=EARLIER(Data[Name]))),BLANK())

Hope this helps.

[v-lili6-msft · Accepted solution]

hi, @Anonymous

You could use EARLIER Function to create these two columns

Problem 1 = IF(Data[Type]="Parent",CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Parent Name]=EARLIER(Data[Name]))),BLANK())

Problem 2 = IF(Data[Type]="Parent",CALCULATE(SUM(Data[work done]),FILTER(Data,Data[Parent Name]=EARLIER(Data[Name]))),BLANK())

Result:

Best Regards,

Lin

[Anonymous]

Thanks so much. That solved my problem instantly. You are a genius!

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=IF(Data[Type]="Parent",CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Parent Name]=EARLIER(Data[Name]))),BLANK())

Hope this helps.

[Anonymous]

Thanks so much. That solved my problem instantly. You are a genius!