Forum Discussion

JackEnviro's avatar
JackEnviro
Helper I
7 years ago

New Customer by reference in second table

Hi All

 

I currently have a calculated column on my invoices fact table to identify new customers who meet a minimum spend thresholds which is  below.

 

However, our current integration is treating customers who pay on different cards as new contacts when in fact they are returning customers. I would therefore like to add a filter based on customers with the same Postcode.

 

The postcode is stored in a sepreate "Customer" table connected by the ContactID field in the invoice table. Has anyone got any ideas on the best way to apply apply this and identify customers who have ordered with the same postcode before?

 

Thanks 

Jack

 

New Customer =

VAR firstorder =
Calculate ( Min (Invoices[Date]) ,
ALLEXCEPT ( 'Invoices', 'Invoices'[ContactId]),
'Invoices'[ContactId] = EARLIER ( 'Invoices'[ContactId] ), FILTER(Invoices ,Invoices[Full Order] = "Full" ))
 
RETURN
 
IF (Invoices[Full Order] = "Part", "N/A" , IF (firstorder = Invoices[Date], "New" , "Return" ))
Need Help
Show and Tell

5 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi JackEnviro ,

     

    You can create column like DAX below.

     

    New Customer =
VAR firstorder = Calculate ( Min (Invoices[Date]), FILTER(Invoices, Invoices[ContactId] = EARLIER ( Invoices[ContactId] ) ),
FILTER(Customer, Customer[postcode ] = Invoices[ContactId] ), FILTER(Invoices ,Invoices[Full Order] = "Full" ))
RETURN
IF (Invoices[Full Order] = "Part", "N/A" , IF (firstorder = Invoices[Date], "New" , "Return" ))

    Or could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Mariusz's avatar
        Mariusz
        Community Champion
        7 years ago

        Hi JackEnviro 

        I've made a small adjustment to your code, let me know if it works for you.

        New Customer = 
VAR __FirstInvoiceDate = 
CALCULATE( 
    FIRSTDATE( Invoices[Date] ),
    ALL( Invoices ), 
    VALUES( Contacts[Street_PostalCode] )
)
RETURN 
IF (
    Invoices[Full Order] = "Part", "N/A", 
    IF ( __FirstInvoiceDate = Invoices[Date], "New" , "Return" )
)
        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.

        Please feel free to connect with me.
        Mariusz Repczynski