Forum Discussion
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 ...
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)
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
CombinedFirstInstancev2
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