Forum Discussion
Dax CPU Optimization for Capacity
Hello,
I inherited a report that is performing extremely poorly from a CPU usage and constantly making our capacity auto scale daily. I am wondering if there is a defined best practice when trying to use the summarize function using a measure as one of the columns to summarize. Here is an example of the code that gives the correct outputs but is incredibly inefficient in terms of CPU and causing a lot of capacity issues as this is a primary measure that is used by a lot of people and the data it runs on top of is ~125M records. The [Overall Points] measure has ~40 measures that feed into it (was not my doing). This measure also has a bunch of averages and other rank measures that reference this measure so that is why the "relevance" is lower than it really is.
VAR Summary =
SUMMARIZE (
FILTER (
ALL ( Stores ),
'Stores'[Area] IN { "East", "West" } && 'Stores'[StoreName] <> "VA-Bluefield"
),
Stores[StoreName],
"Overall Points", [Overall Points]
)
RETURN
RANKX (
Summary,
[Overall Points],, DESC
)Dax Optimizer says the following around the measure.
If there was a way to get a calculated table to update based on the date range a user enters that would be my ideal solution.
ChrisCarrVictra This may give you better performance but it sounds like you need to optimize your Overall Points measure:
VAR Summary = SUMMARIZE ( CALCULATETABLE( 'Stores', 'Stores'[Area] IN { "East", "West" }, 'Stores'[StoreName] <> "VA-Bluefield" ), Stores[StoreName], "Overall Points", [Overall Points] ) RETURN RANKX ( Summary, [Overall Points],, DESC )Your point of view is great Greg_Deckler . I have the following additions:
Hi, ChrisCarrVictra
If you need to optimize your complex DAX expressions, you can refer to the following article:
Optimizing DAX expressions involving multiple measures - SQLBI
In the following articles, there are tutorials for optimizing different DAX functions:
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
ChrisCarrVictra This may give you better performance but it sounds like you need to optimize your Overall Points measure:
VAR Summary = SUMMARIZE ( CALCULATETABLE( 'Stores', 'Stores'[Area] IN { "East", "West" }, 'Stores'[StoreName] <> "VA-Bluefield" ), Stores[StoreName], "Overall Points", [Overall Points] ) RETURN RANKX ( Summary, [Overall Points],, DESC )Greg_Deckler
😲 is this you who just suggested CALCULATETABLE or am I dreaming? 😅 Looks like you started to like the function 😂tamerj1 Lol! I never had an issue with CALCULATETABLE. In fact, in my opinion, the No CALCULATE approach (VARs, Table VAR, Table Aggregator) without CALCULATE or CALCULATETABLE can solve probably 90% or more of all DAX problems. You add CALCULATETABLE and you are at 99.99% of all DAX problems solved. And, it's the exact same pattern it's just that you use CALCULATETABLE when creating your Table VAR. Still no CALCULATE though! 🙂
Your point of view is great Greg_Deckler . I have the following additions:
Hi, ChrisCarrVictra
If you need to optimize your complex DAX expressions, you can refer to the following article:
Optimizing DAX expressions involving multiple measures - SQLBI
In the following articles, there are tutorials for optimizing different DAX functions:
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
