Forum Discussion

andrehawari's avatar
andrehawari
Icon for Helper II rankHelper II
7 years ago

Troubleshooting DAX Performance

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]

3 Replies

    • andrehawari's avatar
      andrehawari
      Icon for Helper II rankHelper II

      Hi Xiaoshin

       

      Thanks for  the links.  Actually, I already did the tracing using the similar method in your provided links. Thats why I can come  up with that problematic query in my original post. The only things that, I don't know what to do with that problematic dax query since I never really create a script like that. It seems that script autmatically generated by power bi engine

       

      regards