Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Help making a measure more efficient and less resource intensive

I have a measure that seems to be causing "excceeded available resource" messages and want to see if there is a better way to right the below meausre before I open a support ticket.     totalProgra...
Need Help
  • amustafa's avatar
    amustafa
    2 years ago

    This revised DAX might help with performance.

     

    totalProgram =
    VAR CurrentProgram = SELECTEDVALUE(ProjectTable[Program])
    VAR ProgramTotal = CALCULATE(SUM(ProjectTable[Total]), FILTER(ALL(ProjectTable), ProjectTable[Program] = CurrentProgram && ProjectTable[Type] = "Prog"))
    RETURN IF(SELECTEDVALUE(ProjectTable[Type]) = "Prog", ProgramTotal, SUM(ProjectTable[Total]))

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    The above solution calculates the type labeled "work" but it doesn't calculate the type labeled Program.  I just see 0 for Programs.  I'll play around with this and see if I can find different option.   

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

I suggest you modify the DAX as follows:

totalProgram = 
VAR A = 
CALCULATE(SUM(ProjectTable[Total]), ALLEXCEPT(ProjectTable, ProjectTable[Program]))
RETURN
CALCULATE(
    IF(
        SELECTEDVALUE(ProjectTable[Type])="Prog",
        A,
        SUM(ProjectTable[Total])
    )
)

The metric you provided seems to be recalculating the sum of each row of "Type" as "Program". This can be resource intensive, especially if the "ProjectTable" is large.
The changed metric only counts the total number of items of type "Prog" once and stores it in a variable. It then checks the "Type" of the current row and returns the corresponding value.

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