Calculate First Occurence in a Column based on 2 criteria

[WorkHard]

I'm trying to calculate the First time the Event Expired based on the earliest Index.

|-Event-|	|-Index-|	|-Status-|	|-First Occurrence Expired-|
Event1	1	Valid
Event1	2	Valid
Event1	3	Expired	First
Event1	4	Expired
Event1	5	Expired
Event1	6	Expired
Event1	7	Expired
Event2	1	Expired	First
Event2	2	Expired
Event3	1	Valid
Event3	2	Expired	First
Event3	3	Expired
Event3	4	Expired

I'm almost there but I don't know where to take it from here:

First Occurrence Expired = CALCULATE(MAX(datasource[Index]),
FILTER(datasource,datasource[Event] = EARLIEST(datasource[Event]) &&
'datasource'[Index] = EARLIEST('datasource'[Index]) && 
datasource[Status] = "Expired"))

This gives me the correct index.

[FrankAT · Accepted solution]

Hi @WorkHard ,

if I understood you right then this can be the solution:

First Occurance Expired = 
VAR _MinIndex = CALCULATE(MIN('Table'[Index]), FILTER(ALLEXCEPT('Table','Table'[Event]),'Table'[Status] = "Expired"))
RETURN
    IF(MIN('Table'[Index]) = _MinIndex , "First" , "")

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[WorkHard]

super confused.

The RETURN of 

MIN('Table'[Index])

(last line in your code) is always returning 1 for me.

[WorkHard]

Fixed it by removing MIN from the calculation. It's possible that you were creating a measure and I am using a calculating column.

First Occurance Expired = 
VAR _MinIndex = CALCULATE(MIN('Table'[Index]), FILTER(ALLEXCEPT('Table','Table'[Event]),'Table'[Status] = "Expired"))
RETURN
    IF('Table'[Index] = _MinIndex , "First" , "")