Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
powerbi_user003
New Member

Card refresh load time is more than 60 seconds

Total records in the source table used in power bi: 283Million (data for 6 quarters including 2024-Q2)
Records in 2024-Q2: 20 Million
Datasource: Databricks
Input method: Direct query method.

Problem:
              Card showing sum aggregate of sales column for 2024-Q2 takes 60 seconds refresh upon change in slicers in power BI.

Please help me on this.

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

Hi @powerbi_user003 ,

 

From your description, I can see that the number of records is large, so I think performance optimization is the key.

 

Here are some of my suggestions, please refer to:

 

  1. It is recommended to use star schema modeling in Power BI.

 

It not only improves the performance of the model, but also improves the simplicity of DAX code and minimizes complexity.

 

  1. Within the scope of Power BI, you can use Power Query or DAX to transform data.

 

In general, data should be transformed upstream as much as possible and downstream when necessary. The main recommendation in Power BI is to transform in Power Query as much as possible. Transformations in DAX should be performed only when absolutely necessary.

 

  1. Keep DAX measures simple

 

  1. The report layout should be carefully planned, and try to limit the number of visual objects on a single page.

 

  1. Optimize the report to reduce queries

 

Turn off cross-highlighting and cross-filtering in Power BI Desktop.

 

Open the "Optimize" tab and change the "Optimize" preset to "Reduce queries".

vhuijieymsft_0-1716951532234.png

 

For more information, please refer to the link below:

Optimizing Power BI DirectQuery performance - Practical guide part 2 (maxwikstrom.se)

Optimizing card visuals in slow Power BI reports - SQLBI

DirectQuery optimization scenarios with the Optimize ribbon in Power BI Desktop - Power BI | Microso...

 

If you have other questions, please feel free to contact me.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

View solution in original post

1 REPLY 1
v-huijiey-msft
Community Support
Community Support

Hi @powerbi_user003 ,

 

From your description, I can see that the number of records is large, so I think performance optimization is the key.

 

Here are some of my suggestions, please refer to:

 

  1. It is recommended to use star schema modeling in Power BI.

 

It not only improves the performance of the model, but also improves the simplicity of DAX code and minimizes complexity.

 

  1. Within the scope of Power BI, you can use Power Query or DAX to transform data.

 

In general, data should be transformed upstream as much as possible and downstream when necessary. The main recommendation in Power BI is to transform in Power Query as much as possible. Transformations in DAX should be performed only when absolutely necessary.

 

  1. Keep DAX measures simple

 

  1. The report layout should be carefully planned, and try to limit the number of visual objects on a single page.

 

  1. Optimize the report to reduce queries

 

Turn off cross-highlighting and cross-filtering in Power BI Desktop.

 

Open the "Optimize" tab and change the "Optimize" preset to "Reduce queries".

vhuijieymsft_0-1716951532234.png

 

For more information, please refer to the link below:

Optimizing Power BI DirectQuery performance - Practical guide part 2 (maxwikstrom.se)

Optimizing card visuals in slow Power BI reports - SQLBI

DirectQuery optimization scenarios with the Optimize ribbon in Power BI Desktop - Power BI | Microso...

 

If you have other questions, please feel free to contact me.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.