What is DAX?
DAX (Data Analysis Expressions) is a functional programming language used to manipulate with several Microsoft BI tools.
Microsoft BI tools that use DAX:
- PowerBI (Tabular model)
- Excel (PowerPivot)
- Analysis Services (Tabular model)
With DAX you are able to “talk” with the data model, adjust it using calculated columns or return results from it with the help of Measures. Knowing DAX, as stated by many Microsoft BI experts, is the most important analytics skill in PowerBI and Excel.
DAX uses a data model framework to communicate with. The data model is a structured set of tables connected in a meaningful way.
A good data model is a prerequisite for optimal DAX code. When writing DAX you should always balance code with model structure. In case your DAX code becomes too complex, you should try altering the model to help maintain DAX within the scope of complexity you can understand!
We already stated DAX is a functional language, meaning it hides most of the complexity which comes to light when using other types of programming, such as object programming. In object-oriented programming, complexity emerges from defining objects/classes/methods/members and the rules between them. There aren’t any of those in functional programming, you only have to understand how functions work. And functions have a much easier general explanation.
There is always an input to a function, such as a table, column, record, expression, value, etc., and an exit of the function which changes the input values in a specific way as determined by the function documentation. The part between function input and output is encapsulated in a Function name, hiding all of its complexity from the regular user.
Basically, all you need to know is what to push into a function and what the function returns as a result of that input. How it does it (the harder part) doesn’t concern you.
This is the reason functional programming is getting so much traction nowadays. Regular users can combine as many functions as they want to produce the necessary results without all the complexity involved in understanding how the functions operate under the hood.
Why should you learn DAX?
Being the most important language of the modern Microsoft BI stack should give you enough reasons to invest in learning its functionality and capabilities. With the proper knowledge of DAX, you will be able to define calculations that will answer most questions about your data in a concise and fast way. I would say DAX is the most advanced language one can use in a descriptive analysis, before going into a prescriptive one using Python, R, Microsoft AI Insights, or similar.
The demand for DAX coders is rising. At the same time, there still aren’t many seasoned DAX coders that have a profound understanding of its concepts and who can deliver fast and accurate calculations. I would say the reason for the shortage of DAX coders is that writing it effectively is probably the hardest part of the PowerBI ecosystem. On the other hand, a higher entry border is an opportunity for everyone wanting to invest time to properly learn DAX code. The fact there aren’t many experts in the field raises the price of their services.
PowerBI recently introduced “Natural language to DAX generation” which should be able to suggest DAX code through AI-assisted development. We should keep an eye on its development but we still have doubts about its effectiveness in specific real-life examples.
Is DAX hard to learn?
It’s harder to learn than other functional languages such as M and Excel formula one. Still, if learned properly, it shouldn’t be such a headache. The complexity of DAX does not come from hard-to-understand concepts, but from their(concept) interactions.
In any given calculation, there are multiple concepts interfering with each other while forming a final result. You need to have a solid understanding of each concept to be able to debug and write efficient code.
Learning DAX is like learning to ride a bike. you need to learn how to pedal, keep balance, break, and steer before being able to ride a bike for more than a few meters. If you know how to pedal, but not how to keep balance, you won’t get far on a bike. The same logic applies to DAX. You cannot write DAX without the full understanding of its main concepts such as filter and row context, context manipulations, row2filter context transformation and other mentioned in our guide.
The best way to learn DAX
The best way is to start by learning the basics. We are not talking about digesting a 700-page long book, but about reading a few articles/blogs/watching a few videos on its main concepts. Afterward, you should start writing code and try to understand the results that the code returns. This guide can kickstart your DAX journey by providing you with concise topic descriptions after which you should have a general understanding of DAX.
We tried to distill information into a compact whole. You will need to read through 100 pages of our articles and understand them well. For the purpose of explaining the concepts in the most understandable way, we tried to explain each of them in isolation. But do not let this method fool you into thinking you do not need to understand all of them. Even though articles might seem to be independent topics not linked to each other, in any slightly advanced calculation you will have to use multiple, if not all topics from our handbook.
Why DAX Guide
As mentioned earlier, DAX isn’t a simple language. The hardest part is that you need to know most concepts to be able to write a single more complex DAX calculation. There are many books on DAX that are too complex for someone who is just starting with DAX.
The problem arises because people stop reading complex books at a certain point. As a result, they are not acquainted with all the concepts they need to understand prior to writing any DAX measure. This leads to the trial-and-error type of learning DAX, which is the least favorable.
Solution: we’ve devoted our time to writing a handbook about the most important concepts, after which you should be capable to write your own code. You should have a solid understanding of DAX after you’ve read 100 pages written in a concise way. This online handbook should give you solid knowledge before you embark on reading other, more technical types of books that further explain each of mentioned concepts.
In this handbook, we are going to talk only about DAX in the imported mode. Most of the code also applies to Direct Query and Composite models, but we will not cover borderline cases when this statement is not valid.
We haven’t finished a few of our chapters, but we still wanted to share this guide with you and receive feedback. The idea is to constantly update it with more details, yet keep it concise and easy to understand!
DAX data types
DAX works with various data types, but the most used are date, decimal number, and text. Other types include whole number, fixed decimal number (currency), date/time, time, binary, true/false (Boolean). You can define data types prior to loading the data (inside the Power Query editor) or inside the data model.
It is extremely important to correctly define data types prior to working with DAX. If you don’t define data types explicitly, DAX engine will define them for you. However, this is not the best practice since it can lead to unexpected results. Many DAX functions and operators work with a particular data type. For example, SUM function only sums numbers, not text. There are also functions that work with different data types (e.g. text, dates), such as MAX and MIN.
DAX also offers formatting options, which is the way the data from a single column is presented inside the visuals (e.g. Datetime 1/1/2021 12:00 AM can be presented as Dec 2021). All this can be done inside the Data tab in Power BI, or inside the PowerPivot in Excel under the Format option.
Blank is a special value in DAX that represents an empty value. It is different from zero or an empty string. Blank values represent that there is no value, similarly to null values in SQL (or Power Query). By default, rows with blank values will not show up on visuals. This can be changed by enabling Show items with no data within the rows or columns of a visual.
You can create a blank value with DAX by using BLANK function. There is also a function ISBLANK, which can be used to test if the result provided is blank.
DAX does not offer code formatting out of the box. When you start writing DAX code, it is usually a very short and simple formula that does not require any formatting. Once you start writing more complex code, you will notice the importance of formatting your code. There are no standards in code formatting, so you can format it in the way you like. There is a free online tool called DAX formatter (by SQLBI) that can be used to format your code. Below, you can see the difference between non-formatted and properly formatted code (by using DAX formatter).
Be sure to always format your code, especially in more advanced DAX formulas.