Forum Discussion

batmit25's avatar
batmit25
Frequent Visitor
2 years ago
Solved

VALUES() returning blank - Customer Churn

Hi everyone,   I'm trying to work on a Customer Churn Report and as a beginning step I need to classify customers as New,Retained and Lost for every month selected via a slicer. The logic here is t...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi batmit25 ,

    I thought of a possibility that could cause me to run fine but you can't show it. I'm using the latest version of Desktop, but when I open the pbix file you provided, it says that you are using an older version of Desktop, I suspect it's because of the version, you can try to use the newer version of Desktop and try again.

    Best Regards,

    Dino Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi batmit25 ,

You can use the following DAX to create a new column:

Lost Customers = 
IF(NOT ISBLANK([Sales Last Month]) && ISBLANK([Sales This Month]), 1, BLANK())

New Customers = 
IF(ISBLANK([Sales Last Month]) && NOT ISBLANK([Sales This Month]), 1, BLANK())

Retained Customers = 
IF(NOT ISBLANK([Sales Last Month]) && NOT ISBLANK([Sales This Month]), 1, BLANK())

And the final output is shown in the following figure:

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

  • batmit25's avatar
    batmit25
    Frequent Visitor
    2 years ago

    Hi Anonymous ,

     

    The IF conditions are right I believe!

    Could I also know how you were able aggregate the Measures in order to show Total Customers(Lost,New,Retained). I used SUMX alongside but the values returned were blank.

     

    Thank you again :))

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi batmit25 ,
      The total of the previously provided solution is not calculated by DAX, but by the built-in calculation function of Power BI Desktop:

       

      And if you want to calculate the total by DAX, you can try the following DAX to create a new measure:

      Total Lost Customers = COUNTROWS(FILTER('Table','Table'[Lost Customers] = 1))

      Or you can use this DAX as following:

      Total New Customers = 
SUMX(FILTER('Table','Table'[New Customers] = 1),'Table'[New Customers])

      And the final output is shown in the following figure:

      Best Regards,

      Dino Tao

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

       

      • batmit25's avatar
        batmit25
        Frequent Visitor
        2 years ago

        Hi Anonymous,

        The DAX measures work perfectly for Retained and New Customers, However while trying to aggregate the Lost Customers value. I run into the same problem of everything being blank.

        I'm not sure whether it's related to how I modeled my data but if need be I can share my report(Just a trial report). Let me know if there's anything I can add to solve this issue. 

         

        Thanks again much appreciated!