My Top 10 Tabular Data Model Best Practices

This article heavily relies on the information of Michael Kovalsky’s blog article “Top 10 Power BI mistakes and their best practice solutions” published on his website elegantbi.com. The last three points are additions to Michael’s article, focused more on data model best practices, less focused on data model performance.

When working with Power BI, creating a well-performing data model is crucial for creating efficient and informative visuals. The performance of your data model can have a direct impact on the speed and responsiveness of your Power BI reports, and it is important to take steps to optimize your data model for the best possible performance.

Here are the top 10 best practices to help improve the performance of your data model. To check these rules, it is best to apply to your model the Best Practice Analyzer in Tabular Editor as described here: Best practice rules to improve your model’s performance | Microsoft Power BI Blog

1. Have only needed columns and tables in your data model. This will help to keep the data model lean and reduce the amount of data that needs to be processed. Unused columns and tables can slow down performance, and it is important to regularly review and remove any unnecessary columns or tables.
2. Use a clean star schema with one time dimension. A star schema is a type of database schema where a central fact table is connected to several dimension tables. This type of schema is well-suited to Power BI and can improve performance. The bare minimum for every data model is to have at least one date dimension, and almost every data model has somewhere a date column. In case you have multiple date columns in one fact table, such as order and ship date and therefore think you would need multiple date dimensions, I would still go with one date dimension and for such use cases add an inactive relationship in combination with DAX function USERELATIONSHIP for the measures which need the unusual date relationships.
3. Avoid bi-di or many-to-many relationships. These types of relationships can add complexity to your data model and slow down performance. Instead, use CROSSFILTER with IF to handle these types of relationships. CROSSFILTER can be used to control the direction of the relationship and can help to improve performance.
4. Move calculated columns and tables to the database, this can significantly improve the performance of your data model. By keeping calculations at the database level, you can offload computation from Power BI and take advantage of the processing power of your database engine.
5. Avoid transformations within the model. Adding unnecessary calculations, column creation, and data manipulation can slow down performance and make it harder to understand your data model. Keep your data model as simple as possible. and move all transformation back to your datawarehouse or if possible to the datasource. For example if you have a SQL database connecting to, than use a plain Select * From Table.
   • Yes “*” and not each column separately. This enables you to be fully flexible with the columns you add or modify in the backend.
   • Yes “Table” to improve perfromance further and not a SQL View. You can use a stored procedure to populate the table with the exact same logic you would have in the SQL View. This is important especially with direct query.
6. In DAX, avoid using “/”, SEARCH, IFERROR, CONTAINS, INTERSECT and FILTER, as these can slow down performance. Instead, use alternative functions that have better performance characteristics. Instead of “/” use DIVIDE. Sticking with the DIVIDE example, you can use the third condition in the filter statement instead of IFERROR and CALCULATE or CALCULATE + KEEPFILTERS instead of FILTER.
7. Use ‘Table’ prefix for calculated columns, not for measures, following a style guide can help keep your data model organized. By using a consistent naming convention, you can make it easier to understand and navigate your data model. Style Guide and adding the check for it in Tabular Editor if not already in Tabular Editors’s Best Practice Analyzer.
   • Calculated Column or Measure = ‘Table’[Column Name] or [Measure Name]
8. Reduce the number of measures in your data model by using calculation groups and visuals field parameters. Too many measures can slow down performance, and by reducing the number of measures, you can improve performance. But more importantly this will improve the datamodel’s usability as well. Calculation groups allow you to group similar calculations together or extend your data model with more measures, which otherwise would have to added manually as explicit measures. One calculation group which should exist in all data models is the Time Intelligence Calculation Group. You should be able to just copy paste the calculation group template if the names of your date column and date dimension match my template. Otherwise adjust the template and paste the tables afterwards into your model. Download the Time Intelligence Calculation Group Template here: https://github.com/KornAlexander/PBI-Tools/blob/main/Calculation%20Group%20Time%20Intelligence.bim
9. Add a last refresh time-stamp to your data model, this will help you keep track of when your data model was last updated. Please make sure to implement this per partition. This can be useful when troubleshooting performance issues, as it allows you to see when data was last updated.
10. Use empty measure tables, display folders and detailed row expressions to keep your data model organized and easy to understand. Empty measure tables allow you to organize your measures, while display folders allow you to group tables and columns together. In case you are using Analyze in Excel with your published model you will need to set up additional things:
    • Relationships need to manually recreated, with fictional columns having just one row. This is needed that the Excel report consumer will immediately know which relationships are possible if the empty measure table is selected. If you are using multiple fact tables, this will pose an issue, taking the maximum possible relationships will at least not hide possible attributes. This needs to be carefully managed, since you might show or hide possible relationships.
    • Detailed row expressions allow you to modify the details shown when you connect to your model in excel and apply a drillthrough with double click onto the value in your pivot table. If detailed rows expressions are not set for measures in an empty measure table than this drillthrough will show just the one fake row you have in your empty measure table.

By following these best practices, you can significantly improve the performance of your data model in Power BI or Excel, making it faster and more efficient for creating visuals and gaining insights from your data. It is always good to keep in mind that every dataset and every use case is unique and what works best for one dataset may not be the best solution for another. As you work with Power BI or Excel and your data model, it is important to regularly review and test your data model to ensure that it is performing as efficiently as possible.

It’s very important to keep in mind that best practices are general guidelines, but it’s important to test them and see how they work in your specific scenario and data model, as they may not always be the best solution in all situations.