Improving efficiency of a large measure

[GunnerJ]

I have two measures that have to go through several "IF" statements to know which measure to use. They then use SUMX to get a grand total but as you can see below the dashboard actually just times out. My request to the community is to provide a more effecient way to get these grand totals or to limit the data. At the moment I'm a bit stuck on this issue.

Summed Monthly Econ Benefit = 
   SUMX('Campaign Member', CALCULATE(
    if(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – Upgrade Internet", [Total Charges],
       IF( SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – Paperless", 'Paperless Updates'[$ Monthly Paperless Benefit],
       IF(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – Bank Draft", [$ Monthly Bank Draft Benefit],
       IF(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Acquisition – Residential", [Total First Go Charges],
       IF( SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – EV Rate", [Monthly EV Benefit],
       IF(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – TOU rate", [Monthly TOU Benefit],
       "")
       ))))),
       FILTER(Campaign,
               Campaign[StartDate] >= DATE(2021, 7,29)
   )))

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

[aj1973]

Hi @GunnerJ 

Add Variable to you measure

In another context, your model is way from being the best you can optimise, you need to better optimise it.

[GunnerJ]

@aj1973 thank you for the quick reply. With the variable I can only get a value to show if I select a single campaign. You are absolutely correct it needs to be optimized more and I was simply hoping to be fortunante that someone would see a "quick" fix to help improve overall performance. 

[aj1973]

It's normal to get values for the selected campaign because you're using SELECTEDVALUE in your measure.

To optimize your model it would need more than a quick fix on the fly, sorry 😔

[GunnerJ]

Thank you for the quick reply! I will continue to see if there's any way to choose between different measures to try and speed things up but I do appreciate the effort to help!