cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
khan786 Frequent Visitor
Frequent Visitor

group by in a measure

I have 2 problems about this table.

 

power bi help 3.JPG

 

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
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
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:

14.JPG

 

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
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
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:

14.JPG

 

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.
khan786 Frequent Visitor
Frequent Visitor

Re: group by in a measure

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

khan786 Frequent Visitor
Frequent Visitor

Re: group by in a measure

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