How to diagnose which calculated column slows down the dataset refreshing

[Jeanxyz]

I have a data model which takes a long time to refresh. After lots of testing, I realize the slow performance is due to a calculated column which involves complex filters. After rewriting the column DAX, the refreshing cycle has decreased from 12 minutes to 10 sec. 

However, the investigation process has been very time consuming - I have to delete the calculated columns and run manual dataset refreshing. To automate this process, I have tried various tools like DAX studio, Performance Analyzer and Diagnostices tools, but all the them failed because the problem only occured when the calculated column is updated during dataset refreshing cycle. Once the update is complete, this column performs the same way as an imported column. Is there any tool which help diagnose performance issues related to calculated columns?

[Jeanxyz · Accepted solution]

It turns out the solution lies with an old tool SQL Profiler. 

discussion thread: https://community.fabric.microsoft.com/t5/Power-Query/what-s-the-best-way-to-load-data-using-power-q...

[johnt75]

You can use DAX Studio. You could either use 

DEFINE
COLUMN 'Table'[New Column' = Column definition

EVALULATE
'Table'

or something like

EVALUATE
ADDCOLUMNS( 
    'Table',
    "New column name",
Column definition
)

[Jeanxyz]

I can see the calculation formula in PB desktop, so it's not a definition issue. What I really need is to know how much capacity/resources is used per calculated column. If one column consumes lots of calculation resources and cause prolonged data refreshig, I would like to know this via a diagonistic tool. 

[johnt75]

By evaluating the column in DAX Studio you can take advantage of the Query Plan and Server Timings, and also just see how long it takes to return results. You can also test how well optimisation attempts work by making the changes in DAX Studio.

[v-aatheeque]

Hi @Jeanxyz ,

If the information is helpful, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post. If you are still facing any issue, please let us know in the comments. We are glad to help you.

We value your feedback, and it will help us to assist others who might have a similar query. Thank you for your contribution in enhancing Microsoft Fabric Community Forum.