Measure does not return min date

[Dunner2020]

Hi there,

I'm trying to create a measure that returns the minimum date if two or more records have the same ID and AuthorisationType is equal to 2 or 11. I wrote a measure which is as follows:

Min Date = CALCULATE(Min(Table[EffectiveDate]),FILTER(Table,Table[ID] = MAX(Table[ID]) &&(Max(Table[AuthorisationTypeID]) = 11 || Max(Table[AuthorisationTypeID]=2))))

The above measure does not return the desired result. For example, as shown in the following picture, record ID 108644 has two dates and the measure did not return the min date.

Could anyone help me where I made the mistake?

Sample file here

[smpa01]

@Dunner2020  please try this

Measure3 = 
MINX (
    FILTER ( 'Table', 'Table'[AuthorisationTypeID] IN { 2, 11 } ),
    VAR _0 =
        CALCULATE ( MAX ( 'Table'[ID] ) )
    VAR _1 =
        CALCULATE ( MIN ( 'Table'[EffectiveDate] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
    VAR _2 =
        CALCULATE (
            MIN ( 'Table'[EffectiveDate] ),
            TREATAS ( { ( { _0 }, { _1 } ) }, 'Table'[ID], 'Table'[EffectiveDate] )
        )
    RETURN
        _2
)

[Ashish_Mathur · Accepted solution]

Hi,

Drag ID to the visual and write this measure:

Min Date = CALCULATE(Min(Table[EffectiveDate]),Table[AuthorisationTypeID] = 11 ||Table[AuthorisationTypeID]=2)