Forum Discussion

buttercream's avatar
buttercream
Icon for Helper II rankHelper II
1 year ago
Solved

Flag consecutive values based on multiple columns

Hi all,   Need your help.  I'm trying to flag customers that made multiple purchases in the same day.  The purchase ID must be within 3 numbers of each other.  So the same customer on the same day ...
Need Help
  • MasonMA's avatar
    MasonMA
    1 year ago

    Hi buttercream 

     

    Consider building a new Column in your table with below:

     

    VAR ThisCustomer = Purchases[Customer]
    VAR ThisDate = Purchases[Date]
    VAR ThisID = Purchases[Purchase ID]
    VAR OtherPurchases =
        FILTER (
            Purchases,
            Purchases[Customer] = ThisCustomer &&
            Purchases[Date] = ThisDate &&
            ABS ( Purchases[Purchase ID] - ThisID ) <= 3 &&
            Purchases[Purchase ID] <> ThisID
        )
    RETURN
    IF ( COUNTROWS ( OtherPurchases ) > 0, "Yes", "No" )
     

     

MasonMA's avatar
MasonMA
Icon for Super User rankSuper User
1 year ago

Hi buttercream 

 

Consider building a new Column in your table with below:

 

VAR ThisCustomer = Purchases[Customer]
VAR ThisDate = Purchases[Date]
VAR ThisID = Purchases[Purchase ID]
VAR OtherPurchases =
    FILTER (
        Purchases,
        Purchases[Customer] = ThisCustomer &&
        Purchases[Date] = ThisDate &&
        ABS ( Purchases[Purchase ID] - ThisID ) <= 3 &&
        Purchases[Purchase ID] <> ThisID
    )
RETURN
IF ( COUNTROWS ( OtherPurchases ) > 0, "Yes", "No" )
 

 

  • buttercream's avatar
    buttercream
    Icon for Helper II rankHelper II
    1 year ago

    Perfect.  Thank you !!!