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

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity 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)