How to flag first instance for every day and every location

[elendil5259]

Hi,

I have a dataset that looks like this. I want to flag the first instance for every day, for various locations (Facility & Room). The results should look like the last column. 

Would this be better to execute in Power Query or via the Table View using DAX? 

Should I sort the table first?

Much appreciated in advance for the advice!

Facility	Room	Event Date-Time	First Instance
GHJ	1	06/01/2023 8:00:00 AM	1
GHJ	1	06/01/2023 10:00:00 AM	0
GHJ	1	06/01/2023 12:00:00 PM	0
GHJ	2	06/01/2023 7:00:00 AM	1
GHJ	2	06/01/2023 8:00:00 AM	0
GHJ	2	06/01/2023 10:00:00 AM	0
GHJ	2	06/01/2023 11:00:00 AM	0
KPK	1	06/01/2023 8:00:00 AM	1
KPK	1	06/01/2023 10:00:00 AM	0
KPK	1	06/01/2023 11:00:00 AM	0
KPK	2	06/01/2023 10:00:00 AM	1
KPK	2	06/01/2023 11:00:00 AM	0
KPK	3	06/01/2023 7:00:00 AM	1
KPK	3	06/01/2023 9:00:00 AM	0

[dufoq3]

Hi @elendil5259, it is also possible in Power Query.

Result

v1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvfwUtJRMgRiAzN9A0N9IwMjYwULKwMDIFJw9FWK1cGhxtCAGEVGUEUByIqMUBWZYzPIiAgXGRHjInRFhqiKvAO8Cfofqxp027ArwmobAXdjV4TVJGO8IQlUo4CpyBJJUSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Facility = _t, Room = _t, #"Event Date-Time" = _t]),
    GroupedRows = Table.Group(Source, {"Facility", "Room"}, {{"All", each _, type table}}),
    Ad_FirstInstance = Table.AddColumn(GroupedRows, "First Instance", each 
        [ a = {1} & List.Repeat({0}, Table.RowCount([All]) -1),
          b = Table.FromColumns(Table.ToColumns([All]) & {a}, Table.ColumnNames([All]) & {"First Instance"})
        ][b], Int64.Type),
    CombinedFirstInstance = Table.Combine(Ad_FirstInstance[First Instance])
in
    CombinedFirstInstance

 v2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvfwUtJRMgRiAzN9A0N9IwMjYwULKwMDIFJw9FWK1cGhxtCAGEVGUEUByIqMUBWZYzPIiAgXGRHjInRFhqiKvAO8Cfofqxp027ArwmobAXdjV4TVJGO8IQlUo4CpyBJJUSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Facility = _t, Room = _t, #"Event Date-Time" = _t]),
    GroupedRows = Table.Group(Source, {"Facility", "Room"}, {{"All", each _, type table}}),
    Ad_FirstInstance = Table.Combine(List.Transform(GroupedRows[All], each Table.FromColumns(Table.ToColumns(_) & { {1} & List.Repeat({0}, Table.RowCount(_) -1) }, Table.ColumnNames(GroupedRows{0}[All]) & {"First Instance"} )))
in
    Ad_FirstInstance

[Gabry]

In dax, create a calculated column, this is the formula:

FirstInstanceFlag =

IF (

    'Table'[Event Date-Time] = CALCULATE(

                    MIN('Table'[Event Date-Time]),

                    ALLEXCEPT('Table', 'Table'[Facility], 'Table'[Room])

                ),

    1,

    0

)

Please mark as accepted solution

[wdx223_Daniel · Accepted solution]

DAX:

CalculatedColumn=IF(COUNTROWS(FILTER(Table,Table[Facility]=EARLIER(Table[Facility])&&Table[Room]=EARLIER(Table[Room])&&Table[Event Date-Time]<EARLIER(Table[Event Date-Time]))),0,1)

Measure=IF(CACULATE(COUNTROWS(Table),Table[Event Date-Time]<MIN(Table[Event Date-Time])),0,1)

Power Query code:

NewStep=let a=Table.Group(Table,{"Facility","Room"},{"MinTime",each List.Min([#"Event Date-Time"]}) in Table.AddColumn(Table,"First Instance",each if a{[Facility=[Facility],Room=[Room]]}[MinTime]=[#"Event Date-Time"] then 1 else 0)