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