Optimizing Performance in Power BI: Best Practices and Strategies

This article explores key techniques to enhance Power BI performance, covering data modeling, DAX optimization, efficient visuals, and query tuning to ensure faster and more responsive reports.

Step 1:

Identify High-Memory Attributes and Remove Unused Fields Using Power BI Helper

Modeling Advice in Power BI Helper provides insights into optimizing your data model for better performance.

Columns in the model and their sizes (in MB) help analyze which tables and columns take up the most space. Suppose your model size is 4 GB, and 3 to 4 columns consume 600 MB, 500 MB, 450 MB, and 300 MB, respectively. Check and aggregate this at the custom SQL level.

Step 2: Unused Fields Identification – Detects fields that are not used in reports and suggests removing them to reduce model size.

Under the Visualization tab, we can see all the report pages and the fields used in visualizations.

Important Section: Fields that are not used in visuals and can be deleted.

These are fields that:

• Are not directly used in any visual.

• Are not used in any calculation that contributes to a visual.

• Are not part of any relationship in the data model.

Removing such fields helps optimize performance and reduce model size.

Step 3: Power BI Query Diagnostics.

Power BI Query Diagnostics helps analyze query performance by tracking how long queries take to run, identifying bottlenecks, and optimizing data loading.

How to Use Query Diagnostics: 1. Go to Power Query Editor → Click on Tools → Select Start Diagnostics

2. Perform actions in Power BI (e.g., refresh data)

3. Stop Diagnostics and review logs in the generated tables.

4. Once you stop the diagnostics, Power BI generates two detailed tables:

   1. Diagnostics_Detailed – Provides a breakdown of each query step and how much time each step takes.

   2. Diagnostics_Summary – Offers a high-level overview of query performance, including total time and data source activity.

   Key Insights from the Detailed Table:

   • Step Duration – Identifies which transformation step takes the most time.

   • Data Source Queries – Shows how much time is spent on external database queries.

   • Bottleneck Detection – Helps find slow operations like merges, custom functions, or complex transformations.

Optimization Tip: Focus on steps with high durations and check if they can be improved through query folding or by reducing transformations.

Query Folding is the process where Power BI pushes transformations back to the data source instead of performing them in Power Query. This improves performance by letting the database handle complex operations rather than Power BI.

Step 4: Optimizing DAX Queries for Better Performance.

DAX (Data Analysis Expressions) plays a crucial role in Power BI performance. Inefficient DAX queries can slow down report loading, increase memory usage, and negatively impact the user experience. Optimizing DAX ensures faster calculations and smoother interactions

1. Use Measures Instead of Calculated Columns

✅ Measures are computed only when used, while calculated columns increase model size.✅ Example: Instead of creating a calculated column for revenue (SalesAmount * Quantity), use a measure:

DAX

CopyEdit

Total Revenue = SUMX(Sales, Sales[SalesAmount] * Sales[Quantity])

2. Common Performance Bottlenecks in DAX and How to Optimize Them

---- Un-Optimized DAX Query: Performance Bottlenecks.

Prior YTD = SWITCH(

TRUE(),

VALUES('Sample Data'[Metric])= "Views" ||

VALUES('Sample Data'[Metric]) = "Product" ||

VALUES('Sample Data'[Metric]) = "Time_Spent" ||

VALUES('Sample Data'[Metric]) = "Registered" ||

VALUES('Sample Data'[Metric]) = "Clicks",

//// **Performance Issue 1: Redundant Calculation of Selected_Date**

// MAX is computed multiple times inside SWITCH, causing unnecessary recalculations

VAR Selected_Date = MAX('Monthly Table'[Month-Year])

//// **Performance Issue 2: Repeated CALCULATE Blocks**

// Each condition separately calls CALCULATE, leading to multiple queries on the same dataset

VAR Result = CALCULATE(

SUM('Sample Data'[value]),

YEAR('Sample Data'[month]) = YEAR(Selected_Date) - 1,

MONTH('Sample Data'[month]) <= MONTH(Selected_Date),

FILTER('Sample Data', 'Sample Data'[month] <= Selected_Date) // **Performance Issue 3: Unnecessary FILTER()**

// The filter is redundant because the conditions inside CALCULATE already handle it.

)

RETURN Result,

//// **Performance Issue 4: Repeating the same calculations in the ELSE block**

VAR Selected_Date = MAX('Monthly Table'[Month-Year])

VAR Result = CALCULATE(

SUM('Sample Data'[value]),

YEAR('Sample Data'[month]) = YEAR(Selected_Date) - 1,

MONTH('Sample Data'[month]) <= MONTH(Selected_Date),

FILTER('Sample Data', 'Sample Data'[month] <= Selected_Date) // Redundant filter again

)

RETURN Result

/ //Divide

//// **Performance Issue 5: Recomputing Selected_Date and Query Again**

VAR Selected_Date = MAX('Monthly Table'[Month-Year])

VAR Result = CALCULATE(

DISTINCTCOUNT('Sample Data'[month]),

YEAR('Sample Data'[month]) = YEAR(Selected_Date) - 1,

MONTH('Sample Data'[month]) <= MONTH(Selected_Date),

FILTER('Sample Data', 'Sample Data'[month] <= Selected_Date) // Another redundant filter

)

RETURN Result

)

--------Optimized DAX Query

Prior YTD =

// Storing the max selected date once instead of repeating it multiple times (Performance Improvement)

VAR Selected_Date = MAX('Monthly Table'[Month-Year])

// Using SELECTEDVALUE() instead of multiple VALUES() checks (Improves efficiency)

VAR Selected_Metric = SELECTEDVALUE('Sample Data'[Metric])

// Calculating total value for prior year with a single CALCULATE (Reduces redundant calculations)

VAR Result =

CALCULATE(

SUM('Sample Data'[value]),

YEAR('Sample Data'[month]) = YEAR(Selected_Date) - 1,

MONTH('Sample Data'[month]) <= MONTH(Selected_Date),

'Sample Data'[month] <= Selected_Date

)

// Calculating distinct month count for prior year (Previously calculated separately inside SWITCH, reducing redundant computation)

VAR Distinct_Months =

CALCULATE(

DISTINCTCOUNT('Sample Data'[month]),

YEAR('Sample Data'[month]) = YEAR(Selected_Date) - 1,

MONTH('Sample Data'[month]) <= MONTH(Selected_Date),

'Sample Data'[month] <= Selected_Date

)

RETURN

SWITCH(

TRUE(),

// Direct check on metric using IN operator (Previously used multiple OR conditions with VALUES, making it faster)

Selected_Metric IN {"Views", "Product", "Time_Spent", "Registered", "Clicks"},

Result,

// Preventing divide by zero error (Previously missing, which could cause calculation failures)

IF(Distinct_Months <> 0, Result / Distinct_Months, BLANK())

)