Visual has exceeded the available resources - How to optimize this measure

[emmalim]

I have the issue for 'Visual has exceeded the available'.

Probably due to I have several columns with little complex measures in a matrix.

I learned online that we should try to use variable to eliminate those duplicated DAX expression.

Optimizing duplicated DAX expressions using variables - SQLBI

So I tried to enhance it, this is one of the measure I have in the matrix:

PL_CM =

VAR _Month = SELECTEDVALUE('Calendar'[Month])

VAR _GPline = SELECTEDVALUE('P&L_ROW'[PL_ROW]) = "Gross Profit %"

VAR _Sales = CALCULATE([vPL_CM],

FILTER(ALL('P&L_ROW'), 'P&L_ROW'[PL_ROW] = "Net Sales"))

VAR _Cost = CALCULATE ([vPL_CM],

FILTER(ALL('P&L_ROW'),'P&L_ROW'[PL_ROW] = "Cost of sales"))

VAR _GP = Calculate (_Sales+_Cost)

VAR _GPPercent = DIVIDE(_GP,_Sales)

VAR _GPLine2 = IF(_Month = "FEB",[PL_GROSSMARGIN_%],_GPPercent)

VAR _OtherLine = IF(_Month="FEB",[PL_YTD],[vPL_CM])

VAR _PLCM =

IF(_GPline, _GPLine2,_OtherLine)

RETURN

_PLCM

My matrix is now able to show, but it took 20 secs to load, so i wish to enhance further.

I noticed this 2 filter lines abit duplicated, can anyone help to enhance it?

FILTER(ALL('P&L_ROW'), 'P&L_ROW'[PL_ROW] = "Net Sales"))
FILTER(ALL('P&L_ROW'),'P&L_ROW'[PL_ROW] = "Cost of sales"))

Thank you 🙂

[Anonymous · Accepted solution]

Hi  @emmalim ,

You can try the following dax

Measure =
CALCULATE(
    [VpL_Cm],
    FILTER(ALL('P&L_ROW'),'P&L_ROW'[PL_ROW] in {"Net Sales","Cost of sales"}))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.