Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
andrehawari
Helper II
Helper II

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 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

SQLBI: Optimization

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.