Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
Best regards,
Yuliana Gu
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
Best regards,
Yuliana Gu
Review this article here:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
thanks for the link
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |