I need help to optimize a report when i check the report performance by Performance Analyzer i majorly get the chunks for DAX and it's very hard to optimize the dax i have tried several things but it didn't work out. Can someone help me with the below DAX optimization.
P&L PY % Test =
var CurrentItem = SELECTEDVALUE('Temp'[Items Normalized])
var CurrentSource = SELECTEDVALUE('Temp'[Source])
var CurrentType = SELECTEDVALUE('Temp'[Type])
var SourceType = CONCATENATE(CurrentSource, CurrentType)
var RLI_Total =
[vs PY%]
var RLI_CNPY =
[Amount CNPY]
var RLI_PY =
[Amount USD PY]
var PerCSE_CNPY =
CALCULATE(
[Amount CNPY],
All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="CSEs"
)
var perCSE_PY=
CALCULATE(
[Amount USD PY],
All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="CSEs"
)
var NetRev_CNPY=
CALCULATE(
[Amount CNPY],
All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="Net Revenues"
)
var NetRev_PY=
CALCULATE(
[Amount USD PY],
All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="Net Revenues"
)
var Opex =
DIVIDE((CALCULATE(
[Amount CNPY],All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="SG&A"
)
+
CALCULATE(
[Amount CNPY],All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="Other Op Exp"
)),(CALCULATE(
[Amount USD PY],ALL( DimPMRAccount_Rollup),All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="SG&A"
)
+
CALCULATE(
[Amount USD PY],All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="Other Op Exp"
)),0)-1
var Non_OP =
DIVIDE((CALCULATE(
[Amount CNPY],All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="Net Interest"
)
+
CALCULATE(
[Amount CNPY],All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="Other Income"
)
+
CALCULATE(
[Amount CNPY],All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="Equity Income (Loss)"
)),(CALCULATE(
[Amount USD PY],All(Temp[Items Short]),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="Net Interest"
)
+
CALCULATE(
[Amount USD PY],All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="Other Income"
)
+
CALCULATE(
[Amount USD PY],All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="Equity Income (Loss)"
)),0)-1
Var UCS_ADS=DIVIDE(DIVIDE(CALCULATE(
[Amount CNPY],All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="UCS"
), [CD],0), DIVIDE(CALCULATE(
[Amount USD PY],All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="UCS"
), [CD PY],0),0) - 1
Var Opex_cse= DIVIDE(DIVIDE(CALCULATE(
[Amount CNPY],All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="SG&A",
ALL( DimPMRAccount_Rollup)
)
+
CALCULATE(
[Amount CNPY],All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="Other Op Exp",
ALL( DimPMRAccount_Rollup)
),PerCSE_CNPY,0),
DIVIDE(CALCULATE(
[Amount USD PY],All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="SG&A",
ALL( DimPMRAccount_Rollup)
)
+
CALCULATE(
[Amount USD PY],All(Temp),
DimPMRAccount_Rollup[ACCOUNT_MR_LVL]="Other Op Exp",
ALL( DimPMRAccount_Rollup)
),perCSE_PY,0),0)-1
return
SWITCH(
TRUE(),
SourceType="RLIabs",FORMAT(DIVIDE(RLI_CNPY-RLI_PY,RLI_PY),"0.0%;(0.0%);--"),
SourceType="RLIpc",FORMAT(DIVIDE(DIVIDE(RLI_CNPY,PerCSE_CNPY,0),DIVIDE(RLI_PY,perCSE_PY,0),0)-1,"0.0%;(0.0%);--"),
SELECTEDVALUE('Temp'[Items Short]) = "UCS (ADS)",FORMAT(UCS_ADS ,"0.0%;(0.0%);--"),
SELECTEDVALUE('Temp'[Items Short]) = "Non-Op A/O" && CurrentType = "abs",FORMAT(Non_OP,"0.0%;(0.0%);--"),
CurrentItem = "OPEX" && CurrentType = "abs",FORMAT(Opex,"0.0%;(0.0%);--"),
CurrentItem = "OPEX" && CurrentType = "pc",FORMAT(Opex_cse,"0.0%;(0.0%);--"),
BLANK()
)
