Forum Discussion

scabral's avatar
scabral
Icon for Helper IV rankHelper IV
6 years ago
Solved

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

Hi,

 

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

 

Claim IdDamage IdLocation IdSeq #Reserve DateReserve ValueReserve Status
199333104-01-2020100approved
199333208-11-2020200approved
277888101-01-2020500approved
277888206-25-2020500approved
277888306-28-2020300pending
355444103-01-2020250approved
355444203-01-2020300approved
400233107-01-2020900pending

 

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 IdDamage IdLocation IdSeq #Reserve DateReserve ValueReserve Status
199333104-01-2020100approved
277888206-25-2020500approved
355444203-01-2020300approved

 

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

 

  • Anonymous's avatar
    Anonymous
    6 years ago

     

     

    // 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

     

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

     

    // 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's avatar
      scabral
      Icon for Helper IV rankHelper IV
      6 years ago

      Hi Daxer,

       

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

       

      Scott

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        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.
    • scabral's avatar
      scabral
      Icon for Helper IV rankHelper IV
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago
        I don't know the model, hence I can't tell you where you could be wrong in the formula. Sorry.
  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    6 years ago

    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