Optimizing a Matrix

[ChristianDLS56]

Hi everyone!

I want to optimize how my matrix table runs. The matrix has 36 columns (from 36 measures, of course) which consist of CROSSFILTER and SUM functions. I think the measures are optimized already - it's just that when I change filter, it takes a while to load (it takes 20 seconds - what I need is 5 seconds)

Appreciate any help 🙂

[ChristianDLS56]

Here's the DAX query I exported from the performance analysis.

// DAX Query

DEFINE

                  VAR __DS0FilterTable =

                                    TREATAS({"2122 ACTUALS"}, 'Filter Table - Fiscal Year'[FY Data Type])

                  VAR __DS0FilterTable2 =

                                    TREATAS({"$/SU"}, 'Filter Table - Amount'[Amount Type])

                  VAR __DS0FilterTable3 =

                                    TREATAS({"USD"}, 'Filter Table - Currency'[Currency])

                  VAR __DS0FilterTable4 =

                                    FILTER(

                                                      KEEPFILTERS(VALUES('Filter Table - Customer'[Customer Type])),

                                                      'Filter Table - Customer'[Customer Type] <> "OTHERS"

                                    )

                  VAR __ValueFilterDM6 =

                                    FILTER(

                                                      KEEPFILTERS(

                                                                        SUMMARIZECOLUMNS(

                                                                                          'Data Table - Channel Profitability/CTS'[Local Trade Channel (PBI)],

                                                                                          'Data Table - Channel Profitability/CTS'[Local SubTrade Channel (PBI)],

                                                                                          'Data Table - Channel Profitability/CTS'[Customer Group (PBI)],

                                                                                          'Data Table - Channel Profitability/CTS'[Customer Name (PBI)],

                                                                                          'Data Table - Channel Profitability/CTS'[Category (PBI)],

                                                                                          __DS0FilterTable,

                                                                                          __DS0FilterTable2,

                                                                                          __DS0FilterTable3,

                                                                                          __DS0FilterTable4,

                                                                                          "VOL_MMSU_2_0", '2.0 measures'[VOL MMSU 2.0],

                                                                                          "GIV_MM_TABLE_2_0", '2.0 switch'[GIV MM TABLE 2.0],

                                                                                          "NOR_MM_TABLE_2_0", '2.0 switch'[NOR MM TABLE 2.0],

                                                                                          "SD_LOCAL_MM_TABLE_2_0", '2.0 switch'[SD LOCAL MM TABLE 2.0],

                                                                                          "SD_CORPORATE_MM_TABLE_2_0", '2.0 switch'[SD CORPORATE MM TABLE 2.0],

                                                                                          "SD_OTHERS_MM_TABLE_2_0", '2.0 switch'[SD OTHERS MM TABLE 2.0],

                                                                                          "SD_TOTAL_MM_TABLE_2_0", '2.0 switch'[SD TOTAL MM TABLE 2.0],

                                                                                          "ODS_PAYMENT_TERMS_MM_TABLE_2_0", '2.0 switch'[ODS PAYMENT TERMS MM TABLE 2.0],

                                                                                          "ODS_SUPPLY_TERMS_MM_TABLE_2_0", '2.0 switch'[ODS SUPPLY TERMS MM TABLE 2.0],

                                                                                          "ODS_DISTRIBUTOR_DISCOUNT_MM_TABLE_2_0", '2.0 switch'[ODS DISTRIBUTOR DISCOUNT MM TABLE 2.0],

                                                                                          "LOGISTICS_ALLOWANCE_MM_TABLE_2_0", '2.0 switch'[LOGISTICS ALLOWANCE MM TABLE 2.0],

                                                                                          "ODS_ADJ_MM_TABLE_2_0", '2.0 switch'[ODS ADJ MM TABLE 2.0],

                                                                                          "ODS_OTHERS_MM_TABLE_2_0", '2.0 switch'[ODS OTHERS MM TABLE 2.0],

                                                                                          "ODS_ALL_MM_TABLE_2_0", '2.0 switch'[ODS ALL MM TABLE 2.0],

                                                                                          "NOS_MM_TABLE_2_0", '2.0 switch'[NOS MM TABLE 2.0],

                                                                                          "LOCAL_T_W_MM_TABLE_2_0", '2.0 switch'[LOCAL T&W MM TABLE 2.0],

                                                                                          "TDC_EX_LOCAL_T_W_MM_TABLE_2_0", '2.0 switch'[TDC EX LOCAL T&W MM TABLE 2.0],

                                                                                          "TDC_ADJ_MM_TABLE_2_0", '2.0 switch'[TDC ADJ MM TABLE 2.0],

                                                                                          "TDC_TOTAL_MM_TABLE_2_0", '2.0 switch'[TDC TOTAL MM TABLE 2.0],

                                                                                          "GC_MM_TABLE_2_0", '2.0 switch'[GC MM TABLE 2.0],

                                                                                          "L_MSE_IN_STORE_MM_TABLE_2_0", '2.0 switch'[L-MSE IN-STORE MM TABLE 2.0],

                                                                                          "L_MSE_MEDIA_MM_TABLE_2_0", '2.0 switch'[L-MSE MEDIA MM TABLE 2.0],

                                                                                          "L_MSE_DIMI_MM_TABLE_2_0", '2.0 switch'[L-MSE DIMI MM TABLE 2.0],

                                                                                          "L_MSE_MM_TABLE_2_0", '2.0 switch'[L-MSE MM TABLE 2.0],

                                                                                          "C_MSE_IN_STORE_MM_TABLE_2_0", '2.0 switch'[C-MSE IN-STORE MM TABLE 2.0],

                                                                                          "C_MSE_MEDIA_MM_TABLE_2_0", '2.0 switch'[C-MSE MEDIA MM TABLE 2.0],

                                                                                          "C_MSE_DIMI_MM_TABLE_2_0", '2.0 switch'[C-MSE DIMI MM TABLE 2.0],

                                                                                          "C_MSE_MM_TABLE_2_0", '2.0 switch'[C-MSE MM TABLE 2.0],

                                                                                          "MSE_TOTAL_MM_TABLE_2_0", '2.0 switch'[MSE TOTAL MM TABLE 2.0],

                                                                                          "DIRECT_SALES_SRA_MM_TABLE_2_0", '2.0 switch'[DIRECT SALES SRA MM TABLE 2.0],

                                                                                          "SRA_OTHERS_MM_TABLE_2_0", '2.0 switch'[SRA OTHERS MM TABLE 2.0],

                                                                                          "SRA_TOTAL_MM_TABLE_2_0", '2.0 switch'[SRA TOTAL MM TABLE 2.0],

                                                                                          "OIX_MM_TABLE_2_0", '2.0 switch'[OIX MM TABLE 2.0],

                                                                                          "BT_MM_TABLE_2_0", '2.0 switch'[BT MM TABLE 2.0],

                                                                                          "GIV_SALIENCE_2_0", '2.0 switch'[GIV SALIENCE 2.0],

                                                                                          "DIFF_VS_AVG_COUNTRY_CHANNEL_PROFITABILITY", '2.0 switch'[DIFF VS AVG COUNTRY CHANNEL PROFITABILITY]

                                                                        )

                                                      ),

                                                      OR(NOT(ISBLANK([NOS_MM_TABLE_2_0])), [NOS_MM_TABLE_2_0] <> 0)

                                    )

                  VAR __DS0Core =

                                    SUMMARIZECOLUMNS(

                                                      ROLLUPADDISSUBTOTAL(

                                                                        'Data Table - Channel Profitability/CTS'[Local Trade Channel (PBI)], "IsGrandTotalRowTotal"

                                                      ),

                                                      __DS0FilterTable,

                                                      __DS0FilterTable2,

                                                      __DS0FilterTable3,

                                                      __DS0FilterTable4,

                                                      __ValueFilterDM6,

                                                      "GIV_SALIENCE_2_0", '2.0 switch'[GIV SALIENCE 2.0],

                                                      "VOL_MMSU_2_0", '2.0 measures'[VOL MMSU 2.0],

                                                      "GIV_MM_TABLE_2_0", '2.0 switch'[GIV MM TABLE 2.0],

                                                      "NOR_MM_TABLE_2_0", '2.0 switch'[NOR MM TABLE 2.0],

                                                      "SD_LOCAL_MM_TABLE_2_0", '2.0 switch'[SD LOCAL MM TABLE 2.0],

                                                      "SD_CORPORATE_MM_TABLE_2_0", '2.0 switch'[SD CORPORATE MM TABLE 2.0],

                                                      "SD_OTHERS_MM_TABLE_2_0", '2.0 switch'[SD OTHERS MM TABLE 2.0],

                                                      "SD_TOTAL_MM_TABLE_2_0", '2.0 switch'[SD TOTAL MM TABLE 2.0],

                                                      "ODS_PAYMENT_TERMS_MM_TABLE_2_0", '2.0 switch'[ODS PAYMENT TERMS MM TABLE 2.0],

                                                      "ODS_SUPPLY_TERMS_MM_TABLE_2_0", '2.0 switch'[ODS SUPPLY TERMS MM TABLE 2.0],

                                                      "ODS_DISTRIBUTOR_DISCOUNT_MM_TABLE_2_0", '2.0 switch'[ODS DISTRIBUTOR DISCOUNT MM TABLE 2.0],

                                                      "LOGISTICS_ALLOWANCE_MM_TABLE_2_0", '2.0 switch'[LOGISTICS ALLOWANCE MM TABLE 2.0],

                                                      "ODS_ADJ_MM_TABLE_2_0", '2.0 switch'[ODS ADJ MM TABLE 2.0],

                                                      "ODS_OTHERS_MM_TABLE_2_0", '2.0 switch'[ODS OTHERS MM TABLE 2.0],

                                                      "ODS_ALL_MM_TABLE_2_0", '2.0 switch'[ODS ALL MM TABLE 2.0],

                                                      "NOS_MM_TABLE_2_0", '2.0 switch'[NOS MM TABLE 2.0],

                                                      "LOCAL_T_W_MM_TABLE_2_0", '2.0 switch'[LOCAL T&W MM TABLE 2.0],

                                                      "TDC_EX_LOCAL_T_W_MM_TABLE_2_0", '2.0 switch'[TDC EX LOCAL T&W MM TABLE 2.0],

                                                      "TDC_ADJ_MM_TABLE_2_0", '2.0 switch'[TDC ADJ MM TABLE 2.0],

                                                      "TDC_TOTAL_MM_TABLE_2_0", '2.0 switch'[TDC TOTAL MM TABLE 2.0],

                                                      "GC_MM_TABLE_2_0", '2.0 switch'[GC MM TABLE 2.0],

                                                      "L_MSE_IN_STORE_MM_TABLE_2_0", '2.0 switch'[L-MSE IN-STORE MM TABLE 2.0],

                                                      "L_MSE_MEDIA_MM_TABLE_2_0", '2.0 switch'[L-MSE MEDIA MM TABLE 2.0],

                                                      "L_MSE_DIMI_MM_TABLE_2_0", '2.0 switch'[L-MSE DIMI MM TABLE 2.0],

                                                      "L_MSE_MM_TABLE_2_0", '2.0 switch'[L-MSE MM TABLE 2.0],

                                                      "C_MSE_IN_STORE_MM_TABLE_2_0", '2.0 switch'[C-MSE IN-STORE MM TABLE 2.0],

                                                      "C_MSE_MEDIA_MM_TABLE_2_0", '2.0 switch'[C-MSE MEDIA MM TABLE 2.0],

                                                      "C_MSE_DIMI_MM_TABLE_2_0", '2.0 switch'[C-MSE DIMI MM TABLE 2.0],

                                                      "C_MSE_MM_TABLE_2_0", '2.0 switch'[C-MSE MM TABLE 2.0],

                                                      "MSE_TOTAL_MM_TABLE_2_0", '2.0 switch'[MSE TOTAL MM TABLE 2.0],

                                                      "DIRECT_SALES_SRA_MM_TABLE_2_0", '2.0 switch'[DIRECT SALES SRA MM TABLE 2.0],

                                                      "SRA_OTHERS_MM_TABLE_2_0", '2.0 switch'[SRA OTHERS MM TABLE 2.0],

                                                      "SRA_TOTAL_MM_TABLE_2_0", '2.0 switch'[SRA TOTAL MM TABLE 2.0],

                                                      "OIX_MM_TABLE_2_0", '2.0 switch'[OIX MM TABLE 2.0],

                                                      "BT_MM_TABLE_2_0", '2.0 switch'[BT MM TABLE 2.0],

                                                      "DIFF_VS_AVG_COUNTRY_CHANNEL_PROFITABILITY", '2.0 switch'[DIFF VS AVG COUNTRY CHANNEL PROFITABILITY]

                                    )

                  VAR __DS0PrimaryWindowed =

                                    TOPN(

                                                      502,

                                                      __DS0Core,

                                                      [IsGrandTotalRowTotal],

                                                      0,

                                                      [GIV_SALIENCE_2_0],

                                                      0,

                                                      'Data Table - Channel Profitability/CTS'[Local Trade Channel (PBI)],

                                                      1

                                    )

                  VAR __DS0CoreNoInstanceFiltersNoTotals =

                                    FILTER(KEEPFILTERS(__DS0Core), [IsGrandTotalRowTotal] = FALSE)

EVALUATE

                  GROUPBY(

                                    __DS0CoreNoInstanceFiltersNoTotals,

                                    "MinGIV_SALIENCE_2_0", MINX(CURRENTGROUP(), [GIV_SALIENCE_2_0]),

                                    "MaxGIV_SALIENCE_2_0", MAXX(CURRENTGROUP(), [GIV_SALIENCE_2_0])

                  )

EVALUATE

                  __DS0PrimaryWindowed

ORDER BY

                  [IsGrandTotalRowTotal] DESC,

                  [GIV_SALIENCE_2_0] DESC,

                  'Data Table - Channel Profitability/CTS'[Local Trade Channel (PBI)]