Power BI cheat sheet

This page provides clear and opinionated guidance for efficiently managing your data in Power BI and Databricks to optimize query performance and create efficient dashboards.

Connect Databricks and Power BI

Best practice	Impact	Docs
Use Power BI parameters when connecting to different Databricks environments	Allows flexibility when connecting to different Databricks workspaces or different Databricks SQL warehouses.	Connection parameters for Databricks
Use Databricks publish to Power BI service functionality	Enables seamless catalog integration and data model sync without leaving the Databricks UI.	Publish to the Power BI service from Databricks
Use Databricks Automatic Publishing to Power BI	Publish datasets from Unity Catalog to Power BI directly from data pipelines.	Power BI task for jobs

Choose the most appropriate storage mode

Best practice	Impact	Docs
Use DirectQuery for Fact tables and Dual for Dimension tables (not Import)	Generate more efficient SQL queries by using the most suitable storage mode.	Manage storage mode in Power BI Desktop Feature setup tutorial
Prefer DirectQuery over Import whenever possible	Allows you to maintain governance and audibility.	DirectQuery in PowerBI
Use composite models for mixed storage modes	Allows mixed usage of DirectQuery, Dual, Import mode tables, and Aggregation and Hybrid tables.	Composite models in Power BI Desktop
Use hybrid tables for aggregated historical data with real-time data	Enables efficient in-memory queries.	Hybrid tables

Optimize data access

Best practice	Impact	Docs
Use user-defined aggregations	Improves query performance over large DirectQuery semantic models by caching pre-aggregated data.	User-defined aggregations Feature setup tutorial
Use automatic aggregations	Continuously optimizes DirectQuery semantic models by building aggregations based on Query History for maximum report performance.	Automatic aggregations Feature setup tutorial
Use table partitioning or incremental refresh	Allows importing data faster and managing larger datasets, especially for very small, static and performance sensitive (less than 2 seconds) reports.	Tabular model partitions Incremental refresh Feature setup tutorial
Add Apply all slicers and Clear all slicers buttons	Prevents unnecessary queries by leveraging query reduction settings when users interact with report filters.	Apply all slicers and Clear all slicers buttons.
Use Assume referential integrity when defining table relations if referential integrity has been validated in the upstream ingestion	Enables more efficient join strategies in SQL queries.	Set Assume referential integrity

Fine-tune your data model

Best practice	Impact	Docs
"Move left" transformations	SQL views leverage the power of the Databricks SQL engine for more efficient report execution compared to PowerQuery transformations and DAX formulas.	What is a view? Feature setup tutorial
If you must use DAX formulas, optimize DAX formulas and avoid large result sets.	Prevents inefficient calculations that lead to deteriorated performance	Best practices to improve model performance
Avoid DAX calculated columns and calculated tables in semantic models and define this data directly in your Gold tables	Precomputed measures perform best in the Gold layer	Power analytics with the gold layer
For DirectQuery, check for query parallelization configuration settings	Improves query parallelization and maximizes utilization of SQL warehouse to improve overall performance.	Query parallelization for Direct Query mode Feature setup tutorial
For DirectQuery, check how many queries Power BI can send in parallel to Databricks	Ensures the Databricks SQL warehouse is sized accordingly to handle the required level of parallelism to avoid queries being queued, resulting in a slow report.	Sizing a serverless SQL warehouse

Monitor performance and metrics

Best practice	Impact	Docs
Use Power BI Performance Analyzer to examine report element performance	Identifies the visualization that takes the most time to load and where the bottleneck is.	Use Performance Analyzer
Evaluate the following properties of Power BI semantic models: Maximum connections per data source Maximum number of simultaneous evaluations Maximum number of concurrent jobs MaxParallelismPerQuery	Fine-tunes the performance of the model.	Maximum number of connections Evaluation configuration settings Query parallelization for dataset performance