Top Five Mistakes to Avoid When Developing Power BI Reports

Because Power BI is an easy-to-start platform and you don’t need an extensive learning curve to get started with Power BI Desktop, we often see some bad practices in the implementation of Power BI reports. In this article and video, I explained the top five mistakes that I see happen a lot in building Power BI reports and I strongly suggest you avoid them.

Video

Mistake 1: Making all the relationships Both-Directional

I often see in my consulting engagements that there is a model with many of the relationships set as both-directional. This is a big mistake. A both-directional relationship will bring two problems into your data model;

1. Performance issues (it will make your report’s response time slower)
2. Ambiguity in the model and relationship

A relationship in Power BI is often for when we want a table to filter another table (based on the selection of items in the visuals). This filtering happens in the direction of the relationship. Meaning that if DimCustomer has a one-to-many relationship with the FactSales, and the relationship direction is single directional from the DimCustomer to FactSales, then DimCustomer can filter the FactSales, but not the other way around.

What we see a lot in the Power BI models is that the developer changes the relationship to both-directional so that each of the tables can filter the other table. This looks like a nice feature, but it will come at a big cost.

Normally one of the main reasons for a both-directional relationship is problems in the design of the tables. A star schema is a preferred approach in which dimension tables filter fact tables.

Solution: Use Single Directional relationships, Build a proper Star-Schema dimensional model, and use Visual Filters and DAX for the scenarios where you need a filter in a different direction.

Learn how to avoid both-directional relationships by reading the articles below;

• Back to Basics: Power BI Relationship Demystified
• What is the Direction of Relationship in Power BI?
• One Dimension Filters Another Dimension in Power BI

Mistake 2: Load Data without Transform

This mistake often happens because Power BI enables developers to connect to many different data sources. This means that a Power BI report developer can connect to the source CRM system instead of the data warehouse and pull the data inside the report. This ability is great for self-service users. However, loading data without transforming also come with a big cost of performance and other complications in the data model.

I see sometimes data models with 50 or more tables loaded in Power BI and often with many both-directional relationships. When you bring the data tables without any transformations, you will need more relationships and the model also gets more complicated. Sometimes your DAX calculations will also become more complex. If you are pulling data from a source operational system, you have to transform it, create new tables, and change the layout and design of tables. This process is called dimensional modeling.

Without a proper dimensional model, you will have hundreds of tables, your model will be complicated, you might need to use both-directional relationships, and the performance will go down. Maintenance of this model will be time-consuming and it would be harder to write calculations over it.

Solution: Change the data model. Create fact and dimension tables in a star-schema model, flatten some of the tables to create dimension tables and create one-to-many from dimension tables to fact tables.

Learn more about the solution in the below articles;

• Basics of Modeling in Power BI: What is a Dimension Table and Why Say No to a Single Big Table
• Basics of Modeling in Power BI: Fact Tables
• Power BI Basics of Modeling: Star Schema and How to Build it
• Build Your First Star Schema Model in Action: Power BI Modeling Basics

Mistake 3: Writing Calculations Instead of Transformations

In the Power BI environment, the calculations are much more visible than the transformations. When you are in the Power BI Desktop environment, you can easily find the place to create a calculation. But you cannot easily find where to do data transformations. That is why many Power BI users tend to create calculations instead of transformations.

This can be explained best as an example. If we have a budget table like the below;

After importing the table into Power BI, you will need to create many calculations, such as below:

• Budget of Year=Mth1+Mth2+….+Mth12
• Budget of Quarter 1=Mth1+Mth2+Mth3
• Budget of Quarter 2=Mth4+Mth5+Mth6
• Budget of Quarter 3
• Budget of Quarter 4
• Budget of Half Year 1=Mth1+….+Mth6
• Budget of Half Year 2
• ….

You will need to create the calculations above and perhaps some other calculations, only because the data is not designed in the right way.

The better approach would be TRANSFORMing this data. In this case, Unpivot would be a good transformation to choose. Change it to a structure like the below;

This table now can be connected to a Date dimension, and easily sliced and diced by attributes (columns) of that date dimension including year, quarter, half-year, etc.

Solution: Use Transformations to re-shape the data, and use calculations only after that for extending analytics.

The above is an example of how using a transformation can save you from creating a lot of hard-coded calculations. To learn more about Power Query Transformations, I suggest taking a look at the set of articles below;

• Get Started with Power Query: Movies Data Mash-Up
• Power Query articles
• Why data preparation and transformation? Why Power Query?
• Pivot and Unpivot with Power BI

Mistake 4: Overuse of DAX Measures

DAX Measures are great, they are dynamic and make your report much more insightful. However, a measure is evaluated runtime and will reduce the performance if you have hundreds of these measures shown on a report page (in a table visual or matrix visual for example).

Some of the calculations can be pre-calculated using a calculated column, in Power Query as a custom column, or even back in the data source. If you can push calculations as much as possible to the back end as pre-calculation, then the performance of measures will be improved significantly. You can also leverage calculated tables and aggregations for a similar purpose.

Solution: Implement parts of the calculations as pre-calculations in Power Query or data source.

To learn more about the difference between different types of calculations in Power BI in the below articles;

• M or DAX? That is the Question!
• Measure vs Calculated Column: The Mysterious Question? Not!
• Why my Power BI Matrix or Table Visual is SLOW

Mistake 5: Re-Write or Copy Instead of Re-Use

At the beginning of developing Power BI reports, you will perhaps have one instance of each entity, object, function, calculations, etc. However, as time passes, you will realize that you may need another instance of a table or calculation. It is a big mistake to re-write or copy it instead of re-using what you already developed.

An example of this is a date dimension. The date dimension is a calendar table that is needed in many of the Power BI reports. If you have developed it once, then re-creating it may not be exactly like what you already have. copying it also means that if you want to change it in the future, you have to change it in two places. You have to find a way to re-use it. Re-creating or copying comes at the big cost of maintenance. Any future changes would cost more time.

There are certain methods in Power BI to re-use components. For example, a table that is needed in more than one Power BI file can be created in a dataflow. And then the table can be re-used in multiple files as a single version of truth. A calculation created in a model can be used in multiple reports using a live connection to the dataset or composite models with Direct Query to the Power BI dataset. If a setting is applied in a visual, it can be implemented as a theme so that it can be applied to more than one visual easily.

Solution: Re-use your work using Dataflows and shared datasets, Themes, templates, and other components.

To learn more about re-using some of the Power BI components, read my articles below;

• Move Your Shared Tables to Dataflow; Build a Consistent Table in Power BI
• Power BI Shared Datasets: What is it? How does it work? and Why should you care?
• Power BI Architecture for Multi-Developer Tenant Using Dataflows and Shared Datasets
• Power BI Theme: Customize Colors, Fonts, and Visual Appearance for Multiple Objects

Summary

The above are some of the most common mistakes I have seen Power BI users make when developing Power BI applications. There are of course much more mistakes and if I wanted to list everything it would be a book most likely. Have you faced any of these challenges? What is the top mistake(s) you have faced? let me know in the comments below.

I provide training and consulting on Power BI to help you to become an expert. RADACAD team is helping many customers worldwide with their Power BI implementations through advisory, consulting, architecture design, DAX support and help, Power BI report review and help, and training of Power BI developers. If you need any help in these areas, please reach out to me.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.