Power BI: What is DAX? And Why you Should (or Should Not) Learn It

-

What is DAX?

        DAX stands for Data Analysis Expressions, it is language developed by Microsoft to interact with data in a variety of their platforms like Power BI, PowerPivot and SSAS tabular models. It is designed to be simple and easy to learn while exposing the power and flexibility of tabular models. In a way, you could compare it with Excel formulas on steroids. Using DAX will truly unleash the capabilities of Power BI.
          Even though I am still far from a DAX expert myself, I have reached a point where I use a lot of the code in my daily job and have colleagues starting to ask more and more questions about it. For this reason, I chose to write this article on why you should(n’t) make use of this tool out of the data science/data analysis toolbox.
            Learning DAX as a Power BI user is much like being an Excel user and discovering how to use formulas, You were able to structure your tables, add some charts and click the sum/average/… button (Σ) but suddenly you discover the world of VLOOKUP, IF functions and whatnot. However, this comparison is not completely valid since, or at least in my opinion, Power BI is already an incredibly powerful tool even without DAX, as in contrast, anything beyond light use in Excel already requires formulas.
            That being said, learning DAX will open up a new world of Power BI for you. The most import feature you will unlock is being able to select, join, filter,… data in a dynamic way. This means that the dashboard can take input from the users and use it to dynamically generate calculated columns, measures and tables.

Fewer Headaches

          It doesn’t take a lot of experience to reach a point where you are cursing at your screen, because your dashboard does not give you the results you expected. Once you know how to use DAX you will be surprised at how many of these headaches you can avoid, or completely bypass (in some hacky way).
            A very simple example of this is the ‘blank’ value in the card widget. When displaying numerical data in a card, for example ‘revenue’, it will return ‘blank’ if you set your filters in a way there is no revenue to show. However, a more natural way to display ‘no revenue’ should be ‘0’ instead of ‘blank’. With a very simple DAX expression, you can yourself create a measure adding a ‘0’ to the formula, meaning you will never have to see ‘blank’ again.
              This is one of the many ways a tiny bit of code can greatly improve the user experience.

Speed Up Your Dashboard
            The better you get at DAX, the smarter you can make your dashboard. By using DAX you can create smarter calculated columns and/or measures by which you can limit the data the dashboard has to fetch and visualize. Even though some DAX expressions can test the limits of the data engines, a well written expression can speed things up, thereby limiting the usage of resources. For some other ways to speed up your dashboard without using DAX, you can read these 5 tips I shared a couple of months ago.

DAX Is More Than Power BI

        If you would take the time investment of learning DAX, your newly acquired skill does not need to limit itself to a Power BI environment. DAX can be used in tabular Microsoft products like:

·         Power BI

·         Microsoft Analysis Services

·         Microsoft Power Pivot for Excel

Not to mention the DAX syntax is also very similar to Excel formulas, making the knowledge also transferable to this good old, widely used piece of software.

It Makes You a Better Data Professional

            Even though DAX can only be used in an environment that supports it, the skill of knowing how to use DAX goes well beyond its scope. As DAX is based on a system of different nested filter contexts where performance is key, it changes your way of thinking about tables and filtering data. By writing a smart piece of DAX code in the morning, you might be able to improve the performance of some Python code you wrote earlier in the afternoon. In other words, by learning DAX you will improve your way of thinking on how to efficiently merge, filter, select and manipulate data.

Why You Shouldn’t Learn DAX

            Although I am happy to study DAX and I just listed some compelling reasons on why you should start using it, I must add that DAX isn’t for everyone. These are some reasons why learning DAX shouldn’t be a priority for you:

Steep Learning Curve

            It won’t be possible to fully understand DAX overnight. Although you can quickly start writing some basic code, it will definitely take time to understand how the different filter contexts interact, etc. (something of which I often still struggle with myself). You need to decide for yourself how much you would be using it to see if it's worth putting in the effort.

You Can Do a Lot With Alternatives

            There is a lot of data manipulation possible in DAX even before your data ends up in one of the widgets. For anything that does not have to be dynamically generated, there are a lot of alternatives. For example, adding some new extra columns to your dashboard can be done just as easily with Python.

Dashboards Can Become Cryptic for Outsiders

            Adding DAX to a dashboard adds a layer of complexity. If you are the only one building or manipulating the dashboard this shouldn’t be a problem, but for those working in teams, it might complicate things. I personally experienced colleagues unfamiliar with the language getting stuck in using dashboard manipulation caused by DAX confusion.

            The desire to simplify things should not be a good reason to stop moving forward, but it is something you should take into account when taking this next step in Power BI.

The 80/20 Rule:

            The pareto principle or 80/20 rule states that 80% of the result can be realized with 20% of the effort and vice versa. Hardcore DAX’ers will not be happy by reading this but I believe 80% can be done without DAX. 

            Power BI is a powerful tool, where even beginners can create useful dashboards and insights. Of course, more advanced dashboards will absolutely rely on a big partition of DAX, but a lot of dashboards are fairly simple and can answer the users need without extensive code. This means that many for many Power BI users the investment is simply not worth it. In today’s world of freelancing platforms, 24/7 connectivity, digital nomads and whatnot, it might be easier to just outsource the DAX part of your dashboard to a professional.

Where To Start Learning DAX:

            Alright, so it seems I have convinced you to dive into the world of DAX. That’s good news since a larger community benefits any software environment. At this point you are probably wondering where to start; Well, granted learning DAX is challenging but not complicated per se. It will take time and effort to understand the concepts, but nor will you need a PhD in Computer Science to get started.

          No worries, I am not going to pitch you some expensive online courses. As with most (if not all) programming languages there are an abundance of free sources, documentation, videos and communities online that can teach you everything there is to know. Some useful sources I frequently visit are:

·         Power BI community: the source 

·         r/PowerBI: Power BI’s subreddit

·         The official DAX documentation

·         https://dax.guide: Some more great documentation

·         DAX Formatter: Free tool to make DAX code more readable

            Finally, aside from these free sources, I do strongly recommend reading The Definitive Guide to DAX by Marco Russo and Alberto Ferrari, which can be considered the bible of the language.

            Well there you have it, I hope this article helps you decide whether or not you want to dive into the world of DAX and if so, where you can get started.

        

Comments

Post a Comment

Popular posts from this blog

4 Ways to Transform Data in Power BI Desktop

-
Image
  While making transformations in Power BI Desktop there often arrives a question which is the best method/place to do the transformations? Transformation method you choose becomes critical if you are dealing with large data sets (million+ rows) and can impact performance of your model and overall user experience. Below are some of the methods you can adopt. 1. Relational Database: If you are connected to any of relational databases like Microsoft SQL Server, Oracle DB, etc and you have the access to make transformation within the database then it is the best place to do the transformation before loading that data in Power BI environment. 2. Native Query (within Power Query): Another method which is less talked about is using native query. • Using this method you can make the necessary transformations using SQL script. • The native query fetches the transformed data directly from the database(like SQL Server, Oracle) hence avoids/minimizes your transformations in Power Query. 3. Po...
Read more

10 Pandas Functions for Faster Data Science

-
Image
  1 . read_html() Web scraping is one of the key processes that brings people to Python. Lots of people don’t know that Pandas has a web scraping function. With read HTML, all you have to do is pass in the name of the URL and you can access that data on that web page. Here is the  full documentation .                                  pd.read_html("URL") 2. corr() This function will return a correlation matrix for pairs of numeric columns.                                  df.corr() 3. drop_duplicates() Getting rid of duplicate values is an important step in data analysis. There are lots of convoluted ways to do it, but Pandas has a super simple function you can use. Here is the  full documentation  for the function.                         ...
Read more

Why does DATA need a massage?

-
Image
  A bird-eye overview of the basic principles of data transformation: This post is a part of the “Analytics for startup” series so it might be helpful to the ones who just started figuring out how to build their own BI solution. I tried to answer the most common question that I frequently hear at the beginning of the BI implementation stage. Why do we need to do any data transformation? A very fair question having the fact that the data transformation can be the most challenging and the most time and money consuming part of the implementation. This is not any kind of tutorial but a bird-eye overview of the basic principles. Why do we need to transform the data? We don’t need to, actually. If the source is just one simple system or a couple of google sheets, then we don’t. In reality, though, we normally have many sources from the very beginning, e.g. some kind of CRM, Google analytics, google sheet, a database of our product, a website, etc. The main ideas of the transformation are...
Read more