code Changes

[sanc_152]

Hi everyone !

is there any way I can optimize the below DAX code.because of sumx functions it is really slowing down the perfomance 

AR SummerizeTableTb =

    SUMMARIZE (

         'New,

        'New '[product],

        "Actual", SUM ( 'New ‘[Actual] ),

        "final", SUM ( 'New ‘[Final Forecast] )

    )

VAR TotalR =

    DIVIDE (

        SUMX (

            ADDCOLUMNS (

                SummerizeTableTb,

                "MAD final", ABS ( [Actual] - [final] ),

                "Actuals", [Actual]

            ),

            IFERROR ( [MAD final], 0 )

        ),

        SUMX (

            ADDCOLUMNS (

                SummerizeTableTb,

                "Actuals", [Actual]

            ),

            IFERROR ( [Actuals], 0 )

        ),

        BLANK()

    )

RETURN

    IF ( ISBLANK ( TotalR ), BLANK (), 1 - TotalR )

@amitchandak 

@Ahmedx 

@Ritaf1983 

 

[lbendlin · Accepted solution]

if you are willing to pay - DAX Optimizer has some ideas.

A quick fix will be to use SUMMARIZECOLUMNS instead of SUMMARIZE to avoid lugging the extended tables around. All the secrets of SUMMARIZE - SQLBI