Forum Discussion

srl01's avatar
srl01
Helper II
8 years ago

Flagging a customer's second visit

Hello -    I have a table of raw sales transaction data (one line per item, multiple lines for a transaction, with a unique transaction code for each transaction).   Date Transaction code Cu...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Try this calculated column formula

 

=CALCULATE(DISTINCTCOUNT(Data[Date]),FILTER(Data,Data[Customercode]=EARLIER(Data[Customercode])&&Data[Date]<EARLIER(Data[Date])))

 

Hope this helps.

 

  • srl01's avatar
    srl01
    Helper II
    8 years ago

    Hello Ashish - thank you!

    I've plugged that formula in but it is taking a very long time to calculate as a column formula (data is about 5 million rows, so that is a lot of filtering and counting going on). 20 minutes on an i7-7700k and it doesn't seem to be anywhere close to finished.

    Any thoughts on how to make this work with a measure instead that could be less computationally intensive?

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      In your original post, you mentioned that you wanted a calculated column formula.  Now you want a measure.  Share the link from where i can download your trimmed data (the file shize should only be a couple of MB's please) and share the expected result.

      • srl01's avatar
        srl01
        Helper II
        8 years ago

        Hi Ashish

         

        Apologies - let me rephrase then as I do indeed want a calculated column formula, but not if it takes hours to calculate. (The model has now been running for 3 hours and is still not done). 

         

        Is there an alternative to your suggestion above (either a calculated column, or a measure) that will be computationally efficient here? 

         

        For example... first creating a calculated column in the CustomerMaster table (meaning one calc for each of 1mil customers, rather than one for each of 5-10mil transaction lines) that looks for the earliest transaction date of that customer after their very first transaction. Then the calculated column in the SalesRawData is just a simple lookup to the CustomerMaster table to see if the date matches.

         

        If that concept makes sense (or any other ideas) I'll gather an extract of the appropriate data and post. Thank you!