Optimizing DAX performance

Anonymous — Wed, 13 May 2020 05:36:22 GMT

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

Re: Optimizing DAX performance

Greg_Deckler — Wed, 13 May 2020 06:12:43 GMT

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

Re: Optimizing DAX performance

Mariusz — Wed, 13 May 2020 07:39:19 GMT

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

Re: Optimizing DAX performance

Anonymous — Wed, 13 May 2020 13:18:01 GMT

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)

Re: Optimizing DAX performance

Anonymous — Thu, 14 May 2020 07:35:58 GMT

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.

Re: Optimizing DAX performance

Anonymous — Thu, 14 May 2020 08:32:13 GMT

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

Re: Optimizing DAX performance

Anonymous — Thu, 14 May 2020 08:36:06 GMT

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

Re: Optimizing DAX performance

Anonymous — Tue, 02 Jun 2020 12:53:44 GMT

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

Thanks for the help.

topic Re: Optimizing DAX performance in DAX Commands and Tips

Optimizing DAX performance

Re: Optimizing DAX performance

Re: Optimizing DAX performance

Re: Optimizing DAX performance

Re: Optimizing DAX performance

Re: Optimizing DAX performance

Re: Optimizing DAX performance

Re: Optimizing DAX performance

Re: Optimizing DAX performance