Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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 ))
     
    Error
     
    Calculation error in measure "Renewals"[ListCustomers1]: A table of multiple values was supplied where a single value was expected.
     
    Thanks for your help =]

     

  • AlB's avatar
    AlB
    7 years ago

    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?    

  • AlB's avatar
    AlB
    7 years ago

    Anonymous

    cool. glad to hear. how about some kudos then? :smileywink:

AlB's avatar
AlB
Community Champion
7 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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 ))
     
    Error
     
    Calculation error in measure "Renewals"[ListCustomers1]: A table of multiple values was supplied where a single value was expected.
     
    Thanks for your help =]

     

    • AlB's avatar
      AlB
      Community Champion
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi AlB,

         

        I'm out of the office now, but yeah that makes sense. My colleague and I were trawling through all of the online documentation with no success. I'll put it down to it being Friday. =]

         

          Thank you so much for your help.