How to develop complex Dax expressions

At some point or another, any Power BI developer must write complex Dax expressions to analyze the data. But nobody tells you how to do it. What is the process to do it? What is the best way to do it and how can a development process be? These are the questions I will answer here.

Introduction

Sometimes my clients ask me how the solution occurred for a specific measure in Dax. My answer is always that I follow a specific process to find a solution.

Sometimes, the process is not simple, and I must divert or start from scratch when I see that I have taken the wrong address.

But the development process is always the same:

1. Understand the requirements.

2. Define mathematics to calculate the result.

3. Understand whether the measure must work in any specific scenario.

4. Start with the intermediary results and work step by step until I fully understand how it should work and I can deliver the requested result.

5. Calculate the final result.

The third step is the most difficult.

Sometimes my client asks me to calculate a specific result in a particular scenario. But after asking again, the answer is: yes, I will also use it in other scenarios.

For example, some time ago, a client asked me to create some measures for a specific scenario in a report. I had to do it live during a workshop with the client team.

Days after I delivered the requested results, he asked me to create another report based on the same semantic model and logic that we prepared during the workshop, but for a more flexible scenario.

The first set of measures was designed to work closely with the first scenario, so I did not want to change them. Therefore, I created a new set of more generic measures.

Yes, this is the worst case, but it is something that can happen.

This was just an example of how important it is to take some time to thoroughly understand the needs and possible cases of future use for the requested measures.

Step 1: The requirements

For this piece, I take a measure of my previous article to calculate the linear extrapolation of my customer count.

The requirements are:

• Use customer counting measure as a base measure.
• The user can select the year to analyze.
• The user can select any other dimension in any cutter.
• The user will analyze the result over time per month.
• The last count of customers should be taken as input values.
• YTD's growth rate should be used as the basis for the result.
• According to YTD's growth rate, customer count must be extrapolated until the end of the year.
• The YTD customer count and extrapolation should be shown in the same line chart.

The result should be seen for the year 2022:

Ok, let's see how I developed this measure.

But before doing so, we must understand what the context of the filter is.

If you are already familiar with it, you can omit this section. Or you can read it anyway to make sure we are at the same level.

Interlude: The context of the filter

The context of the filter is the central concept of Dax.

When writing measures in a semantic model, either in Power BI, a semantic model of fabric or a semantic model of analysis services, you must always understand the context of the current filter.

The context of the filter is:

The sum of all filters that affect the result of a Dax expression.

Look at the following image:

Now look at the following image:

There are six filters that affect the filter context of the cells marked for the two measures “Sumorist sales” and “AVG Retail Sales”:

• The “Contentoso Paris” store
• The city “Paris”
• The class name “Economy”
• The month of April 2024
• The country “France”
• The manufacturer “Proseware Inc.”

The first three filters come from the visual. We can call them “internal filters.” They control how the matrix visual can expand and how many details we can see.

The other filters are “external filters”, which come from the cutters or the filter panel in Power BI and are controlled by the user.

The power of Dax measures lies in the possibility of extracting the value of the filter context and the ability to manipulate the context of the filter.

We do this when writing expressions Dax: we manipulate the context of the filter.

Step 2: Intermediary results

Ok, now we are ready to go.

First, I do not start with the line visual, but with a table or a visual matrix.

This is because it is easier to see the result as a number than as a line.

Although a linear progression is visible only as a line.

However, intermediary results are better legible in a matrix.

If you are not familiar with working with variables in Dax, I recommend reading this piece, where I explain the concepts for variables:

The next step is to define the base measure. This is the measure we want to use to calculate the planned result.

As we want to calculate the YTD result, we can use a YTD measure for customer count:

Online Customer Count YTD =
VAR YTDDates = DATESYTD('Date'(Date))
RETURN
CALCULATE(
DISTINCTCOUNT('Online Sales'(CustomerKey))
,YTDDates
)

Now we must consider what to do with these intermediary results.

This means that we must define the arithmetic of the measure.

For each month, I must calculate the last known client count.

This means that I always want to calculate 2,091 for each month. This is YTD's last customer count for 2022.

Then, I want to divide this result into the last month with sales, in this case 6, for June. Then multiply it by the current month number.

Therefore, the first intermediary result is to know when the last sale was made. We must obtain the last date in the online sales table for this.

According to the requirements, the user can select any year to analyze, and the result must be calculated monthly.

Therefore, the correct definition is: I must first know the month in which the last sale for the selected year was made.

The fact table contains a date and a relationship with the date table, which includes the month number (column: (month)).

So, the first variable will be something like this:

Linear extrapolation Customer Count YTD trend =
// Get the number of months since the start of the year
VAR LastMonthWithData = MAXX('Online Sales'

,RELATED('Date'(Month))
)

RETURN
LastMonthWithData

This is the result:

Wait: we must always get the last month with sales. As it is now, we always have the same month as the month of the current row.

This is because each row has the context of the filter established each month.

Therefore, we must remove the filter for the month, while we keep the year. We can do this with ALLEXCEPT():

Linear extrapolation Customer Count YTD trend =
// Get the number of months since the start of the year
VAR LastMonthWithData = CALCULATE(MAXX('Online Sales'
,RELATED('Date'(Month))
)
,ALLEXCEPT('Date', 'Date'(Year))
)

RETURN
LastMonthWithData

Now, the result looks much better:

As we calculate the result for each month, we must know the month number of the current row (month). We will reuse this as the factor for which we multiply the average to obtain linear extrapolation.

The next intermediary result is to obtain the month number:

Linear extrapolation Customer Count YTD trend =
// Get the number of months since the start of the year
VAR LastMonthWithData = CALCULATE(MAXX('Online Sales'
,RELATED('Date'(Month))
)
,ALLEXCEPT('Date', 'Date'(Year))
)
// Get the last month
// Is needed if we are looking at the data at the year, semester, or
quarter level
VAR MaxMonth = MAX('Date'(Month))
RETURN
MaxMonth

I can leave the first variable in place and just use the Maxmonth variable after the return. The result shows the number of month per month:

According to the definition formulated above, we must obtain the last YTD customer count for the last month with sales.

I can do this with the following expression:

Linear extrapolation Customer Count YTD trend =
// Get the number of months since the start of the year
VAR LastMonthWithData = CALCULATE(MAXX('Online Sales'
,RELATED('Date'(Month))
)
,ALLEXCEPT('Date', 'Date'(Year))
)
// Get the last month
// Is needed if we are looking at the data at the year, semester, or
quarter level
VAR MaxMonth = MAX('Date'(Month))
// Get the Customer Count YTD
VAR LastCustomerCountYTD = CALCULATE((Online Customer Count YTD)
,ALLEXCEPT('Date', 'Date'(Year))
,'Date'(Month) = LastMonthWithData
)

RETURN
LastCustomerCountYTD

As expected, the result shows 2,091 for each month:

You can see why I start with a table or a matrix when developing complex measures.

Now, imagine that an intermediary result is a date or text.

Showing such a result in a visual line will not be practical.

We are ready to calculate the final result according to the previous mathematical definition.

Step 3: The final result

We have two ways to calculate the result:

1. Write the expression after RETURN statement.

2. Create a new “result” variable and use this variable after the return statement. The final expression is this:

(LastCustomerCountYTD / LastMonthWithData) * MaxMonth

The first variant looks like this:

Linear extrapolation Customer Count YTD trend =
// Get the number of months since the start of the year
VAR LastMonthWithData = CALCULATE(MAXX('Online Sales'
,RELATED('Date'(Month))

)

,ALLEXCEPT('Date', 'Date'(Year))

)
// Get the last month
// Is needed if we are looking at the data at the year, semester, or
quarter level
VAR MaxMonth = MAX('Date'(Month))
// Get the Customer Count YTD
VAR LastCustomerCountYTD = CALCULATE((Online Customer Count YTD)
,ALLEXCEPT('Date', 'Date'(Year))
,'Date'(Month) = LastMonthWithData
)

RETURN
// Calculating the extrapolation
(LastCustomerCountYTD / LastMonthWithData) * MaxMonth

This is the second variant:

Linear extrapolation Customer Count YTD trend =
// Get the number of months since the start of the year
VAR LastMonthWithData = CALCULATE(MAXX('Online Sales'
,RELATED('Date'(Month))
)
,ALLEXCEPT('Date', 'Date'(Year))
)
// Get the last month
// Is needed if we are looking at the data at the year, semester, or
quarter level
VAR MaxMonth = MAX('Date'(Month))
// Get the Customer Count YTD
VAR LastCustomerCountYTD = CALCULATE((Online Customer Count YTD)
,ALLEXCEPT('Date', 'Date'(Year))
,'Date'(Month) = LastMonthWithData
)
// Calculating the extrapolation
VAR Result =
(LastCustomerCountYTD / LastMonthWithData) * MaxMonth
RETURN
Result

The result is the same.

The second variant allows us to quickly return to intermediary results if the final result is incorrect without establishing the expression after the RETURN Declaration as a comment.

Simply facilitates life.

But it depends on you what variant you like the most.

The result is this:

By turning this table into a visual line, we obtain the same result as in the first figure. The last step will be to establish the line as a discontinuous line, to obtain the necessary visualization.

Complex calculated columns

The process is the same when writing Complex Dax expressions for calculated columns. The difference is that we can see the result in the Power Bi desktop table view.

Keep in mind that when the calculated columns are calculated, the results are physically stored in the table when pressing Enter.

The results of the measures are not stored in the model. They are calculated on the fly in the visualizations.

Another difference is that we can take advantage of the context transition to obtain our result when we need to depend on other rows in the table.

Read this article to get more information on this fascinating topic: