Optimizing DAX performance

[Anonymous]

Hi All,

I have a DAX measure that is taking too long for calculations. I was hoping if some one could help me optimize the code.

Sample code:

Measure = 
Var var1 = some calculation
Var var 2 = some calculation
... 9 such variables

Var var10 = VALUES(user selection)

Var var11 = CALCULATE ( var1 * some value, columnvalue = "a")
Var var12 = CALCULATE ( var2 * some value, columnvalue = "b")

... 9 such variables

Notes:
some value calculations are based on the filters applied in each calculate statement.

Looking forward to some exciting solutions.

PS: This is my first question in the forum. Please excuse me in case I have missed out on some guidance.

Thanks

Udit

[Mariusz]

Hi @Anonymous 

Sure you can use DAX Studio to optimize your DAX

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

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

@MariuszI already used dax studio and found out that this particular code is taking too long to execute. Everything else is fine. So thought of asking if there is a way I can restructure my code to make it more effectie.

[Anonymous]

Yeah, there is a way, albeit quite involved.

The first thing to do is to... learn how to structure models correctly so that DAX is speedy.

The second thing to do is to learn how SWITCH/IF is calculated.

And the best thing to do is to take the book "The Definitive Guide to DAX" and read it in full. Especially the part that deals with optimization.

Best
D

[Anonymous]

One more thing... Without knowing what the model looks like and what the measure should do there is no way one could effectively optimize this measure.

Best
D

[Greg_Deckler]

Also, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Greg_Deckler]

Really need to see all the code. You can probably ditch the VALUES. See these posts:

• https://community.powerbi.com/t5/Community-Blog/Soapbox-Series-Adding-No-VALUE-S/ba-p/1059818
• https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275
• https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-2/ba-p/976813

[Anonymous]

Hi Greg,

I have posted my code below. This is what I am trying to achieve. let me know if this helps.

Target Stock =
Var axservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level AX])/100),0)
Var ayservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level AY])/100),0)
Var azservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level AZ])/100),0)
Var bxservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level BX])/100),0)
Var byservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level BY])/100),0)
Var bzservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level BZ])/100),0)
Var cxservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level CX])/100),0)
Var cyservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level CY])/100),0)
Var czservicelevel = IFERROR(NORM.S.INV(MAX(Parameters[Service Level CZ])/100),0)
Var boxtypeselection = VALUES('9 Box Type Selection'[Value])
Var targettypeselection = VALUES('Target Stock Selection'[Value])
var ax =
CALCULATE(
axservicelevel
*
SQRT(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
    POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
    IF(targettypeselection=2,
    POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
    *
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2),0)
)
+
    DIVIDE(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(ABC_Sales,ABC_Sales[ABC Analysis]="A"),
FILTER(XYZ_Sales,XYZ_Sales[XYZ Analysis]="X"))

var ay =
CALCULATE(
ayservicelevel
*
SQRT(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
    POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
    IF(targettypeselection=2,
    POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
    *
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2),0)
)
+
    DIVIDE(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(ABC_Sales,ABC_Sales[ABC Analysis]="A"),
FILTER(XYZ_Sales,XYZ_Sales[XYZ Analysis]="Y"))

var az =
CALCULATE(
azservicelevel
*
SQRT(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
    POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
    IF(targettypeselection=2,
    POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
    *
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2),0)
)
+
    DIVIDE(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(ABC_Sales,ABC_Sales[ABC Analysis]="A"),
FILTER(XYZ_Sales,XYZ_Sales[XYZ Analysis]="Z"))

var bx =
CALCULATE(
bxservicelevel
*
SQRT(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
    POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
    IF(targettypeselection=2,
    POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
    *
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2),0)
)
+
    DIVIDE(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(ABC_Sales,ABC_Sales[ABC Analysis]="B"),
FILTER(XYZ_Sales,XYZ_Sales[XYZ Analysis]="X"))

var bby =
CALCULATE(
byservicelevel
*
SQRT(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
    POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
    IF(targettypeselection=2,
    POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
    *
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2),0)
)
+
    DIVIDE(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(ABC_Sales,ABC_Sales[ABC Analysis]="B"),
FILTER(XYZ_Sales,XYZ_Sales[XYZ Analysis]="Y"))

var bz =
CALCULATE(
bzservicelevel
*
SQRT(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
    POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
    IF(targettypeselection=2,
    POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
    *
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2),0)
)
+
    DIVIDE(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(ABC_Sales,ABC_Sales[ABC Analysis]="B"),
FILTER(XYZ_Sales,XYZ_Sales[XYZ Analysis]="Z"))

var cx =
CALCULATE(
cxservicelevel
*
SQRT(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
    POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
    IF(targettypeselection=2,
    POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
    *
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2),0)
)
+
    DIVIDE(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(ABC_Sales,ABC_Sales[ABC Analysis]="C"),
FILTER(XYZ_Sales,XYZ_Sales[XYZ Analysis]="X"))

var cy =
CALCULATE(
cyservicelevel
*
SQRT(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
    POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
    IF(targettypeselection=2,
    POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
    *
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2),0)
)
+
    DIVIDE(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(ABC_Sales,ABC_Sales[ABC Analysis]="C"),
FILTER(XYZ_Sales,XYZ_Sales[XYZ Analysis]="Y"))

var cz =
CALCULATE(
czservicelevel
*
SQRT(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    POWER(sum(XYZ_Sales[Revenue_Monthly_Std]),2),
    POWER(sum(XYZ_Sales[Quantity_Monthly_Std]),2))
+
    IF(targettypeselection=2,
    POWER(AVERAGE('Lead Time Data'[Lead_Time_Deviation])
    *
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2),0)
)
+
    DIVIDE(
    DIVIDE(IF(OR(boxtypeselection=1,boxtypeselection=2),
    AVERAGE('Lead Time Data'[Lead_Time]),
    AVERAGE('Manual Lead Time'[Lead_Time])),30,0)
*
    IF(OR(boxtypeselection=1,boxtypeselection=3),
    SUM(XYZ_Sales[Revenue_Monthly_Avg]),
    SUM(XYZ_Sales[Quantity_Monthly_Avg]))
    ,2)
,
FILTER(Coverage,Coverage[MTX Class]="MTS"),
FILTER(Coverage,Coverage[ABC Classification]="C"),
FILTER(Coverage,Coverage[XYZ Classification]="Z"))

Var Val =
switch(max('9-Box'[Class]),
"AX",ax,
"AY",ay,
"AZ",az,
"BX",bx,
"BY",bby,
"BZ",bz,
"CX",cx,
"CY",cy,
"CZ",cz,
(ax + ay + az + bx + bby + bz + cx + cy + cz))
return if(isblank(Val),0,Val)
*
SWITCH(TRUE(),OR(VALUES('9 Box Type Selection'[Value])=1,VALUES('9 Box Type Selection'[Value])=3),
[Rate],
OR(VALUES('9 Box Type Selection'[Value])=3,VALUES('9 Box Type Selection'[Value])=4),
1,1)

[Anonymous · Accepted solution]

So I was able to shorten thi block using SUMX(VALUES(Filter condition column), SUM()) and it worked.

Thanks for the help.