Forum Discussion

elendil5259's avatar
elendil5259
Frequent Visitor
2 years ago
Solved

How to flag first instance for every day and every location

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!

 

FacilityRoomEvent Date-TimeFirst Instance
GHJ106/01/2023 8:00:00 AM1
GHJ106/01/2023 10:00:00 AM0
GHJ106/01/2023 12:00:00 PM0
GHJ206/01/2023 7:00:00 AM1
GHJ206/01/2023 8:00:00 AM0
GHJ206/01/2023 10:00:00 AM0
GHJ206/01/2023 11:00:00 AM0
KPK106/01/2023 8:00:00 AM1
KPK106/01/2023 10:00:00 AM0
KPK106/01/2023 11:00:00 AM0
KPK206/01/2023 10:00:00 AM1
KPK206/01/2023 11:00:00 AM0
KPK306/01/2023 7:00:00 AM1
KPK 306/01/2023 9:00:00 AM0

 

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    2 years ago

    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)

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

    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)

  • Gabry's avatar
    Gabry
    Super User
    2 years ago

    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
  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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