DAX Series: 1.What is the DAX Language?

(You can also find the article on my website together with other articles and more interesting things)

This is the first article of the series: DAX Series — Learn the core of Power BI
1. What is the DAX Language?

As I promised I will post two articles every week about different aspects of the DAX language. One article in the middle of the week and one in the end of the week.

In the end of this article I have a link to my GitHub where you can find one empty Power BI Model and one full model with the measures and similar I have used in this article.

In this article I will describe what the DAX language is in Power BI. I will also give input in why it is important to learn the language. It will be more of a high level overview, where more detail will be in later articles.

You can also find the story here:
https://eliasnordlinder.com/power-bi/f/dax-series-1what-is-the-dax-language

So, what is DAX?

Well, it is not Haddaways newest single, it does stands for Data Analysis eXpressions.

In short, it is a functional programming language that is used for several different Business Intelligence tools. These are tools such as Power BI, Analysis Services and Power Pivot for Excel. It was originally developed in 2009 and then released with PowerPivot in Excel, but later used in Power BI.

The syntax for DAX is very different from the programming languages. I think that it mostly resemblances SQL, as it builds a lot on tables, aggregations and joins, and it shares some similarities as well to Excel, as Excel is also a functional language.

The DAX gods Marco Russo and Alberto Ferrari from sqlbi describes DAX as “a matter of querying a set of tables joined by relationships and aggregated values.”

Compared to other programming languages as Python or R, there are not that many similarities and you will need to take a different approach when working with the DAX language.

Look at the two examples of simple DAX syntax is below:

The first measure Summarize all the sales in the Order_Details table, while the second measure calculates the total sales for orders which have a ship country of Sweden in the table Orders.

On the surface, this syntax looks rather simple, at least compared to other programming languages, but it is very important to not be fooled by the simplicity.

The most dangerous thing with DAX is that you can write things that works, long before you understand what is happening behind the scenes.

Once again, sqlbi have a great blog post about this which you can visit now and come back to later when you have worked more with DAX as well.

DAX Analogy — School

I would like to draw an analogy between DAX and school. Let us pretend that you have very easy for a subject until you begin university. Let us pretend it is math for example, that you could always come up with the answers quickly without really knowing how you did it or to explain it.

At university you will get a great surprise when the professors are not satisfied with just giving the answer, but want to you to explain in detail how you came up with the answer. It is not only important to get the answer right , but you need to understand all the theory behind it works as well.

I believe that DAX shares a lot of similarities. As a beginner it feels very nice, you can write a lot of functions very quickly. You do not need to understand the building blocks of the language to write these functions and they, usually, works as you think they should. You will get the answer and move on quickly to the next measure in your report.

The problem is when you want to get into more advanced DAX, you want to “Begin university”. To be able to take the next step in DAX you really have to understand the theory behind the language, not just know the answer.

If you want to climb the deep incline in the graph below, it is of paramount importance to also understand the theory behind the DAX Language.

DAX Formulas and DAX Queries

As Ruth Pozuelo Martinez at Curbal, which is a great resource for learning DAX, describes it is it very easy to confuse DAX Formulas and DAX Queries when you begin to learn DAX. Especially if you search on Microsoft Documentation you will get up a lot of examples of DAX Queries.

This is an example of a DAX Formula:

This is an example of a DAX Query:

While you can write DAX Queries in external tools such as DAX Studio, you can not do it in Power BI. This article will therefore focus on DAX Formulas, while I will talk about DAX Queries in later posts when I discuss DAX Studio in detail.

DAX — Main building blocks

If we limit ourselves to Power BI, there are in short three different ways you can write DAX. I would rank them as below in complexity level:

• Calculated Columns
• Calculated Tables
• Measures

Calculated Columns

This is usually what most people coming from Excel will use when they begin to use DAX. Therefore I would rank this as the easiest way to write DAX. The syntax is relatively easy to understand. Below is “Total Sales a Calculated Column”.

The calculation for the column is as below:

First take the Order Quantity, multiply it with the unit price and include the discount as well. Looking at the table in the picture above, this calculation will be done in every single row, similar to what would be done in Excel.

I will discuss calculated columns much more in my third article of this series. But if you want to work with calculated columns, remember this.

Calculated columns takes up memory and are stored in your RAM and is calculated when you write the formula above. You should only write them when absolutely necessary, whenever is possible use measures instead, which I will describe further down.

Calculated Tables

This is probably something you will not use too much when working with DAX. One thing you can do with them is to created tables to use to failure proof your solutions, if you do not want to use DAX Studio. The example below creates a table where sales are only 2019 for example:

When you get to more advanced DAX one thing I use calculated tables for now is to see how functions that returns tables such as (FILTER, ALL, Summarize, Addcolumns) behave, but this could also be done in DAX Studio.

Measures

This should be your cornerstone in DAX. This is what can really transform your Power BI Report when you learn how to use them. Let us recreate the sales amount, but this time with a measure, it would look as below:

This looks much more complex than the calculated column with the “SUMX” and “Sales” included as well. I will go into detail about this in article three, but you can not use the same syntax as for Calculated Columns when writing measures in DAX, you will have to “Wrap it” in something.

One more really important thing is that measures are only calculated when you use them anywhere, and is therefore not taking up memory in your model as calculated columns, and should always be used when possible.

DAX — Advanced Concepts

There are a lot of advanced concepts such as Evaluation Context, Filter Context, Row Context and Context Transition in DAX, that works behinds the scenes without you even noticing them are there. I will not explain in detail in this blog post, but will post more advanced explanations about in later blog posts.

A lot of how DAX works also relates to how your model and relationships are built up. With a bad model you will not get any good result of your DAX formulas either. DAX is just one of the four pillars of Power BI Solutions which are Data Modelling, Power Query, DAX and Data visualizations.

I would really encourage people to try to read a lot of theory of DAX as soon as possible when learning this language, instead of just jumping in and creating measures. If you have read the theory, you will think about it everytime you create a measure and it will remember it better, and much quick go to the advanced DAX.

In a later blog post I will go through ways that I think is good starting points in your DAX journey, well-written blog posts, books, youtube channels and everything else that can be to your help.

Why should you learn DAX?

Now maybe you think:

“Why should I go through this hassle of learning DAX theory, will it really do me any good?”

If you are reading this blog post you probably already have some ideas on why you want to learn the language. You have maybe just began your Power BI journey, or are using Excel but want to do more advanced things.

Below are some reasons for why I think DAX is really important to learn.

1. You can evolve your Power BI Reports dramatically

If you learn DAX you can do so much more advanced things with your reports. Sam McKay at EnterpriseDNA said it very well:

If we’re not utilizing DAX well, we’re missing roughly 90% of the analytical potential of Power BI.

You need to really understand DAX well to be able to show all the detail and work with all the business logic that your customers want to have in their reports.

2. DAX is fun!

Even if it is cumbersome to take the next step in DAX, it is also a fun journey every time you learn new ways to work with DAX. The more you learn, the more fun will you have, and you will really appreciate just how much you can do with the language.

3. You get more dynamic and can understand performance issues

As you may know there are the possibility to create DAX quick measures and use the function “Show value as”.

The problem with using these functions are that you do not understand how to analyze if there are any performance issues with the measures. You also lose the possibility to change the measures slightly according to your need.

If you learn DAX you will have so much more possibility to change the measures slightly and also analyse the performance of these.

Conclusion

I hope that you now understand a little bit more about the DAX language.

• The sharp contrast between DAX and other programming languages.
• The learning curve and why you should learn DAX theory early
• Some high-level difference between Calculated columns and measures
• Why you should begin your DAX journey right now!

References

Curbal: #1 DAX Fridays! 101: The basics of the DAX Language

EnterpriseDNA: Why You Need To Learn DAX — A DAX Overview

sqlbi: 7 reasons DAX is not easy

Power BI Models available on GitHub

You can find the Power BI files for all of the Blog posts below:
https://github.com/EliasNo/DAX-Series

Inside PowerBI-Datamodels you can find the folder 1. What is DAX where I have posted the “Clean version” and the “Answer Key” with all the measures and similar done in this article.

Thank you for reading

Thank you very much for reading my blog!

Drop a comment below about what you thought about the content, or if you want to mention how you use DAX in your daily work.

I would be interested to hear more about how other people see these things.

If you enjoy reading my stories, and would like to support me as a writer, consider to sign up to become a Medium member using this link, and I can earn a small commission, with no extra cost for you.

Questions or inquiries

If you have any questions or input please contact me on

LinkedIn: www.linkedin.com/in/elias-nordlinder
Email: Elias.Nordlinder@gmail.com
Webpage: eliasnordlinder.com

Have a great week everyone
//Elias