cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seedjay1
Regular Visitor

Customer churn measure - seems correct but can't drill into which specific customers churned???

Hi all,

 

My dataset is a simple star:

Customer

    CustomerId

    LastSaleDate (calculated column at data refresh time, for quicker querying)

 

Calendar

    Date

 

Sales

    Date

    CustomerId

    Amount

 

And my churn measure:

 

Churned Customers =
var ChurnDays = 32
var CurrentDate = min('Sales'[Date])

return
calculate(
    distinctcount(Customer[CustomerId]),
    filter(
        all('Customer'[LastSaleDate]),
        Customer[LastSaleDate] < CurrentDate - ChurnDays
    )
)

 

When I look at this in a matrix grouped by date, this gives me the correct numbers.

 

But when I add CustomerId to the grouping, nothing shows up.

 

What am I missing here, that blocks seeing WHICH customers churned?

 

NoCustomersShow.png

1 ACCEPTED SOLUTION

hi  @seedjay1 

The main problem is:

var CurrentDate = CALCULATE( min('Sales'[OrderDate]),ALL(Territory))

 

you could also use all('Territory'[LastSaleDate]) in the formula too.

 

Regards,

Lin

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

View solution in original post

8 REPLIES 8
v-lili6-msft
Community Support
Community Support

hi  @seedjay1 

What is the relationship among these three tables?

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

It seems there should be something wrong in your data model.

Could you please share your sample pbix file for us have a test? that will be a great help to solve the problem.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Regards,

Lin

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

Hi @v-lili6-msft ,

 

Here's an example made from AdventureWorks data. It slices by Territory rather than Customer, but the concept is as I originally described. (Finally got around to making a public-data example)

 

https://app.powerbi.com/view?r=eyJrIjoiMDIwNWU1NTItM2MyNS00ZTdiLTk4ZjAtY2I4N2NkMmIxMmYzIiwidCI6IjgyM...

 

PBIX: https://1drv.ms/u/s!AgXgj9hQLmm6a2VzONshwgO4_Ug?e=onI8dl

 

Expectation: the first matrix row for 6/13 show drill down to show Central region (as you can see by slicing the chart to Central territory).

 

Pretty sure I'm missing something simple. 🙂

 

Thanks for any suggestions,

cdj

hi  @seedjay1 

For your case, just adjust the formula as below:

Churned Territories = 
var ChurnDays = 32
var CurrentDate = CALCULATE( min('Sales'[OrderDate]),ALL(Territory))

return
calculate(
    distinctcount(Territory[TerritoryID]),
    filter(
        ALLEXCEPT(Territory,Territory[Name]),
        Territory[LastSaleDate] < CurrentDate-ChurnDays
    )
)

Result:

6.JPG

 

 

Regards,

Lin

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

@v-lili6-msft- very excellent, thank you!

 

Question: what's the purpose of the ALLEXCEPT bit in this? It seems like my original filter there combined with your change to the VAR returns the same values/same behavior. What's a situation where the ALLEXCEPT shows its value in this context?

hi  @seedjay1 

The main problem is:

var CurrentDate = CALCULATE( min('Sales'[OrderDate]),ALL(Territory))

 

you could also use all('Territory'[LastSaleDate]) in the formula too.

 

Regards,

Lin

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

View solution in original post

Greg_Deckler
Super User IV
Super User IV

You could always create a measure like this:

 

Churned Customers =
var ChurnDays = 32
var CurrentDate = min('Sales'[Date])

return
CONCATENATEX(
    filter(
        all('Customer'[LastSaleDate]),
        Customer[LastSaleDate] < CurrentDate - ChurnDays
    ),
    [CustomerID],
    ","
)

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Greg,

 

Thanks for the reply.

 

Sorry if I wasn't clear, but I wasn't looking for a different measure that does a different thing.

 

I'm looking for an explanation of why my measure won't slice by dimension values - it's not a problem I've encountered before, so I figure I've mucked up the DAX somehow.

 

Thanks again for the reply,

CDJ

@seedjay1 - It is very difficult to say, would need a way to recreate the issue. Sample data, example PBIX, something like that.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors