Top Five Mistakes to Avoid When Developing Power BI Reports

Top five mistakes in Power BI to avoid

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

A both-directional relationship in Power BI

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;

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.

snowflake

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;

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;

Budget table

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;

Unpivot Transformation in Power BI and Power Query

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;

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;

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;

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 on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

12 thoughts on “Top Five Mistakes to Avoid When Developing Power BI Reports

  • These are all tips when assuming you import data versus DirectQuery. Would love to see a write up when people are using DirectQuery.

  • Dear Reza, each of your articles helped me to work better. I really like to read each of them. I am happy now, that I found just the last 5th โ€œmistakeโ€, which I have to do better by developing my reports. Your books and blogs are written great and help me a lot. Thank you and please do not stop to write ๐Ÿ™‚

  • Thank you for a good article Reza, these are all things that I see. Simplifying the data and the modelling at the start is key to a good report. Not having a date table is another big mistake that I see a lot. Keep up the good work (you & Leila)

  • Valuable information! Especially about Reports,Clear workable tips with huge information for to follow while preparing reports.Thank you Reza Rad.

  • what an explanation reza.You are amazing.Thank you so much.All of your explanations are cristal clear will never end up in ambiguity(;)]thanks alot….

Leave a Reply

%d bloggers like this: