Aggregate by ID in DAX

Hi

First time post - I couldn't see a DAX thread? I've been using Powerpivot for years but normally I create measures in the source data before Excel & have more normalized data through ETL process, but this isn't possible in this case.

I want to create a measure in DAX that allows me to group by a parent id & perform calculations / aggregation between rows when grouped.
For example, with the data below; What is the average time between a case being opened & closed?


Many Thanks in advance for any help!

case_id	datetime	status
1	1/2/2017 1:23:00 PM	opened
1	1/2/2017 2:00:00 PM	esclated
1	1/2/2017 2:00:00 PM	closed
2	1/3/2017 11:00:00 AM	opened
2	1/3/2017 11:15:00 AM	esclated
2	1/3/2017 11:45:00 AM	replied
2	1/3/2017 5:00:00 PM	closed
3	1/3/2017 3:00:00 PM	opened
3	1/3/2017 3:26:00 PM	esclated
3	1/3/2017 3:39:00 PM	closed

Hi Marvin,

Thanks for your quick response!

Yeah, I can do it for single dataset using a pivot / standard excel functions. However I'm trying to build this for an auto-increment data set in a report where users aren't able to edit/update the formulae themselves.

So my current solution is similar to what you have suggested - using a pivot on a hidden sheet to group by the id & then measures which calc the first open & last close dates & the difference between.

However this seems fairly inelegant & I'm not sure how well the performance will stand up when there's a few 100,000 rows. I'm fairly certain you can use the GROUPBY / SUMMARIZE / ADDCOLUMNS DAX functions to create this as a single measure but haven't been able to find a decent example yet.

Maybe group in PQ then use in Data Model with PP measures

it's only idea