Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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)

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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