Forum Discussion
churn
Hi Guys,
Thanks for your continued support.
I have encountered a problem idendifying customers who have bought our subscriptions in one year but not another.
Below is a low level example of my problem.
| customer ID | Subscription ID | Year of Subscription |
| ABC | 1123 | 2015 |
| xyz | 1123 | 2016 |
| mnc | 1123 | 2017 |
| dcs | 1123 | 2015 |
| hjg | 1123 | 2019 |
- I want to identify customers who have purchased a subscription in 2016 and 2017 but not 2018.
- I want to identify customers who have purchased a subscription in 2016 and 2018 but not 2017.
- I want to identify customers who have purchased a subscription in 2017 and 2018 but not 2019.
I have searched online and watched several YouTube videos but haven't been able to solve the problem.
I would appreciate any help any of you kind people could give me. =]
Brendan
Hi AlB,
Thats some sweet DAX code thank you for the prompt reply. Having some issues though. Anything you could do with the following
ListCustomers1 =VAR T2017 = CALCULATETABLE ( VALUES (Renewals[Account_ID]), Renewals[Renewal Year] = 2017 ) VAR T2018 = CALCULATETABLE ( VALUES ( Renewals[Account_ID]), Renewals[Renewal Year] = 2018 ) RETURN (EXCEPT ( T2017, T2018 ))ErrorCalculation error in measure "Renewals"[ListCustomers1]: A table of multiple values was supplied where a single value was expected.Thanks for your help =]
Hi Anonymous
You are trying to squeeze that code into a measure, which does not allow multiple values.
The code generates a one-column table, as I said. Use the "New Table" button under "Modelling" to create a new table and then add the code to define it. You'll see a one-column table with the list of names.
You can then place a table visual (or matrix) in your report and place 'Acount ID' in rows. 'Acount ID' is the only column in the generated table, ListCustomers1.
Does that help?
Anonymous
cool. glad to hear. how about some kudos then? :smileywink:
8 Replies
Hi Anonymous
How about something like this for your requirement number 1. It's a one-column table:
ListCustomers1=
VAR T2016 = CALCULATETABLE ( VALUES ( Table1[Customer ID] ); Table1[Year of Subscription] = 2016 ) VAR T2017 = CALCULATETABLE ( VALUES ( Table1[Customer ID] ); Table1[Year of Subscription] = 2017 ) VAR T2018 = CALCULATETABLE ( VALUES ( Table1[Customer ID] ); Table1[Year of Subscription] = 2018 ) RETURN EXCEPT ( INTERSECT ( T2016; T2017 ); T2018 )You can follow the same pattern for the others. Add the filter for the Subscription ID if you need to.
It could be done with slicers to dynamically determine the years rather than hard-coding them but it gets trickier.
Hi AlB,
Thats some sweet DAX code thank you for the prompt reply. Having some issues though. Anything you could do with the following
ListCustomers1 =VAR T2017 = CALCULATETABLE ( VALUES (Renewals[Account_ID]), Renewals[Renewal Year] = 2017 ) VAR T2018 = CALCULATETABLE ( VALUES ( Renewals[Account_ID]), Renewals[Renewal Year] = 2018 ) RETURN (EXCEPT ( T2017, T2018 ))ErrorCalculation error in measure "Renewals"[ListCustomers1]: A table of multiple values was supplied where a single value was expected.Thanks for your help =]Hi Anonymous
You are trying to squeeze that code into a measure, which does not allow multiple values.
The code generates a one-column table, as I said. Use the "New Table" button under "Modelling" to create a new table and then add the code to define it. You'll see a one-column table with the list of names.
You can then place a table visual (or matrix) in your report and place 'Acount ID' in rows. 'Acount ID' is the only column in the generated table, ListCustomers1.
Does that help?