Forum Discussion
Dax Query too long when disabling Edit Interactions Filter
I Have a Matrix and works fine, but when I disable the Edit Interactions filter to show all months the query takes 50 seconds.
I have this code from DAX Studio can you tell me what´s wrong?
// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('DIM CALENDAR'[MONTH_YEAR])),
NOT('DIM CALENDAR'[MONTH_YEAR] IN {BLANK()})
)
VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('DIM HCO'[TERRITORY_NUMBER])),
AND(
NOT(SEARCH("EISAI", 'DIM HCO'[TERRITORY_NUMBER], 1, 0) >= 1),
NOT(ISBLANK('DIM HCO'[TERRITORY_NUMBER]))
)
)
VAR __DS0FilterTable3 =
FILTER(
KEEPFILTERS(VALUES('DIM MSL'[MSL])),
NOT('DIM MSL'[MSL] IN {BLANK()})
)
VAR __DS0FilterTable4 =
FILTER(
KEEPFILTERS(VALUES('DIM GEO'[REGION_NAME])),
NOT('DIM GEO'[REGION_NAME] IN {BLANK()})
)
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL('DIM HCO'[ACCOUNT_NAME], "IsGrandTotalRowTotal"),
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP('DIM CALENDAR'[MONTH_YEAR], 'DIM CALENDAR'[ORDER_MONTH_YEAR]), "IsGrandTotalColumnTotal"
),
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
"v_Interactions", 'INTERACTIONS'[#Interactions],
"v__Focused", 'INTERACTIONS'[% Focused],
"v__Section_Score", 'SITE READINESS SECTION'[# Section Score],
"v__Site_Overall_Score", IGNORE('SITE READINESS OVERALL'[# Site Overall Score])
)
VAR __DS0PrimaryWindowed =
TOPN(
102,
SUMMARIZE(__DS0Core, 'DIM HCO'[ACCOUNT_NAME], [IsGrandTotalRowTotal]),
[IsGrandTotalRowTotal],
0,
'DIM HCO'[ACCOUNT_NAME],
1
)
VAR __DS0SecondaryBase =
SUMMARIZE(
__DS0Core,
'DIM CALENDAR'[MONTH_YEAR],
'DIM CALENDAR'[ORDER_MONTH_YEAR],
[IsGrandTotalColumnTotal]
)
VAR __DS0Secondary =
TOPN(
102,
__DS0SecondaryBase,
[IsGrandTotalColumnTotal],
1,
'DIM CALENDAR'[ORDER_MONTH_YEAR],
1,
'DIM CALENDAR'[MONTH_YEAR],
1
)
VAR __DS0BodyLimited =
NATURALLEFTOUTERJOIN(
__DS0PrimaryWindowed,
SUBSTITUTEWITHINDEX(
__DS0Core,
"ColumnIndex",
__DS0Secondary,
[IsGrandTotalColumnTotal],
ASC,
'DIM CALENDAR'[ORDER_MONTH_YEAR],
ASC,
'DIM CALENDAR'[MONTH_YEAR],
ASC
)
)
EVALUATE
__DS0Secondary
ORDER BY
[IsGrandTotalColumnTotal],
'DIM CALENDAR'[ORDER_MONTH_YEAR],
'DIM CALENDAR'[MONTH_YEAR]
EVALUATE
__DS0BodyLimited
ORDER BY
[IsGrandTotalRowTotal] DESC, 'DIM HCO'[ACCOUNT_NAME], [ColumnIndex]