Forum Discussion

ChristianDLS56's avatar
ChristianDLS56
Frequent Visitor
3 years ago

Optimizing a Matrix

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 🙂

 

1 Reply

  • 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)]