Forum Discussion

emmalim's avatar
emmalim
Frequent Visitor
3 years ago
Solved

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

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 🙂

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.