Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 =
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.
Hi Amy / @v-xicai
Thanks for your help. I tried your suggestion but it doesn't identify any new customers.
I have prepared a file to show the tables and relationships:
https://drive.google.com/file/d/1Oh4GnRM4o4KeCLfTtEZEsxOpY4xWal62/view?usp=sharing
Thanks
Jack
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" ) )
Hi
Thanks for looking, I tried the code but I don't think the logic is correct I'm afraid.
I need the "FirstOrder" to be the first order from that ContactID unless the postcode has been used before. I may have got it wrong so please do talk me through your code if you thinkI have.
I am wondering if I could create a column that uses the ContactID unless the postcode has been used before ? Or posssibly a check that if the postcode has been seen before the order is from a return customer possibly like below:
New Customer =
Still working on it but any ideas welcome!
Hi All
I think I have a solution but it feels a little complicated.....
I have created a calculated column to bring throught the Postcode to the Invoices table:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |