DAX Programming

On many occasions, it is commonly thought that a data analyst only deals with Excel formulas or charts and that programming languages are rarely touched upon. This notion is fueled by the emergence of many business intelligence solutions that allow you to perform comprehensive analysis straightforwardly on any dataset, without delving too deep into complex calculations. However, in tools such as Power BI, mastering the programming aspect, specifically the Data Analysis Expressions (DAX) language, is crucial for achieving the professional success you are seeking.

While I consider DAX a simple language to learn, it is considered one of the most challenging topics for those learning or delving into data analysis, particularly with Power BI.

In this chapter, you will learn the basics of DAX, explore the primary aggregation functions, create some measures and calculated columns using this language, and delve into slightly more complex measures while addressing topics...

Technical requirements

• Power BI Desktop

What is DAX?

According to Google, the data analysis process consists of six phases. In the first phase, we identify the problem, generating a series of questions that will likely be answered through our analysis. In the second phase, we prepare the data we will analyze, determining what we will measure, what factors we need to consider, and where the data is located. The third phase involves data processing, where we make the data useful. This is essentially the transformation phase in the ETL process. The fourth phase is the analysis phase, where we start creating all the measures and indicators that will help us answer the questions we posed in the first phase. The fifth phase is the sharing phase, where we create our reports by choosing the best visual object for each of the indicators we will present. Lastly, there is the acting phase. In this sixth phase, we provide recommendations based on the analysis we have conducted on our model.

DAX is used in the analysis phase. Its...

Basic aggregation functions

Although one of the goals of data warehousing is to bring the data in the most aggregated and organized form possible, it is necessary to have functions within the BI tool that allow you to aggregate the data in various ways. DAX has several aggregate functions, such as the SUM function that you saw a few sections ago. This function sums all the values in a column.

Similarly, the MAX, MIN, COUNT, and AVERAGE functions are used to obtain the maximum, minimum, count, and average values, respectively. Each of these functions has a different tolerance for certain types of data, and it is important to know their particularities because you will have to use at least one of them in your day-to-day work as a data analyst.

Using MAX and MIN

In the following measure, you can see one of the ways you can use the MAX or MIN function. In this case, two values are passed to the function to obtain the greater of the two, and in this example, the result would be...

Measures versus calculated columns

After showing you the basic aggregation functions in DAX, it’s important to know that this language is used for creating measures and calculated columns. Unlike Excel, where adding a column to a table is simply done by right-clicking and adding the column to the left or right, in Power BI, it’s a bit different. Here, we have to specify the expression that will define the column, and this expression must be written in DAX.

A calculated column is essentially an additional column that’s added to a table that you can use in the same way as other columns in your model. You can use aggregation functions on them and even use them to create relationships with other tables. The DAX expression you define for this column will be executed on the current row, row by row. This means you can’t directly access values from other rows like in Excel, unless you modify the filter context. The topic of filter contexts is something we’...

Understanding evaluation contexts

In the previous section, we used the SUMX function to sum the result of multiplying price by quantity. Similarly, we achieved the same result by creating a calculated column without the need to use the SUMX function.

Measures

The following code refers to a measure:

SUMX TEST = SUMX (
    Products,
    Products[Price] * Products[Qty]
    )

Calculated column

The following code refers to a calculated column:

Total = Products[Price] * Products[Qty]

The fact that both calculations return the same result is related to the execution environment, specifically the evaluation context in which they are executed.

A simple example to understand this concept is by adding the measure to a card in your report. As you can see, the result is the total sum of each multiplication of price by quantity. This is essentially the total sales as we have renamed the measure:

...

Filtering data and modifying contexts

Not all calculations will be as simple as a sum or an average. In many cases, you may need to perform calculations under specific conditions – for example, averaging the sales of products in a particular category or determining which customer had the most orders above a certain amount. To achieve this, we need to use a fundamental function that allows us to modify the execution context of our measures: the CALCULATE function.

CALCULATE

In the previous section, I showed you what the filter context and row context are. We mentioned that the context conditions the result of a measure. However, sometimes, it is necessary to bypass the rules or modify the context to obtain the desired result. There is a privileged function within DAX that allows us to modify the filter context of an expression, and that function is CALCULATE.

The CALCULATE function is the only function allowed in DAX for modifying the filter context or adding a new one...

Time intelligence functions

Time intelligence functions are a crucial topic on the path to becoming an experienced data analyst. These functions allow you to perform calculations based on time dimensions such as dates, hours, months, or years. They enable you to make comparisons, identify trends, and conduct historical analysis more effectively.

In this section, I will show you some of DAX’s most commonly used time intelligence functions. Before introducing the first function, it’s important to know that time intelligence functions have a few minimum requirements to function correctly. These requirements are as follows:

• There must be a calendar table in your model
• All dates must be present for the period you are looking for
• The calendar table should always start on January 1 and end on December 31, including each day within this period
• The calendar table must contain a column of type DateTime or Date
• The calendar table must be marked as the...

Complex DAX measures

Creating measures in DAX can sometimes be complex if you don’t have a good understanding of certain functions or concepts. It can also be frustrating when you’re unsure how to implement a specific requirement within your report. However, some tricks can be very helpful in finding a solution for the measure you’re working on.

Complex model = complex measures

In previous sections, I mentioned that your data model will influence the complexity of your measures and, consequently, their results. For example, the following data model includes a calendar table and a table with sales records, specifically internet service sales. As you can see, three relationships are established between these tables, although Power BI only allows one of them to be active at a time. On the calendar table side, we always have the DateKey column, which is involved in the relationships, while on the sales table side, we have the DueDateKey, OrderDateKey, and ShipDateKey...

Q&A

During the interview process for the position of data analyst or Power BI Engineer, as many companies often refer to it, the topic of DAX is often touched upon in practical scenarios and problem-solving. However, you may encounter some technical questions that tend to be somewhat complex, so you should be prepared for that moment.

Below, I present some of the questions that may arise during the interview, some of which I have personally asked while interviewing candidates, while others have been asked to me in some interview processes.

Question 1: What is the advantage of the DIVIDE function compared to the regular expression Expression A / Expression B?

Answer: Using the DIVIDE function has several advantages. One of them is preventing the display of invalid values in the report. Additionally, it avoids division by zero in cases where the denominator is an expression that could return zero.

Question 2: What is the difference between a measure and a calculated column...

Summary

In this chapter, you learned about DAX concepts that are essential for mastering any job interview. First, I started by mentioning some points to take into account so that the learning process of this language is as fluid as possible. Then, we saw some of the basic aggregation functions such as MAX, MIN, and SUM, as well as some very practical examples of these functions. In the next section, we started by discussing the differences between measure and calculated columns and when you should use them.

Next, you saw a brief definition of what evaluation contexts are and the role played by the filter context and the row context inside measures. You also understood the importance of these to master the creation of complex measures using DAX. In the final stretch, you also saw some time intelligence functions, as well as some very useful examples that taught you how to go back and forth in time with these functions.

Finally, I showed you some advanced concepts about DAX and...