topic Re: Find first occurrence of column by date/time belonging to group of rows, populate new column in DAX Commands and Tips

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

jeff_h — Mon, 14 Mar 2022 06:54:23 GMT

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 Alpha	Flow Alpha
1111A	2022-03-01 10:02	Flow Beta	Flow Alpha
1111A	2022-03-01 10:05	Flow Gamma	Flow Alpha
2222B	2022-03-01 12:34	Flow Delta	Flow Delta
2222B	2022-03-01 12:35	Flow Omicron	Flow Delta
2222B	2022-03-01 12:56	Flow Alpha	Flow Delta
2222B	2022-03-01 13:10	Flow Omega	Flow Delta
3333C	2022-03-01 14:22	Flow Gamma	Flow Gamma
3333C	2022-03-01 14:47	Flow Alpha	Flow Gamma

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

Thanks in advance,

Jeff.

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

amitchandak — Mon, 14 Mar 2022 07:37:37 GMT

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])

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

jeff_h — Mon, 14 Mar 2022 08:24:54 GMT

amitchandak Awesome! Thanks so much, that worked perfectly.

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

kb177 — Thu, 06 Jul 2023 13:07:32 GMT

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