DAX SUMMARIZE() with filter - Powerpivot

Question

Rephrasing a previous question after further research. I have a denormalised hierarchy of cases, each with an ID, a reference to their parent (or themselves) and a closure date.

Cases
ID | Client    | ParentMatterName | MatterName | ClaimAmount | OpenDate | CloseDate
1  | Mr. Smith | ABC Ltd          | ABC Ltd    | $40,000     | 1 Jan 15 | 4 Aug 15
2  | Mr. Smith | ABC Ltd          | John       |      $0     |20 Jan 15 | 7 Oct 15
3  | Mr. Smith | ABC Ltd          | Jenny      |      $0     | 1 Jan 15 | 20 Jan 15
4  | Mrs Bow   | JQ Public        | JQ Public  |  $7,000     | 1 Jan 15 | 4 Aug 15

After the help of greggyb I also have another column, Cases[LastClosed], which will be true if the current row is closed, and is the last closed of the parent group.

There is also a second table of payments, related to Cases[ID]. These payments could be received in parent or child matters. I sum payments received as follows:

Recovery All Time:=CALCULATE([Recovery This Period], ALL(Date_Table[dateDate]))

I am looking for a new measure which will calculate the total recovered for a unique ParentMatterName, if the last closed matter in this group was closed in the Financial Year we are looking at - 30 June end date.

I am now looking at the SUMMARIZE() function to do the first part of this, but I don't know how to filter it. The layers of calculate are confusing. I've looked at This MSDN blog but it appears that this will filter to only show the total payments for that matter that was last closed (not adding the related children).

My current formula is:

Recovery on Closed This FY :=
CALCULATE (
    SUMX (
        SUMMARIZE (
            MatterListView,
            MatterListView[UniqueParentName],
            "RecoveryAllTime", [Recovery All Time]
        ),
        [RecoveryAllTime]
    )
)

All help appreciated.

greggyb greggyb · Accepted Answer · 2015-10-20T14:06:04

Again, your solution is much more easily solved with a model addition. Remember, storage is cheap, your end users are impatient.

Just store in your Cases table a column with the LastClosedDate of every parent matter, which indicates the date associated with the last closed child matter. Then it's a simple filter to return only those payments/matters that have LastClosedDate in the current fiscal year. Alternately, if you know for certain that you are only concerned with the year, you could store just LastClosedFiscalYear, to make your filter predicate a bit simpler.

If you need help with specific measures or how you might implement the additional field, let us know (I'd recommend adding these fields at the source, or deriving them in the source query rather than using calculated columns).

DAX SUMMARIZE() with filter - Powerpivot

1 Answers