Power BI Best Practices

Certainly, here’s the information presented in a pointwise format for easy reference:

1. Limit the Number of Visuals:
— Keep visuals to around eight per report page.
— Use one grid per page.
— Limit tiles to 10 per dashboard.

2. Use On-Premises Data Gateway Standard Mode:
— Choose standard mode over personal mode for large databases.

3. Separate Gateways for Live Connection and Refresh:
— Use different gateways for live connections and scheduled data refresh.

4. Limit Complex Measures and Aggregations:
— Prefer calculated measures over calculated columns.
— Push calculations to the source whenever possible.

5. Use Star Schema:
— Choose Star schema over Snowflake schema when designing data models.

6. Use Slicers Sparingly:
— Avoid excessive use of slicers as they generate additional queries.
— Each slicer generates two queries: one fetches the data and the other fetches selection details. Adding too many slicers drastically slows performance.
— Use the Filter pane to evaluate and remove unnecessary slicers.

7. Ensure Reports and Data Sources Are in the Same Region:
— Place reports and data sources in the same region to reduce network latency.

8. Import Only Necessary Fields and Tables:
— Import only essential data to keep the model lean.
— Consider partitioning large tables, process multiple partitions in parallel.

9. Use Templates (.PBIT Files):
— Templates speed up report development and ensure consistency.

10. Reduce Queries:
— Enable query reduction settings for slicers and filters.
— For slicers, select the “Add an Apply button to each slicer to apply changes when you’re ready” option.
— For filters, select the “Add a single Apply button to the filter pane to apply changes at once (preview)” option.

11. Avoid Bi-Directional and Many-to-Many Relationships:
— Be cautious with these relationships against high-cardinality columns.

12. Avoid Floating Point Data Types:
— Floating point data types can introduce unpredictability and decrease performance. Incase of floating point data types, restrict rounding off to fixed decimal places.

13. Replace Auto-Generated Date Tables:
— Replace auto-generated date tables with custom date tables.
— Split date and time components for better data compression.

14. Set IsAvailableinMdx to False:
— Disable attribute hierarchy for measure columns and unused columns.
— This reduces data size and load time.

15. Reduce Data Loaded on the Page:
— Use bookmarks, drill through pages, and tooltips to display relevant data. This improves page load time, especially for landing pages.

16. Use Report Backgrounds for Static Images:
— For static images, use report backgrounds to reduce performance overhead.

17. Choose Storage Mode Appropriately:
— Select the right storage mode for tables based on data source and query requirements (Import/DQ/Live Connection).

18. Cross-Check Referential Integrity:
— For Direct Query Sources, verify “Assume Referential Integrity” settings in relationships. By default, this value is set to “Off”. When it is marked as “Off” it performs Outer Join instead of Inner Join which can be slower.

By following these points, you can enhance the performance and efficiency of your Power BI reports and dashboards.