cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

## Calculate First Occurence in a Column based on 2 criteria

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.

1 ACCEPTED SOLUTION
Community Champion

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)

3 REPLIES 3
Community Champion

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)

Helper V

super confused.

The RETURN of

``MIN('Table'[Index])``

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

Helper V

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" , "")``````

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors