Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
hi @seedjay1
The main problem is:
you could also use all('Territory'[LastSaleDate]) in the formula too.
Regards,
Lin
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
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)
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:
Regards,
Lin
@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:
you could also use all('Territory'[LastSaleDate]) in the formula too.
Regards,
Lin
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],
","
)
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |