Optimising Measure for Speed

[WillTickel]

I've currently got a sorting measure that sorts different categories and sub-categories into buckets based on how they've grown their cash margin, sales or margin rate over the last year. It currently takes ages to load and I'm convinced it can be optimised so it doesn't take minutes to run. 

This is my measure:

Growth Label =
IF(AND(AND([Cash Margin DIFF]>0,[Net Sales Diff]>0),[Margin Rate Diff]>0),"Growing Everything",
IF(AND(AND([Cash Margin DIFF]>0,[Net Sales Diff]<0),[Margin Rate Diff]>0),"Growing Margin",
IF(AND(AND([Cash Margin DIFF]>0,[Net Sales Diff]>0),[Margin Rate Diff]<0),"Growing Sales",
IF(AND(AND([Cash Margin DIFF]<0,[Net Sales Diff]>0),[Margin Rate Diff]>0),"Logical Error",
IF(AND(AND([Cash Margin DIFF]<0,[Net Sales Diff]>0),[Margin Rate Diff]<0),"Growing Sales, Declining Rate",
IF(AND(AND([Cash Margin DIFF]>0,[Net Sales Diff]<0),[Margin Rate Diff]<0),"Logical Error",
IF(AND(AND([Cash Margin DIFF]<0,[Net Sales Diff]<0),[Margin Rate Diff]>0),"Growing Rate, Declining Sales",
IF(AND(AND([Cash Margin DIFF]<0,[Net Sales Diff]<0),[Margin Rate Diff]<0),"Declining Sales","GAP"))))))))

This is based on a series of intermediate measures (below):

Cash Margin DIFF = [Cash Margin TY]-[Cash Margin LY]

Cash Margin LY = CALCULATE([Cash Margin Measure],FILTER(Data,Data[Year]=MIN(Data[Year])))

Cash Margin TY = CALCULATE([Cash Margin Measure],FILTER(Data,Data[Year]=MAX(Data[Year])))

Cash Margin Measure = SUMX(Data, Cash Margin)

I have no real idea about the principles of how you optimise measures and whether that involves doing what I've done here (creating lots of intermediate measures) or not. 

Any guidance will be much appreciated!

[Elaine_H]

Try using variables and a SWITCH statement instead: 

Growth Label = VAR CashMarginDiffPositive = [Cash Margin DIFF]>0

VAR NetSalesDiffPositive = [Net Sales DIFF]>0

VAR MarginRateDiffPositive = [Margin Rate DIFF]>0

RETURN

SWITCH( TRUE(),AND(AND(CashMarginDiffPositive,NetSalesDiffPositive),MarginRateDiffPositive)=TRUE(),"Growing Everything",

AND(AND(CashMarginDiffPositive,NOT NetSalesDiffPositive),MarginRateDiffPositive)=TRUE(),"Growing Margin",

AND(AND(CashMarginDiffPositive,NetSalesDiffPositive),NOT MarginRateDiffPositive)=TRUE(),"Growing Sales",

AND(AND(NOT CashMarginDiffPositive,NetSalesDiffPositive),MarginRateDiffPositive)=TRUE(),"Logical Error",

AND(AND(NOT CashMarginDiffPositive,NetSalesDiffPositive),NOT MarginRateDiffPositive)=TRUE(),"Growing Sales, Declining Rate",

AND(AND(CashMarginDiffPositive,NOT NetSalesDiffPositive),NOT MarginRateDiffPositive)=TRUE(),"Logical Error",

AND(AND(NOT CashMarginDiffPositive,NOT NetSalesDiffPositive),MarginRateDiffPositive)=TRUE(),"Growing Rate, Declining Sales",

AND(AND(NOT CashMarginDiffPositive,NOT NetSalesDiffPositive),NOT MarginRateDiffPositive)=TRUE(),"Declining Sales","GAP")

[kazael]

Hi @WillTickel

I learned a lot about measure optimization through this video:

https://www.sqlbi.com/tv/power-bi-dax-optimization-examples/

[v-frfei-msft]

Hi @WillTickel,

Please check the blog to find the ways to optimise Measure for Speed.

https://www.sqlbi.com/articles/optimizing-dax-expressions-involving-multiple-measures/

Regards,

Frank