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)

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