Check if all rows for a certain customer are marked as "churned"

[Laila92]

I have a dataset where one customer can have multiple products. He can be live on certain products, but churned on other products.
He will only be marked as a churned customer if he is churned from all products. 
How can I loop through the product status of each customer with DAX, checking if all of the statuses are lost, and then counting these up so I have the number of churned customers?
Example data:
Customer ID   Product ID    Status

1                      1                   Live

1                      2                   Lost
2                      1                   Lost

2                      2                   Lost

3                      1                   Live

3                      2                   Lost

The count I would want for the dataset above is 1, for customer 2. The other ones have Live deals so are not churned.

I tried using COUNTX/COUNTAX but to no avail, for example:

Churned Customers = COUNTX( FILTER('Deal Status','Deal Status'[Deal - Status] = "Lost"),'Deal Status'[Deal - Status])

[Greg_Deckler]

Try this:

Measure = 
    VAR __Table = SUMMARIZE('Table9',[Customer ID],"TotalCount",COUNTX('Table9',[Product ID]),"LostCount",COUNTX(FILTER('Table9',[Status]="Lost"),[Product ID]))
RETURN
    COUNTROWS(FILTER(__Table,[TotalCount] = [LostCount]))

[Anonymous]

Hi @Laila92 

Try this measure,

ChurnedCustomer =

VAr Live_count=CALCULATE(DISTINCTCOUNT(Sheet1[Prod ID]),FILTER(Sheet1,Sheet1[Status]="Live"))

Var Total=DISTINCTCOUNT(Sheet1[Prod ID])

return

Total-Live_count

If i solve your problem mark it as solution and give kudoes.

Thanks & regards,

Pravin Wattamwar

www.linkedin.com/in/pravin-p-wattamwar

[Laila92]

Your formula calculates the churns on deal level, not on customer level. I need the total count of customers for whom all deals are marked as churn. 

[Anonymous]

its on customer level only.

In my data set i have taken customer as product ID and prod id as ID. Just rename the columns.

Thanks,

Pravin Wattamwar

[Anonymous · Accepted solution]

This will be formula as per your data set.

ChurnedCustomer =

VAr Live_count=CALCULATE(DISTINCTCOUNT(Sheet1[Customer ID]),FILTER(Sheet1,Sheet1[Status]="Live"))

Var Total=DISTINCTCOUNT(Sheet1[Customer ID])

return

Total-Live_count

Regards,

Pravin Wattamwar

[Laila92]

Thank you, but this is not giving me back entirely what I need. This formula returns a count also when a client has churned on one product, but is live on another. How can I filter out the clients that do have a churn on one product but are live on another?
Example of the data where it goes wrong:

Product ID           Client          Lost Date             Deal status
1                           1                 August 23,2018   Churned
2                           1                                             Live
3                           1                                             Live

This should not give me back a count as churned customer, but it did.