Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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]
Hi @andrehawari,
I'd like to suggest you to refer to following link to use SSMS to connect to power bi data model, then you can try to use AS data engine to trace dax formula performance:
Connect to Power BI Desktop Model from Excel and SSMS
In addition, you can also consider to use DAX studio to trace query performance:
How to Trace DAX Queries using DAX Studio
Regards,
Xiaoxin Sheng
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
Hi @andrehawari,
If these query is generated by power bi itself, I also not found a way to modify the logic of generate dax query which power bi used.
Perhaps you can take a look at following links if it help for your scenario:
Optimizing & Analyzing DAX Query Plans
Regards,
Xiaoxin Sheng
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
111 | |
59 | |
57 |