Performance Issues with Formula

[Pfoster]

Hello,

I have made a report where Sales Managers can see their Net Turnover in comparison to an other Scenario. This Delta is split into FX-, Price-, Volume- und Mix-Effects.

With FX- and Mix-Effect, there is no issue, but I think, my Price-Effect and my Volume-Effect are killing the performance, because on the total line, I want to have the sum of the line-results:

here are the Measures I used:

Price Effect = 
VAR Grain =
    SUMMARIZECOLUMNS(
        'Sales Data'[Company Code],
        'Sales Data'[Material],
        'Sales Data'[Posting period],
        'Sales Data'[Ship To],
        'Sales Data'[Sold To],
        'Sales Data'[Country Code of ship to],
        FocusScenario[Focus Scenario]
    )
VAR Base =
    ADDCOLUMNS(
        Grain,
        "NSPComp",   CALCULATE([NSPComp_LC]),
        "NSPFocus",  CALCULATE([NSPFocus_LC]),
        "FX",        CALCULATE([FX_Rate_CompPeriod]),
        "FocusMT",   CALCULATE([FocusMT])
    )
RETURN
SUMX(
    FILTER(
        Base,
        NOT ( 'Sales Data'[Company Code] IN { "PF3091","PF3HGS","PF3262","PF3267" } ) &&
        'Sales Data'[Material] <> "100222" &&
        [NSPComp]  > 0 &&
        [NSPFocus] > 0
    ),
    ([NSPFocus] - [NSPComp]) * [FX] * [FocusMT] * 1000
)

VolEffect = 
VAR BaseTable =
    ADDCOLUMNS (
        SUMMARIZECOLUMNS (
            'Sales Data'[Company Code],
            'Sales Data'[Material],
            'Sales Data'[Posting period],
            'Sales Data'[Ship To],
            'Sales Data'[Sold To],
            'Sales Data'[Country Code of ship to],
            FocusScenario[Focus Scenario]
        ),
        "__RowVolEffect",
            VAR Focus    = [FocusMT]
            VAR CompRaw  = [CompMT]
            VAR CompCalc = IF ( ISBLANK ( CompRaw ), 0, CompRaw )
            VAR BothZeroOrBlank =
                ( ISBLANK ( Focus ) || Focus = 0 ) &&
                ( ISBLANK ( CompRaw ) || CompRaw = 0 )
            RETURN
                IF (
                    BothZeroOrBlank,
                    BLANK(),
                    IF (
                        CompCalc = 0,
                        ( Focus - CompCalc ) * [NSPFocus] * 1000,
                        ( Focus - CompCalc ) * [NSPComp] * 1000
                    )
                )
    )
RETURN
SUMX ( BaseTable, [__RowVolEffect] )

Is there a way to get these Measures quicker and more stable? Thank you for any advise! 

[v-sgandrathi]

Hi @Pfoster,

Thank you @danextian and @amitchandak  for your replie sto the query.

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help.

Thank you.

[danextian · Accepted solution]

Hi @Pfoster 

For the first measure, filter the table first before summarizing to reduce the number of rows. The measures in the ADDCOLUMNS don't need calculate as itis implicit if a measure is referenced.

Price Effect =
VAR FilteredSales =
    FILTER (
        'Sales Data',
        NOT ( 'Sales Data'[Company Code] IN { "PF3091", "PF3HGS", "PF3262", "PF3267" } ) &&
        'Sales Data'[Material] <> "100222"
    )

VAR Grain =
    SUMMARIZE (
        FilteredSales,
        'Sales Data'[Company Code],
        'Sales Data'[Material],
        'Sales Data'[Posting period],
        'Sales Data'[Ship To],
        'Sales Data'[Sold To],
        'Sales Data'[Country Code of ship to],
        FocusScenario[Focus Scenario] -- this works if FocusScenario is a related table on the one side of a relationship with Sales Data on the many side.
    )

VAR Base =
    ADDCOLUMNS (
        Grain,
        "NSPComp",  [NSPComp_LC],
        "NSPFocus", [NSPFocus_LC],
        "FX",       [FX_Rate_CompPeriod],
        "FocusMT",  [FocusMT]
    )

RETURN
SUMX (
    FILTER (
        Base,
        [NSPComp] > 0 &&
        [NSPFocus] > 0
    ),
    ( [NSPFocus] - [NSPComp] ) * [FX] * [FocusMT] * 1000
)

For the second measure, try this:

VolEffect =
VAR BaseTable =
    ADDCOLUMNS (
        SUMMARIZECOLUMNS (
            'Sales Data'[Company Code],
            'Sales Data'[Material],
            'Sales Data'[Posting period],
            'Sales Data'[Ship To],
            'Sales Data'[Sold To],
            'Sales Data'[Country Code of ship to],
            FocusScenario[Focus Scenario]
        ),
        "__RowVolEffect",
            VAR Focus    = [FocusMT]
            VAR CompCalc = [CompMT] + 0
            VAR BothZero =
                ( ISBLANK(Focus) || Focus = 0 ) &&
                CompCalc = 0
            RETURN
                IF (
                    NOT BothZero,
                    IF (
                        CompCalc = 0,
                        ( Focus - CompCalc ) * [NSPFocus] * 1000,
                        ( Focus - CompCalc ) * [NSPComp] * 1000
                    )
                )
    )
RETURN
SUMX(BaseTable, [__RowVolEffect])

Note: DAX optimization isn’t a one-size-fits-all solution and usually requires testing. If this approach doesn’t achieve the desired performance, consider pre-computing or aggregating the results in a calculated table. While this may increase memory usage, it can help the visual render more quickly.

[amitchandak]

@Pfoster , ideally, for this level of calculation, I would expect an aggregated table 

But you can move this file in summarizecolumns 

NOT ( 'Sales Data'[Company Code] IN { "PF3091","PF3HGS","PF3262","PF3267" } ) &&
'Sales Data'[Material] <> "100222"