Forum Discussion
Table visual loading slow / High DAX duration
Hi,
I’m working on optimizing the performance of visuals across all dashboards. One of the tables in one report is taking a long time to load, primarily due to a DAX query that’s causing delays. Most queries are native SQL (no measures or calculated columns). I’ve used DAX Studio to analyze the query but haven’t identified the specific column causing the issue. But, I found that removing the "Id" column (not a primary key) significantly reduces query time when tested individually.
Any suggestions on optimizing the loading of this table?
Also, for optimizing measures, do you recommend using "Dax Optimizer"?
Thanks a lot!
Thank you Greg_Deckler and elitesmitpatel , your ideas is great!
Hi, amir_mm
Optimizing DAX queries can be challenging. We can work together with Power BI's built-in profiling tools and external tools to achieve this goal.
First, we can use the Performance analyzer that comes with Power BI Desktop:We can find the tutorial on how to use this tool from the following link:
Any actions you take in the report are displayed and recorded in real time in the Performance Analyzer pane in the order in which Power BI loads the visuals.
Performance Analyzer is looking at how long it takes for each visual to query the data model and render the results. This is the time (in milliseconds) from when the user performs something on the page to when the visual is rendered.The official documentation also has an optimization guide where you can get some tips for optimizing the user experience:
Optimization guide for Power BI - Power BI | Microsoft Learn
Secondly, we can use external tools such as DAX studio, daxoptimizer, etc. When you use these tools, you need to follow the official documentation of the corresponding tools to understand the metrics.
The following article describes the practices for optimizing DAX:
Optimizing DAX expressions involving multiple measures - SQLBI
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
amir_mm What is the DAX query?
Hey amir_mm
Here are few thing you can do
1) Remove Unnecessary Columns: if you can then remove unnecessary column.2) Use DirectQuery instead of Import mode
3) If it is very import then Create a SQL View for this and keep only necessary column.
Thank you Greg_Deckler and elitesmitpatel , your ideas is great!
Hi, amir_mm
Optimizing DAX queries can be challenging. We can work together with Power BI's built-in profiling tools and external tools to achieve this goal.
First, we can use the Performance analyzer that comes with Power BI Desktop:We can find the tutorial on how to use this tool from the following link:
Any actions you take in the report are displayed and recorded in real time in the Performance Analyzer pane in the order in which Power BI loads the visuals.
Performance Analyzer is looking at how long it takes for each visual to query the data model and render the results. This is the time (in milliseconds) from when the user performs something on the page to when the visual is rendered.The official documentation also has an optimization guide where you can get some tips for optimizing the user experience:
Optimization guide for Power BI - Power BI | Microsoft Learn
Secondly, we can use external tools such as DAX studio, daxoptimizer, etc. When you use these tools, you need to follow the official documentation of the corresponding tools to understand the metrics.
The following article describes the practices for optimizing DAX:
Optimizing DAX expressions involving multiple measures - SQLBI
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
May I know if DAX optimization tool is free to access?I used "Dax Optimizer" tool to identify the faulty measures. The tool is not free and you need to get a one time or monthly subscription for it but I think it worth it.
Basically, it will list all the measures with performance issue (in terms of CPU and RAM) and also indicates what parts of the measures have to be optimized (but it will NOT do the optimization for you). You would need to optimize the query yourslef based on the proposed suggestions.
