Very Slow Measure

[gjensen]

I'm needing to write a measure that brings back the total value of customers who didn't drop a specific service. I have a measure that does this but it's so slow that it won't be accepted by end users. Below is the visual it will be added to if fixed. It's currently filtered to a specific grouping but can (and needs to be able to) filter to other groupings. Ultimately the measure sees customers that were a part of "total new customers" and not a part of the "Stopped Customers" and then adds up the economic benefits. 

Actual Charges = 
VAR vRunningTotal =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( 'Campaign Member'[NISC__iVUE_Customer_ID__c] ),
            "@StoppedCust", [Stopped Customers]
        )
    )
VAR vActiveCustomers =
    SELECTCOLUMNS (
        FILTER ( vRunningTotal, [@StoppedCust] <> 1),
        "BI_CUST", 'Campaign Member'[NISC__iVUE_Customer_ID__c]
    )
VAR vResult =
    CALCULATE (SUMX(Charges,[Total Charges] ), vActiveCustomers )
RETURN
    vResult

 Stopped Customers looks at the campaign reason code and determines which supporting measure to use. I realize this is likely already an issue but the end user wants to be able to see the different various on the same visual.

Stopped Customers = 
    if(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – Upgrade Internet", [Stopped Gig Customers],
       IF( SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – EV Rate", [Stopped EV Rate Customers],
       if(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – TOU rate", [Stopped TOU Rate Customers],
       IF(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Acquisition – Residential", [Stopped Go Service],
       IF(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – Bank Draft", 0,
       IF(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – Paperless", [Stopped Paperless Customers],
       "")
       )))))

 Visual (hidden page 1) showing the "actual charges" measure works (after a couple of minutes churning). If a customer is flagged for stopping services actual charges is blank.

If there's anything obvious about my code that would be slowing things down please let me know. 

Link to file

https://www.dropbox.com/s/ts9vw19t8otr47i/Marketing%20Campaigns%20Dashboard.pbix?dl=0

[gjensen]

@YukiK @amitchandak 

I'm now wondering if there's a more simple approach. 

I have a measure that calculates economic benefit. If I'm able to just filter out those flagged as "stopped" that should give me what I need.

This is more or less what I'm after but the filter doesn't get recognized. This is just an update for the "Total Charges" measure already in the file. Am I missing anything where the filter is ignored?

Actual Total Charges = 
  CALCULATE(
    SUMX(Charges, [$ First Upgrade Internet Benefit]),
    FILTER(Charges,
        [Stopped Customers] <> 1)
  )

[YukiK]

I'd use SWITCH than nested IF statement. That might not have direct impact on performance but readability is clearer.

Another thing that caught my attention is this:

VAR vResult =
    CALCULATE (SUMX(Charges,[Total Charges] ), vActiveCustomers )

I think you can just do this:

VAR vResult =
    SUMX(vActiveCustomers,[Total Charges] )

 Or this:

VAR vResult =
    CALCULATE([Total Charges], vActiveCustomers )

Also, you can use field parameters for stopped customers

[amitchandak]

@gjensen , check if approach like this can help

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q