Forum Discussion
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.
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:
- 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.
- 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.
- Keep DAX measures simple
- The report layout should be carefully planned, and try to limit the number of visual objects on a single page.
- 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".
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
If you have other questions, please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf 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!
1 Reply
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:
- 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.
- 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.
- Keep DAX measures simple
- The report layout should be carefully planned, and try to limit the number of visual objects on a single page.
- 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".
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
If you have other questions, please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf 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!