Let’s start thinking in a Power BI mode
Today I want to talk not about tips and tricks, but about philosophy and strategy.
In recent years, both working with my customers or chatting with the community, I realized that often the problem is not so much (or not only) of technical nature, but it is rooted in a wrong approach to the Power BI project.
I’m talking about a project and not just a report, precisely because this activity must be seen as a real project.
In fact, a Power BI project consists of several elements, each with its own characteristics, but all designed to ensure the soundness and efficiency of the project as a whole.
In this sense, I’ve always thought that the metaphor of the Gothic Cathedral, full of gorgeous elements that participate in the beauty of the whole, works perfectly for Power BI.
For a Power BI project to work well, each part must be developed according to recognized best practices, but first and foremost, it’s critical that each part does what it was designed to do.
Over the years, however, I’ve found that this overview is too often missing.
In this case, we can divide the wrong approaches into two macro categories: head-chargers and monotheists.
The approach used by head-chargers is to approach a power bi report as a long, one-way street.
From data sources to Power Query, to DAX, to visualizations, always moving forward trying to solve problems at the exact point where they occur.
If they end up in a dead end, too narrow or bumpy, they try to disassemble the pieces of the machine to get it through a too narrow passageway or through the rough terrain at the edge of the road anyway. When instead the solution would be much simpler and more elegant: all they have to do is turn around and go back to the turn where they took the wrong direction and take the right one.
Monotheists, on the other hand, are those who feel strong in one of the two Power BI languages, Power Query/M or DAX, and try to solve everything with that. There are those who try to do aggregations or calculate cumulative values in Power Query and those who try to solve modeling problems with pyrotechnical (and very difficult!) evolutions in DAX. Maybe someone finds ingenious solutions, but the result is always something unstable, forced, not performing, tiring, limited.
And sooner or later, the knots come to the comb.
Ladies and gentlemen, I’ll let you in on a secret: this is not how a Power BI project works.
Every component of Power BI is designed for a reason and to perform a task to the best of its ability. So let’s make them do it.
Power Query is designed to import, manipulate, clean, and combine data to best prepare it for use in a data model.
The data model must be designed to ensure that it is as flexible and scalable as possible, and that all necessary steps are taken.
DAX is a language designed to do aggregations, from the simplest to the most complex, and in that it is almost unbeatable.
Before launching into development, you need to design, to be clear about what you need to do. Then, during development, you always have to keep this vision at a high level, in order to properly distribute the activities.
If I encounter a problem, I have to understand the nature of the problem and based on that I have to intervene at the exact point, which is often not where it occurs.
If I’m getting into a particularly complex DAX measure, it’s always good practice to ask if this complexity isn’t due to poor model design.
A fundamental law of Power BI states that the better the model, the simpler the DAX.
If I have a problem with blank values in the dimensions, it means that I have a problem mapping the keys in the dimension to the keys in the fact table. This is not a problem to be solved with DAX: it’s a matter of cleaning up the data. So to be solved in Power Query. Adding to the dimension a dummy value with the description we want and properly valuing the null or unmapped keys in the fact table.
If we need to calculate the cumulative of a value, we don’t calculate it in Power Query, but import the data at its natural granularity and then use Time-Intelligence functions like TOTALYTD.
If we need to do time calculations, we don’t invent complex calculations, but we equip ourselves with a solid calendar table and convert the time values in the fact tables into days, whatever their granularity, so that we can build the direct relationships with the calendar table and take advantage of DAX’s native functions.
To avoid making all these mistakes, it is critical to act strategically, to plan, to design.
Planning means putting the model at the center, which will be our keystone that will support the whole system. Once the correct model is defined, both the Power Query and DAX parts will come accordingly.
Defining the model means first of all identifying exactly what facts and dimensions and their relationships, trying to always follow the polar star schema.
In short … it’s critical to really start thinking in Power BI mode!
