Powerpivot process time in groups

[BojeErvenius]

Hi.

I have 2 tables with data in a PowerPivot model.

Table 1 is Application and table 2 is Lender. They are related on applicationID.

An application can have many lenders. Each lender is a part of a group (1-5) and has a start date and a stop date, there can be many lenders with the same group for each order. They are processed group by group, first group 1 is processed and then group 2 and so forth.

I now would like to create a measure that calculates the average processing time for each group. My workaround at this moment is to transfer the information to the application table with calculated columns, one for each group, and then create the measures base on this columns. This is probably the worst solution ever
=MAXX(FILTER(RELATEDTABLE(Lender);Lender[ApplyGroup]=1);Lender[AppliedLenderDT])-MINX(FILTER(RELATEDTABLE(Lender);Lender[ApplyGroup]=1);Lender[AppliedLenderDT])

 

[BojeErvenius]

Cant edit?
The calculated column should be
=MAXX(FILTER(RELATEDTABLE(ProductApp);ProductApp[ApplyGroup]=1&&ProductApp[AnswerLenderDT]>0);ProductApp[AnswerLenderDT])-MINX(FILTER(RELATEDTABLE(ProductApp);ProductApp[ApplyGroup]=1&&ProductApp[AppliedLenderDT]>0);ProductApp[AppliedLenderDT])

 

[TimRodman]

Can you paste a screenshot of what your table relationships look like?

You can upload your screenshot to Imgur using the upload images button on the top. No need to sign-up for an account.

Then you can come back here and paste the Direct Link pictured below into the Insert Image button on this board.