cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WorkHard
Helper V
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-|
Event11Valid 
Event12Valid 
Event13ExpiredFirst
Event14Expired  
Event15Expired  
Event16Expired  
Event17Expired  
Event21ExpiredFirst
Event22Expired  
Event31Valid 
Event32ExpiredFirst
Event33Expired 
Event34Expired 

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
FrankAT
Super User II
Super User II

Hi @WorkHard ,

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

 

14-05-_2021_22-59-23.png

 

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)

View solution in original post

3 REPLIES 3
FrankAT
Super User II
Super User II

Hi @WorkHard ,

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

 

14-05-_2021_22-59-23.png

 

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)

View solution in original post

super confused.

The RETURN of 

MIN('Table'[Index])

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

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

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors