Table visual loading slow / High DAX duration

[amir_mm]

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!

[Anonymous · Accepted solution]

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:

Use Performance Analyzer to examine report element performance in Power BI Desktop - Power BI | Micr...

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.

[Max19]

Hi @Anonymous ,

May I know if DAX optimization tool is free to access?

[amir_mm]

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.

[elitesmitpatel]

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.

[Greg_Deckler]

@amir_mm What is the DAX query?