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:

enter image description here

Only customer events will be flagged, right? – alejandro zuleta Oct 11 '16 at 21:55 — alejandro zuleta, Oct 11 '16 at 21:55

alejandro zuleta · Accepted Answer · 2016-10-12T21:33:11.867

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.

Thanks! This works on almost every line item, but (there's always a but)... I did not mention the fact that a sub_event can have duplicates, and what differentiate the first sub event is COLUMN C... how do I add an additional criteria so the calculation only flags the first sub_event. — Marcelo Aguilar, Oct 12 '16 at 20:44
@MarceloAguilar, do you mean you could have a repeated sub_event for one event? If so the column C will flag both sub_events as client events? — alejandro zuleta, Oct 12 '16 at 20:48
Yes... I updated my original question and added an image to illustrate my point. — Marcelo Aguilar, Oct 12 '16 at 20:59
@MarceloAguilar, I've updated my answer adding a new expression that take in account duplicated `Sub_Events`. — alejandro zuleta, Oct 12 '16 at 21:34

Identify first occurence of event based on multiple criterias

1 Answers1