How to reduce the refresh time of the table and measures

[Yanou]

Hello everyone,

I have a PBI model of around 150 000 data lines and, I think, a relevant Power Query model. I know that the time refresh (30 seconds ...) is caused by the mesure and my model table. Could you tell me advise and solution to organize my data model in an other way please ? Some one speaks to me about a mapping dimension table but I don't manage to do it. 

The context : a accounting data base with task codes. The accounting entries have a amount (balance) to calculate some costs of subvention.

See enclose my data model (with the links and the measures) to have a point of view;

Thanks a lot

Yannick

https://we.tl/t-oYyNv3UtZb 

[v-yangliu-msft · Accepted solution]

Hi  @Yanou ,

On the data model, when preparing data, you can append and merge tables with similar structure and purpose to form a table, or clean the unwanted content in advance.

After that, you can choose the correct data model content, such as Star schema, Snowflake schema, try to use Measure, because the Calculated column will store the calculation results directly in Power BI, and Measure will calculate when you need it, make sure to remove any unnecessary columns, etc.

Refer to：

Power BI Performance Optimization: Make Reports Run Up to 10X Faster (zebrabi.com)

You can check the query duration by Performance Analyzer option in Power BI Desktop.

For reference: Use Performance Analyzer to examine report element performance

Or, you can try to update your code by DAX Studio by checking the resource occupancy.

https://daxstudio.org/

You can also optimize Dax:

DAX Best Practices | MAQ Software Insights

Measure In Power BI: Optimization Tips And Techniques - Enterprise DNA

This is the related document, you can view this content:

Reduce loading time during refresh - Microsoft Power BI Community

Any way to speed up really slow refresh times in Power Query? | Power Query | Excel Forum (myonlinet...

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.