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

[seedjay1]

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?

[v-lili6-msft]

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

[seedjay1]

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

[v-lili6-msft]

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:

Regards,

Lin

[seedjay1]

@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?

[v-lili6-msft · 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

[Greg_Deckler]

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],
    ","
)

[seedjay1]

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

[Greg_Deckler]

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