cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yanou
Helper II
Helper II

How to reduce the refresh time of the table and measures

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 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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.

View solution in original post

1 REPLY 1
v-yangliu-msft
Community Support
Community Support

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.