Forum Discussion
Performance Issues with Formula
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!
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.
3 Replies
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.
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"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.
