Nested Sumx performance

[ed_mcdill]

Project Management Cost_Variable_Target =

VAR TargetPercentage =
ADDCOLUMNS (
Projects,
"Target_Percent", SWITCH (
TRUE (),
Projects[Project_Type] = "Type A", 0.125,
Projects[Project_Type] = "Type B", 0.11,
Projects[Project_Type] = "Type C", 0.12,
Projects[Project_Type] = "Type D", 0.06,
Projects[Project_Type] = "Type E", 0.107,
0
)
)

RETURN
SUMX (
TaskForecastMonths,
SUMX ( TargetPercentage, [Target_Percent] * [Project_Cost] )
)

I have Project dimension table (Projects) and a fact table (TaskForecastMonths) which stores how much the project costs by month. This is a tabular model owned by another team so I can't add a calculated column.

Based on the type of project I assign a different percentage (this is the bit I'd maybe use a calculated column if I owned the model), this percentage I then multiply against the project cost in the TaskForecastMonths fact table. This gives me a target cost for a team that I use for a KPI.

This works but it is very slow, (15-20 seconds to filter) and I'm assuming a nested sumx might be the issue. Does anyone have any experience with this? Also what's the best practice for submitting questions like this, how should I format and frame the question?

Thanks for all the help I've been receiving on this form.

[v-yulgu-msft · Accepted solution]

Hi @ed_mcdill,

Based on my research, the SUMX function might affect the execution performance, especially nested SUMX. You could refer to below blogs to optimizie your DAX formula:

Tune your PowerPivot DAX query, don’t use the entire table in a FILTER and replace SUMX if possible

Nested SUMX or DAX Query?

Best regards,
Yuliana Gu 

[Greg_Deckler]

Review this article here:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[ed_mcdill]

thanks for the link