Forum Discussion
Optimizing a Matrix
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)]