cancel
Showing results for
Did you mean:
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?

1 ACCEPTED SOLUTION
Community Support

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.
8 REPLIES 8
Community Support

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.

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.
Regular Visitor

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...

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

Community Support

``````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

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.
Regular Visitor

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

Community Support

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

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

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

Proud to be a Super User!

Regular Visitor

Hi Greg,

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.

CDJ

Super User IV

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

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

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

Proud to be a Super User!

Announcements