First and Last Purchase

[CAPEconsulting]

I am trying to create a calculated column that marks a "First" and "Last" purchase of a customer by product where reason is not 2 (reason 1 was initiation in the store and reason 3 was final transfer to another store) and score is not invalid (999). This is the sample table structure

My calculateed column is called Status and is calculated in DAX as

Status =

VAR

First = CALCULATE(MIN(O[Date]), ALLEXCEPT(O,O[CustID], O[Type]), FILTER(SUMMARIZE(O, O[Score], O[reason]), O[reason] = 1 && O[Score] <> 999))

VAR

Last = CALCULATE(MAX(O[Date]), ALLEXCEPT(O,O[CustID], O[Type]), FILTER(SUMMARIZE(O, O[Score], O[reason]), O[reason] = 3 && O[Score] <> 999))

RETURN

IF(O[Date] = First, "Pre", IF(O[Date] = Last, "Post", BLANK()))

It is working just fine for all the dataset except for the above scenario where the max date with a reason = 3 was not the overall max date for the customer. I can visually see that the row of date 5/12/17 should come up as the Post flag, but it still comes as blank. Why ??

Any suggestions ?

[v-piga-msft]

Hi @CAPEconsulting ,

For your requirement, please try the measure below.

Measure =
VAR first =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[CustID], 'Table'[Type] ),
            'Table'[Reason] = 1
                && 'Table'[Score] <> 999
        )
    )
VAR last =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[CustID], 'Table'[Type] ),
            'Table'[Reason] = 3
                && 'Table'[Score] <> 999
        )
    )
RETURN
    IF (
        MIN ( 'Table'[Date] ) = First,
        "Pre",
        IF ( MAX ( 'Table'[Date] ) = Last, "Post", BLANK () )
    )

Here is the output.

Hope this can help you.

Best Regards,

Cherry

[CAPEconsulting]

Hey @v-piga-msft Thanks for that , So conceptually using FILTER before the ALLEXCEPT and having all FILTER statements in one is what you are proposing. While I think it will (and does) work, my undertstanding from @marcorusso  and @AlbertoFerrari  is that for large datasets filter should only be applied to relevant columsn through VALUES or SUMMARIZE functions and hence I had SUMMARIZE in there and seperated the ALLEXCEPT statemnet as it's own filter argument. Any suggestions of how this could be done for large data sets and still have great performance @OwenAuger  , @marcorusso  and @AlbertoFerrari  any suggestions ?

[CAPEconsulting · Accepted solution]

Actually, looks like I must have mistyped something somewhere, as my initial syntax works just fine - and will be perfect for large datasets too

Status =

VAR

First = CALCULATE(MIN(O[Date]), ALLEXCEPT(O,O[CustID], O[Type]), FILTER(SUMMARIZE(O, O[Score], O[reason]), O[reason] = 1 && O[Score] <> 999))

VAR

Last = CALCULATE(MAX(O[Date]), ALLEXCEPT(O,O[CustID], O[Type]), FILTER(SUMMARIZE(O, O[Score], O[reason]), O[reason] = 3 && O[Score] <> 999))

RETURN

IF(O[Date] = First, "Pre", IF(O[Date] = Last, "Post", BLANK()))