retrieve rows with max value within a group based on date filter

[scabral]

Hi,

i have the following table with some sample data similar to what we have in our model:

Claim Id	Damage Id	Location Id	Seq #	Reserve Date	Reserve Value	Reserve Status
1	99	333	1	04-01-2020	100	approved
1	99	333	2	08-11-2020	200	approved
2	77	888	1	01-01-2020	500	approved
2	77	888	2	06-25-2020	500	approved
2	77	888	3	06-28-2020	300	pending
3	55	444	1	03-01-2020	250	approved
3	55	444	2	03-01-2020	300	approved
4	00	233	1	07-01-2020	900	pending

there will be a date filter called "As Of Date" that will be selected by the user.  I will need to create a new measure that filters the above table using these rules each time:

1. rows where reserve date <= the As Of Date selected by the user

2. rows that are in "Approved" status

3. for each combination of Claim Id, Damage Id, and Location Id, get the row with the latest sequence number

4. sum the reserve value column

so based on the sample data above, if the user select an As Of Date of 8-1-2020, i should get the following rows and sum the reserve value field:

Claim Id	Damage Id	Location Id	Seq #	Reserve Date	Reserve Value	Reserve Status
1	99	333	1	04-01-2020	100	approved
2	77	888	2	06-25-2020	500	approved
3	55	444	2	03-01-2020	300	approved

the measure should return 900 since that is the sum of the reserve values for the rows that meet the criteria.

i figured i would need to filter the table based on the As Of Date as a first step, but i'm not sure how to get the max sequence number for the group.  any help would be appreciated.

thansk

scott

[Fowmy]

@scabral 

Hope this solution fits your expected results?
I created a calendar table and linked it to adjust the as of date to the end date in the slicer.

Measure = 
VAR T = 
	CALCULATETABLE(
		GROUPBY(
			'Table',
	     	'Table'[Claim Id],'Table'[Damage Id],'Table'[Location Id],         
	        "M", MAXX(CURRENTGROUP(),'Table'[Seq #])
	    ) ,	
	'Table'[Reserve Status] = "approved"
    )
    
VAR T2 = 

FILTER(
	GENERATE(	
		SELECTCOLUMNS( T,
			"CID", 'Table'[Claim Id],"DID", 'Table'[Damage Id],"LID", 'Table'[Location Id],
			"SEQ", [M]
		),		
		'Table'
	),
	'Table'[Claim Id] = [CID] && 'Table'[Seq #] = [SEQ]
)
	
RETURN

SUMX(T2, 'Table'[Reserve Value])

________________________

If my answer was helpful, please mark it as a solution

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube
LinkedIn

[Anonymous · Accepted solution]

// Assumptions:
// Apart from [Reserve Status] all other columns
// in the fact table T should be hidden. Let's 
// assume that the AsOfDateSlicer table that stores
// dates is DISCONNECTED from T.

[Total Reserve] =
var __asOfDate = SELECTEDVALUE( AsOfDateSlicer[Date] )
var __filter =
    CALCULATETABLE(
        ADDCOLUMNS(
            SUMMARIZE(            
                T,
                T[ClaimId],
                T[DamageID],
                T[LocationID]
            ),
            "@MaxSeq",
                CALCULATE( MAX( T[Seq #] ) )
        ),
        KEEPFILTERS( T[Reserve Status] = "approved" ),
        T[Reserve Date] <= __asOfDate
    )
var __result =
    CALCULATE(
        SUM( T[Reserve Value] ),
        treatas(
            __filter,
            T[ClaimId],
            T[DamageID],
            T[LocationID],
            T[Seq #]
        ),
        ALL( T )
    )
RETURN
    __result

[scabral]

Hi Daxar,

so another question.  I tried to use the DAX to also count a column from a dimension table, but only for the rows in the dimension table that exist in the TREATAS table like this:

Count of Claims =

VAR ReserveFilter =

CALCULATETABLE (

ADDCOLUMNS (

SUMMARIZE (

'Claim Reserve Values',

'Claim Reserve Values'[Claim ID],

'Claim Reserve Values'[Damage ID],

'Claim Reserve Values'[Location ID]

),

"MaxSeq", CALCULATE ( MAX ( 'Claim Reserve Values'[Reserve Sequence Num] ) )

),

KEEPFILTERS('Claim Reserve'[Reserve Status ID] = 2 )

)

VAR Result =

CALCULATE (

DISTINCTCOUNT( 'Claim Adjustment File'[Claim Number]),

TREATAS (

ReserveFilter,

'Claim Reserve Values'[Claim ID],

'Claim Reserve Values'[Damage ID],

'Claim Reserve Values'[Location ID],

'Claim Reserve Values'[Reserve Sequence Num]

),

ALL ( 'Claim Reserve Values' ),

'Claim Reserve'[Reserve Group ID] in {1000007, 1000008}

)

RETURN

Result

howerver, the count comes back for all of the rows in the 'Claim Adjustment File' table.  I want to only count the 'Claim Adjustment File'[Claim Number] if it exists as part of the TREATAS result.  The [claim id] field would be the relationship to use between the tables.

thansk

scott

[Anonymous]

I don't know the model, hence I can't tell you where you could be wrong in the formula. Sorry.

[scabral]

Hi Daxer,

this seems to work, but why did you use the KEEPFILTERS on only the Reserve Status field?

Scott

[Anonymous]

KEEPFILTERS lets you keep existing filters on the column instead of overwriting them. This is usually needed on a column that is exposed to the end user because when the user filters by the column you usually don't want to overwrite the filter in your measure. But whether you need KEEPFILTERS or not depends on what you want to achieve. I assumed that you wanted the measure to respond dynamically to a filter on the column mentioned under KEEPFILTERs.