A data analyst is building a Power BI model to track customer churn. The model includes a 'Customers' table and a 'Subscriptions' table. The 'Subscriptions' table contains 'CustomerID', 'StartDate', and 'EndDate'. A customer is considered churned if their 'EndDate' is in the past and there is no subsequent 'StartDate' for that customer after their 'EndDate'. The analyst needs to create a measure that counts the number of churned customers. Which DAX pattern would be most appropriate for identifying and counting churned customers?
- A`VAR ChurnedCustomers = FILTER(VALUES(Customers[CustomerID]), VAR LastEndDate = MAXX(FILTER(Subscriptions, Subscriptions[CustomerID] = Customers[CustomerID]), Subscriptions[EndDate]) RETURN LastEndDate < TODAY() && COUNTROWS(FILTER(Subscriptions, Subscriptions[CustomerID] = Customers[CustomerID] && Subscriptions[StartDate] > LastEndDate)) = 0) RETURN COUNTROWS(ChurnedCustomers)`
- B`COUNTROWS(FILTER(Customers, NOT ISEMPTY(FILTER(Subscriptions, Subscriptions[EndDate] < TODAY() && CALCULATE(COUNTROWS(Subscriptions), ALLEXCEPT(Subscriptions, Subscriptions[CustomerID]), Subscriptions[StartDate] > EARLIER(Subscriptions[EndDate])) = 0))))`
- C`CALCULATE(DISTINCTCOUNT(Subscriptions[CustomerID]), FILTER(Subscriptions, Subscriptions[EndDate] < TODAY() && NOT CONTAINS(Subscriptions, Subscriptions[CustomerID], Subscriptions[CustomerID] && Subscriptions[StartDate] > EARLIER(Subscriptions[EndDate]))))`
- D`SUMX(Customers, IF(MAXX(FILTER(Subscriptions, Subscriptions[CustomerID] = Customers[CustomerID]), Subscriptions[EndDate]) < TODAY() && CALCULATE(COUNTROWS(Subscriptions), ALLEXCEPT(Subscriptions, Customers[CustomerID]), Subscriptions[StartDate] > MAXX(FILTER(Subscriptions, Subscriptions[CustomerID] = Customers[CustomerID]), Subscriptions[EndDate])) = 0, 1, 0))`
Show answer & explanationAnswer & explanation
Correct answer: A. `VAR ChurnedCustomers = FILTER(VALUES(Customers[CustomerID]), VAR LastEndDate = MAXX(FILTER(Subscriptions, Subscriptions[CustomerID] = Customers[CustomerID]), Subscriptions[EndDate]) RETURN LastEndDate < TODAY() && COUNTROWS(FILTER(Subscriptions, Subscriptions[CustomerID] = Customers[CustomerID] && Subscriptions[StartDate] > LastEndDate)) = 0) RETURN COUNTROWS(ChurnedCustomers)`
Option C correctly identifies churned customers by iterating through each customer, finding their latest subscription end date, checking if it's in the past, and then verifying if there are no subsequent subscriptions. The use of VAR to store intermediate results and `COUNTROWS` on the filtered set of customers is an efficient and readable pattern for this complex logic.
Why the other options are wrong
- B. This uses nested `FILTER` and `ISEMPTY` with `ALLEXCEPT` in a less direct way for the second condition, which can be less efficient and harder to debug.
- C. The `CONTAINS` function is not suitable for complex row-context filtering and comparison like this; it checks for existence of a row with specific values.
- D. The `SUMX` over customers with an `IF` statement is a valid approach, but the nested `MAXX` and `CALCULATE` with `ALLEXCEPT` for the second condition can be less performant and harder to interpret than the `VAR` and `COUNTROWS` approach in option C for this specific scenario.
DAX Churn Detection Pattern
A DAX pattern for identifying customer churn by comparing subscription end dates with current date and absence of future subscriptions.
- Requires identifying the latest subscription end date for each customer.
- Needs to check if this latest end date is in the past.
- Must verify that no new subscriptions exist after the latest end date.
Memory trick: Last End Past, No Next Start, Then Churn is Fast.