cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors