How to do Optimization in Power BI
1) Remove unnecessary columns and rows: Filter the data which is only required for your report. It will reduce size.
2) Use Aggregate Table: It is basically helps to improve query performance for the large kind of dataset whenever we connected via Direct query.
3) Use correct data type for column: It will help to avoid unexpected results by improving the data integrity. One need to check and apply proper datatypes for each column.
4) Query Folding: Use Query folding to transfer your transformation step to the source side. It can also improve the data refresh time even faster.
5) Data cleaning: Clean your data as much as possible. handle properly missing values, errors, null values, unwanted space in column values (Trim), and outliers. because of Garbage in Garbage Out.
6) Disable Auto date/time: It cannot be a inbuilt setting but user have to choose Power BI option settings.
7) Disable load of intermediate table in power query: It can improve the performance by disabling because for certain data state we cannot use this.
8) Try to use the Star Schema model: It is always recommended to use the star schema model and if not convert from snowflake by using Related DAX or Merge in Power query for improving optimization.
9) Avoid Bi-Directional cross filtering: Try to avoid cross filtering in the data modelling and use the cross-filter Dax function to increase performance.
10) Avoid M:M relationships: If required, we can use a bridge table to improve the performance.
11) Avoid using calculated columns: Try to push the calculation at the source or at power query. If it is not possible, then use it, because Power query uses compression techniques to reduce the data size.
12) Use a separate calendar table: Avoid calendarauto to create a table because it takes the min date of your model. If your model has a DOB column from 1950, it will create a calendar from that date. Use the calendar function or create in a power query.
13) Write a reusable and efficient DAX: Don’t load a complete table for calculation. Always use specific columns while filtering.
14) Using variables: Variables is the best way to write formulas. It is also helpful in debugging and reusability.
15) Use measure: best fit for dynamic calc and execute in run time.
Happy learning! Don’t forget to tap the clap button a couple of times to support me and save the article for future reference. Connect with me on LinkedIn. Whatever you do, put your heart into it and success will follow.
