For the better part of the last 2 years, I’ve been most of my time working “on the field” getting to know each and every user persona of Power Pivot, Power Query and Power BI in general.
This is one of the reasons why I didn’t post that much during the 2016-2018 period. I did a full research on my own to better understand the user personas, what their pain points are and how to better reach these with techniques and patterns that are applicable to them.
I’ve learned a lot from these people and one of the main situations that most new Power BI users that come from Excel face is the fact that they try to tackle their scenarios the same way that they would tackle them inside of Excel, which usually prevents them from taking the full advantage of what Power BI has to offer and at times it makes them waste way too much time in their initial steps because of preconceived ideas.
In this post I’d like to talk about the main scenario that I see and it has to do with overusing DAX for any situation that you can think of, why this happens and start the conversation on how you can avoid placing yourself or any of your colleagues in this position so you can fully take advantage of what Power BI has to offer which goes beyond just DAX.
I like to explain things with analogies, and in this case I’ll be using history to explain a bit about why new Power BI users who come from Excel tend to go towards learning or using DAX to tackle most of their problems.
The History of the Panama Canal
As some of you are aware, I’m from Panama City, Panama. My mom and dad were born here and so did my grandparents.
Apart from Mariano Rivera (wiki) and Roberto Durán (wiki), the Panama Canal is what we’re usually known for.
I’m going to overly simplify the history of the Panama Canal, but it goes a bit like this:
In the XIX century there was a team of people led by Ferdinand de Lesseps (wiki) who engineered and constructed the Suez Canal (wiki) – a sea-level canal that joined two seas. The Mediterranean and the Read Seas.
This same group of people and company saw an opportunity in Panama – to do exactly the same that they did for the Suez canal. A canal that would join the Pacific and the Atlantic Ocean through Panama.
This team acquired the rights to start building the Panama canal and they started working on it on 1882 – They created what was the Panama Canal Company where they used the SAME methods, techniques and procedures that they used for the Suez Canal without ever acknowledging how vastly different Panama was / is from Egypt.
The French Plan for the Panama Canal
How did the land looked before the works of the canal started? The state of the land where the Canal would be created looked like the image below prior to any developments.
As you can tell, it wasn’t an even landscape.
What was the French Plan for the Panama Canal? “If it worked before, why change our ways?” this is the mindset the French company had. They chose to go with a sea-level canal.
They made it work for the Suez Canal and they had enough experience to tackle other projects that would require a sea-level canal approach and, from their initial surveys prior to the start of the project, it seemed like a sea-level canal would be feasible.
In theory, this would’ve worked. They could’ve made things work using the same approach that they used for the Suez Canal, but they didn’t survey the area enough, not because they didn’t want to, but because resources were scarce and the technology wasn’t available.
The main reason why the French folks failed is because they never expected things like epidemics (malaria and yellow fever), recurrent landslides, and corruption to have such a massive impact on them. They were simply not prepared for these things or never accounted for them.
In 1889, the Panama Canal Company declared itself in bankruptcy and the project was suspended. The project had a progress of around 40%, but at this time the company led by the French failed to deliver on their promises and it was a pretty big scandal in France back then.
A new Plan by the USA: A lock-based canal
The USA saw the opportunity and decided to acquire the rights to finish the project. Theodore Roosevelt had a tremendous interest in this project as it would position the USA with a crucial landmark and pathway that would be the bridge between worlds (sort of speak).
The New Panama Canal Company was established in 1904 and it started working almost immediately. This company learned from the mistakes of its predecessors and it did extensive research and surveys to fully understand what was so different about Panama from the rest of the world and what would this project need, within the Panamanian environment, to work most effectively.
A lock-based canal system was the right call. One of the biggest differences between the Panama Canal and the Suez Canal is its length. The Panama Canal is 80km and the Suez Canal is 193km.
So if it’s shorter than the Suez, why did it require so much more effort? that’s because there’s quite a big difference between the terrains through which the Canal had to be created and the logistics behind it.
What does this has to do with Power BI at all?
Using that bit of history as an analogy, an average Power BI user comes from Excel where they use the Excel formula language to work with. This means that they’re using the “sea-level” canal approach where they should be using the “lock-based” canal approach.
The Excel Formula language (sea-level canal approach) has worked for them forever. In this case, you can say that they’ve created their own Suez Canals before using the traditional Excel Formula language.
How does an Excel user try to tackle their own “Panama Canal” project in Power BI as a new user? Well, DAX looks A LOT like the Excel formula language, so they start from there and don’t really pay much attention to the rest of the features and options within Power BI.
Not to mention that when you use any search engine, the first thing that most people talk about on any communities or forums is DAX, so even doing a bit of available research points you into learning DAX.
Similar to the approach that the French folks had with the Panama Canal, their initial approach could potentially work, but it would only get you so far. Using solely DAX to solve your problems can give you the right solution, but it may not be the most efficient or flexible way if you’re trying to create a robust and long lasting solution, no to mention that in some cases going this route would make you waste hours of work on something that should’ve been done in minutes or even seconds.
Panama recently did an expansion to the Panama Canal. Basically a new set of locks for the “Panamax” and “Neopanamax” ships (wiki) – can you even imagine how complicated the expansion would’ve been if it would’ve been an expansion over the finished French project?
What did USA did that the French company didn’t? They learned from their predecessors and decided to invest their resources in doing a full research of Panama and what would be the best way to work with the Panamanian territory and its environment.
What can you do as a new Power BI user to better understand your new “Panama Canal” project (Power BI) and make the best approach? The main goal is to make a shift on how you approach things inside of Power BI. This Power BI approach is fundamentally different from the approach that you commonly have / use within Excel for the same scenario.
I have an article that I highly recommend you read (here) where I give you that needed research before taking a deep dive into Power BI. It’s an article about the ‘Heart of Power BI’ – this will give you a better overview or big picture of the components of Power BI, where you should focus your efforts, and how to approach your projects.
Understanding the big picture will lead you to make better decisions. Once you better understand how Power BI works, you’ll learn how to better tackle each scenario (even new ‘Canal Expansion’ projects) where most of the time DAX is not the main focus of attention, but rather your Data Preparation and Data Modelling phases and techniques.
The main takeaway that I want you to take from this is that you shouldn’t approach your scenarios with Power BI the same way that you’ve approached them before in Excel.
While DAX does look familiar, the true power of Power BI lies behind the concept of a Data Model which goes beyond just a DAX measure or Calculated Column.
I don’t want you to leave this article thinking that DAX is bad. Is completely the opposite – that’s how most people fall in love with Power BI, because of how familiar and approachable DAX looks like, but at the same time, like any other tool, it has it pitfalls and sometimes paths that look deceivingly the best based on our preconceptions, hence why we need to invest more time learning about the the concept of a Data Model and perhaps Data Preparation before making any choices that would have really BIG consequences in the future.
I remember my first real attempt at using Power BI in 2018. It took a long time to write all those 200 DAX measures.
Weeks later I realised I could replace all 200 measures with 1 measure. That was a valuable learning experience 😀