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.  

 

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

 

When I remove the totalProgram field the message goes away.  here is sample data.

ProgramIDNameType*TotalProgramTotal
Test program 1101Test program 1Prog*$18,4600
Test program 1102Project aWork$1046010460
Test program 1103Project bWork$80008000

 

Goal of the measure is to calculate sum for each Program and only put that total in rows where Type = Prog.  If Type = Work, we just want to use what is in the related Total column 

 

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.   

4 Replies

  • amustafa's avatar
    amustafa
    Solution Sage
    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
      Not applicable
      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    I removed the  ProjectTable[Type] = "Prog") and it acted as expected.  I found that I have too many measures so i ended up moving some of the calculations to dataflow in PBI Service and that seems to be resolving the memory error.