Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Temminck
Advocate I
Advocate I

Find aborted customers based on several conditions

Hi there,

 

I need to detect customers having issues to register for a product.

 

To find the customers having issues I thought of adding a calculated column in my product table. However maybe a measure would be better? I don’t know… please help

 

Here an example product table with 6 customers:

Order Id

Order Status

ContactId

CatalogType

CatalogName

ProductLabel

OrderDate

Calculated column:

Needs help?

1

Paid

65445

Membership

Membership

 Membership 2018-2019

01/01/2018

no

2

Paid

65445

Membership

Membership

 Membership 2020-2020

01/01/2020

no

3

Aborted

65445

Membership

Membership

 Membership 2021-2022

06/08/2020

no

4

Order

65445

Membership

Membership

 Membership 2021-2021

07/08/2020

no

5

Order

65445

Membership

Membership

 Membership 2021-2021

07/08/2020

no

6

Paid

45894

Membership

Membership

 Membership 2018-2019

01/01/2018

no

7

Paid

45894

Membership

Membership

 Membership 2020-2020

01/01/2020

no

8

Aborted

45894

Membership

Membership

 Membership 2021-2021

07/24/2020

yes

9

Aborted

45894

Membership

Membership

 Membership 2021-2021

07/25/2020

yes

10

Aborted

45894

Membership

Membership

 Membership 2021-2021

07/28/2020

yes

11

Paid

45689

Events

Parties

Party45

08/09/2020

no

12

Aborted

89564

Events

Parties

Party45

10/12/2020

yes

13

Aborted

77895

Exam

Exam 2020

Milan 3/10/2021

01/10/2021

yes

14

Aborted

77895

Exam

Exam 2020

Paris 4/11/2021

01/10/2021

yes

15

Aborted

66658

Exam

Exam 2021

Avignon 3/10/2021

01/10/2021

No

16

Aborted

66658

Exam

Exam 2021

Dubai 4/11/2021

01/10/2021

No

17

Paid

66658

Exam

Exam 2021

Berlin 5/01/2021

01/10/2021

no

 

If the customer failed one or more times to purchase a product we need to intervene (= OrderStatus aborted).   

 

There are 4 scenarios when customers purchase a product:

  1. The customer failed one or more times (aborted)
  2. The customer paid successfully after the first time (paid)
  3. The customer failed one or more times and then managed to pay successfully (aborted, paid)
  4. The customer failed one or more times and has one or several open orders (aborted, order)

 

Additionally, when customers fail to purchase the product the first time and try again it regularly happens that they chose a different productlabel but in the same catalogtype. This only happens for the catalogtypes Membership and Exam.

 

In summary, we need to find customers where :

EVENT: customer has one or more times status aborted for the catalogtype ‘Event’ and the same ‘ProductLabel’ 

EXAM: customer has one or more times status aborted for the catalogtype 'Exam‘ and same 'CatalogName’

MEMBERSHIP: customer has one or more times status aborted for the for the catalogtype ‘Membership‘ and ‘ProductLabel’ where StartYear <= SelectedYear &&  EndDate >= SelectedYear

 

Someone knows how to create such calculated column or measure?

Please let me know if it’s not clear enough.

0 REPLIES 0

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors