summarize data from 2 tables and get max row

[scabral]

Hi,

I have the following 2 tables with some sample data:

Reserve Values:

Reserve:

both tables are joined in the model using the ReserveKey (Reserve(one) to ReserveValues(many)).

What i need to do is for these 2 tables is get the row that has the max sequence number for each ClaimId, DamageId, and Location ID from Reserve Values table where the Reserve[ReserveType] = "Estimate".  But I also need to append the ReserveStatus and the ReserveType from Reserve table to the row for that max sequence number.

so in the above sample data i would need the following:

i have written the following DAX which gets me the correct data, except for the ReserveStatus:

CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'Reserve Values',
                'Reserve Values'[ClaimID],
                'Reserve Values'[DamageID],
                'Reserve Values'[LocationID],
                'Reserve'[ReserveType]
            ),
            "MaxSeqNum", CALCULATE ( MAX ( 'Reserve Values'[Reserve Sequence Num] ) )
        ),
        KEEPFILTERS ( 'Reserve'[ReserveType] = "Estimate")

)

If I add ReserveStatus into the summarize then it includes it in the grouping which i don't want because i want only the max sequence number for claimid,damageid and locationid.

Thanks

Scott

[Anonymous]

Hi @scabral 

It's really not clear what you want to do. Are you trying to write a measure? Or a query? A calculated table? Apart from that, if you group rows as above by claimid, damageid and locationid (where the reserve type is "Estimate), then each grouping could potentially have several reserve statuses. You did not say which of them you'd like to attach, so this does not have a clear answer.

By the way, when you filter by 'Reserve'[ReserveType] = "Estimate", what's the motivation to include 'Reserve'[ReserveType] under SUMMARIZE? We know it's going to be "Estimate", so what's the point?

[scabral]

HI Daxer,

so this will eventually be a measure where i will sum the Reserve Amt for the rows that i need.  

You are correct in that each combination of ClaimId, DamageId, and LocationId can have multiple reserve statuses, however i need the max sequence number for each ClaimId, DamageId, and LocationID from the Reserve Values table.  Once i get the max sequence number row, i then need to append the Reserve Status from the Reserve Table which is related to the Reserve Values table on ReserveKey.

Also, there are different ReserveTypes in our dataset like Deductibles, Exposures, etc...

I only need to include "Estimate" reserve types in this measure.

thanks

Scott