Hi, I have a problem with my Dashboard performance. Currently it has aboout 65 millions row and it takes approx 10 second to load a table.
Upon analysing the process using SQL Server Profiler, I found the internal dax engine generate the dax code below with the longest duration time (>5s)
The dax engine below generating Evaluate TOP N queries, that I suspected causing the significant drop in performance. However, I don't have any idea why internal DAX engine generate this query since I don't have any measure that explicitly use TOPN function at all.
Any idea how to trace this dax query?
DEFINE
VAR __DS0FilterTable =
TREATAS({"August 2017"}, 'Months'[Filter])
VAR __DS0FilterTable2 =
TREATAS({"Ice"}, 'MyTable'[Group])
VAR __DS0FilterTable3 =
TREATAS({"Sales"}, 'KPI'[KPI])
VAR __DS0FilterTable4 =
FILTER(
KEEPFILTERS(VALUES('Months'[FILTER])),
NOT('Months'[FILTER] IN {BLANK()})
)
VAR __ValueFilterDM0 =
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'MyTable'[Month],
'MyTable'[CombiningColumn],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
"Amount", 'MyTable'[Amount],
"Target", 'MyTable'[Target],
"Achv", 'MyTable'[Achv],
"v1", 'MyTable'[1],
"Ball_Code", IGNORE('MyTable'[Ball Code]),
"v1_Month_Filter_MyTable_M", IGNORE('MyTable'[1 Month Filter])
)
),
[v1_Month_Filter_MyTable_M] = 1
)
EVALUATE
TOPN(
501,
SUMMARIZECOLUMNS(
'MyTable'[Month],
'MyTable'[CombiningColumn],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__ValueFilterDM0,
"Target", 'MyTable'[Target],
"Amount", 'MyTable'[Amount],
"Achv", 'MyTable'[Achv],
"v1", 'MyTable'[1],
"Ball_Code", IGNORE('MyTable'[Ball Code])
),
[Target],
0,
'MyTable'[Month],
1,
'MyTable'[CombiningColumn],
1
)
ORDER BY
[Target] DESC,
'MyTable'[Month],
'MyTable[CombiningColumn]
