Optimizing Power BI Reports
Building performant reports is important in gaining trust with stakeholders and ensuring user adoption. Here are some practical and easy tips on how to optimize your Power BI reports! These tips will be broken down into two sections, Data Model and Report View.
1. Use STAR Schema
Power BI is built to perform most efficiently when using a STAR schema. By breaking out your data model into fact and dimension tables, your queries will be optimized in your report. These fact and dimension tables should be related to one another, not joined (aka flat file format), which will result in more performant reports.
2. Limit Data Model
The fastest way to optimize your Power BI report is to limit the number of columns to only the ones you need in your data model. Go through your tables in Power Query and determine what fields are being used. Delete these columns if they are not being used in any of your reports or calculations. Another way to limit your data model is to use Row Level Security when applicable.
3. Push Data Transformations Upstream
Building on the previous step, if you are able to move your data manipulations to your query, this will help your performance. Whether you’re doing calculations, formatting, or grouping at the appropriate granularity in your SQL, this will eliminate Power BI spinning its wheels on these items. If you end up doing your transformations in Power Query, try Query Folding.
4. Move Row Level Logic to Power Query
Move any logic that needs to be calculated at the row level to Power Query. Instead of using DAX to perform the more basic IF THEN calculations, use M to do the same thing. These fields will be included in your permanent data model brought in from Power Query instead of them being performed in the Power BI side.
5. Remove Calculated Columns
Calculated columns are a huge drain on performance. Every time you interact with your report, calculated columns are recalculated. Measures, on the other hand, are only recalculated when that specific measure are interacted with on the report. If possible move your calculated column to Power Query. If you can’t do that, make it a Measure. If you can’t do that, ask yourself why you need this calculated column.
6. Convert Multiple Measures to Variables
Instead of creating metrics that require using multiple measures, use variables. You can perform multiple calculations within a single measure using the VAR and RETURN functions. This will minimize the number of measures you use as well as increase performance for the metrics being calculated. If you are going to be repeating a variable multiple times then you can still create an independent measure for that value. See example of variables below.
// Measure =
VAR num = SUM(revenues)-SUM(expenses)
VAR denom = COUNT(customer)
7. Amend Dates and Column Types
Dates will show as mm/dd/yyyy 12:00:00am by default in Power BI. To cut down on characters in the data model, change the date types to Date to eliminate the Time portion. If you have integers with a large amount of decimal points, reduce the decimal places showing. Lastly, if you have text showing TRUE or FALSE, you can change these to be binary, so 1 and 0.
1. Minimize Visuals
When creating your report, try and use as few visual elements in your report as possible. This will decrease the amount of calculations that Power BI is performing when rendering your report.
2. Don’t Use Slicers (if possible)
Use slicers only where needed. Otherwise, use the filters available to users in the Filter Pane. Slicers are less efficient at returning required data than the Filter Pane. If you do use a slicer, make it a single drop down instead of a list. A list will pre-populate some queries in it and make it less efficient.
3. Use Performance Analyzer to Analyze
Power BI has a built in way for you to analyze the performance of your reports. In the View ribbon you can find the Performance Analyzer. By opening this pane and clicking Record, this will show you how fast your report renders when performing different functions in your report. You can also see what specific sections of your report are causing performance delays so that you can work on improving those sections.
Thanks so much for reading! If you have any comments, suggestions, or feedback make sure to email me at firstname.lastname@example.org.