DAX( data analysis expression)

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.

Writing DAX in Power BI (image source: microsoft.doc.com)
Why You Should Learn DAX

It Opens Up A Whole New World

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.

This must be relatable to some of you

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 visualise. 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 :)
Guy in a Cube: amazing YouTube channel with a ton of tutorials
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.?