cancel
Showing results for
Did you mean:
Frequent Visitor

## group by in a measure

This is a heirarchy like this:

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

(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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

## Re: group by in a measure

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.

Community Support Team

## Re: group by in a measure

hi, @khan786

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User

## Re: group by in a measure

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.

Community Support Team

## Re: group by in a measure

hi, @khan786

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: group by in a measure

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

Frequent Visitor

## Re: group by in a measure

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