Dax CPU Optimization for Capacity

[ChrisCarrVictra]

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.

[Greg_Deckler · Accepted 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
    )

[v-jianpeng-msft · Accepted solution]

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:

Optimization - SQLBI

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.

[Greg_Deckler · Accepted 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
    )

[tamerj1]

@Greg_Deckler 
😲 is this you who just suggested CALCULATETABLE or am I dreaming? 😅 Looks like you started to like the function 😂

[Greg_Deckler]

@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! 🙂