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 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:
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.
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 |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |