Identify first occurence of event based on multiple criterias

Question

I have a dataset in PowerPivot and need to find a way to flag ONLY the first occurrence of a customer sub event

Context: Each event (COLUMN A) can have X number of sub events (COLUMN B), I already have a flag that identifies a customer event based on multiple criteria's (COLUMN D)... What I need is a way to flag only the first occurrence of a customer sub event within each event, I've added a fake COLUMN E to illustrate how the flagging should work.

UPDATE Additional situation - Having duplicated customer sub_events but only need to flag the first sub_event... should look like this:

Answer 1

Create a calculated column in your model using the following expression:

=
IF (
    [Customer_Event] = 1
        && [Sub_Event]
            = CALCULATE (
                FIRSTNONBLANK ( 'Table'[Sub_Event], 0 ),
                FILTER (
                    'Table',
                    'Table'[Event] = EARLIER ( 'Table'[Event] )
                        && [Customer_Event] = 1
                )
            ),
    1,
    0
)

If Sub_Event column is a number replace FIRSTNONBLANK ( 'Table'[Sub_Event], 0 ) by MIN('Table'[Sub_Event])

Also if your machine regional settings use ; (semicolon) as list separator replace every , (comma) in my expression by a semicolon in order to match your settings.

UPDATE: Repeated values in Sub_Event column.

I think we can use CaseRow# column to get the first occurence of Sub_Event value:

=
IF (
    [Customer_Event] = 1
        && [Sub_Event]
            = CALCULATE (
                FIRSTNONBLANK ( 'Table'[Sub_Event], 0 ),
                FILTER (
                    'Table',
                    'Table'[Event] = EARLIER ( 'Table'[Event] )
                        && [Customer_Event] = 1
                )
            )
        && [CaseRow#]
            = CALCULATE (
                MIN ( 'Table'[CaseRow#] ),
                FILTER (
                    'Table',
                    'Table'[Event] = EARLIER ( 'Table'[Event] )
                        && [Customer_Event] = 1
                )
            ),
    1,
    0
)

It is not tested but should work.

Let me know if this helps.