Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Optimizing DAX performance

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...
DAX Performace
Measures
Optimizing DAX
  • Anonymous's avatar
    Anonymous
    6 years ago

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

     

    Thanks for the help.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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

 

Thanks for the help.