Optimizing a measure

[michael_knight]

Hi,

I've got a measure and it's performing terribly in the Performance Analyzer so I'm wondering if anyone can help in optimzing the measure?

This is how I got the measure, there are quite a few steps in order to get to the final measure. Is there a way to condense them into only a couple measures?

Lost Leads Count = SUM('Lost Leads'[Lost Leads]) 

Lost Leads = 
CALCULATE('Lost Leads'[Lost Leads Count], 
USERELATIONSHIP('Lost Leads'[Date], 'Full Date'[Full Date]))


Leads (WBAH)  - Count = COUNTROWS(FILTER(Opportunity, Opportunity[Date Last Assigned] && 
Opportunity[Company] <> "Test 1" &&
Opportunity[Company] <> "Test 2" && 
Opportunity[Full Name - WBAH] <> "Person1" &&
Opportunity[Full Name - WBAH] <> "Person2" &&
Opportunity[Full Name - WBAH] <> "Person3" &&
Opportunity[Full Name - WBAH] <> "Person4" &&
Opportunity[new_phonenumber] <> "MISTAKE NOV" && 
Opportunity[Disqualified Reason] <> "Duplicate Record"
)) + 'Lost Leads'[WBAH Lost Leads]


Leads = 
CALCULATE([Leads (WBAH)  - Count], 
FILTER (Opportunity, Opportunity[Created On] >= DATE(SELECTEDVALUE('Full Date'[Fiscal]),4,1)), 
USERELATIONSHIP('Opportunity'[Date Last Assigned], 'Full Date'[Full Date]))


Leads - Board Pack Count = var leadsboardpack = SUM('Board Pack'[Leads Count]) 
return
leadsboardpack + [Leads]


Leads - Board Pack = 
CALCULATE([Leads - Board Pack Count], 
FILTER (Opportunity, Opportunity[Created On] >= DATE(SELECTEDVALUE('Full Date'[Fiscal]),4,1)), 
USERELATIONSHIP('Board Pack'[Date], 'Full Date'[Full Date]))

[v-alq-msft]

Hi, @michael_knight 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[michael_knight · Accepted solution]

Hi @v-alq-msft 

Apologies for the late reply. I figured this problem out myself. I simple cut down on the amount of VARs I was using, I created a seperate measure and referred to that in the new measure rather than referring to the VAR. That seemed to help with the optimization of some of the measure 

Thanks for the reply, much appreciated! 

Mike

[v-alq-msft]

Hi, @michael_knight 

Based on my research, I'd like to suggest you use "NOT in" and use variables instead of repeating fields.

Here is my example.

You may create a measure as follows. It is inefficent.

Measure 1 = 
IF(
    MAX('Table'[Class])<>"c1"&&MAX('Table'[Class])<>"c2", 
    1,0
)

You can use the modified measures.

Measure 2 = 
var _class =  MAX('Table'[Class]) 
return
IF(
    _class<>"c1"&&_class<>"c2", 
    1,0
)

Measure 3 = 
IF(
    NOT MAX('Table'[Class]) in {"c1","c2"}, 
    1,0
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.