Forum Discussion

CAPEconsulting's avatar
CAPEconsulting
Helper III
6 years ago
Solved

First and Last Purchase

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 ?
Need Help
Show and Tell
  • CAPEconsulting's avatar
    CAPEconsulting
    6 years ago

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

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    6 years ago

    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's avatar
      CAPEconsulting
      Helper III
      6 years ago

      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's avatar
        CAPEconsulting
        Helper III
        6 years ago

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