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
jeff_h
Frequent Visitor

Find first occurrence of column by date/time belonging to group of rows, populate new column

Hi All,

As per the title, what I'm trying to acheive in a calculated column is find the first entry of a column value ('flowName') for a group of rows (grouped by 'uniqueId'), and populate this found value into a new column ('firstFlow')

So for example, heres a sample set of existing data with a uniqueId, dateTime and flowName.  

 

uniqueId               dateTime                         flowName                  firstFlow                     

1111A

2022-03-01 10:01

Flow AlphaFlow Alpha
1111A2022-03-01 10:02Flow BetaFlow Alpha
1111A2022-03-01 10:05

Flow Gamma

Flow Alpha
2222B2022-03-01 12:34Flow DeltaFlow Delta
2222B2022-03-01 12:35Flow OmicronFlow Delta
2222B2022-03-01 12:56Flow AlphaFlow Delta
2222B2022-03-01 13:10Flow OmegaFlow Delta
3333C2022-03-01 14:22Flow GammaFlow Gamma
3333C2022-03-01 14:47Flow AlphaFlow Gamma


What I'm trying to acheive is the column result in Blue. 

Thanks in advance,

Jeff.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@jeff_h , a new column

 

New Column =
var _max = minx(filter(Table, [uniqueId] = earlier([uniqueId]) ) ,[dateTime])
return
maxx(filter(Table, [uniqueId] = earlier([uniqueId]) && [dateTime] =_max) ,[flowName])

View solution in original post

3 REPLIES 3
kb177
Helper II
Helper II

@amitchandak   can this be used when month changes, and source is used a get from folder 

amitchandak
Super User
Super User

@jeff_h , a new column

 

New Column =
var _max = minx(filter(Table, [uniqueId] = earlier([uniqueId]) ) ,[dateTime])
return
maxx(filter(Table, [uniqueId] = earlier([uniqueId]) && [dateTime] =_max) ,[flowName])

@amitchandak Awesome! Thanks so much, that worked perfectly. 

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.

Top Solution Authors