Help making a measure more efficient and less resource intensive

[Anonymous]

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.

Program	ID	Name	Type	*TotalProgram	Total
Test program 1	101	Test program 1	Prog	*$18,460	0
Test program 1	102	Project a	Work	$10460	10460
Test program 1	103	Project b	Work	$8000	8000

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 

[amustafa · Accepted solution]

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]

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.

[Anonymous]

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.

[amustafa · Accepted solution]

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 · Accepted solution]

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.