How to Develop Complex DAX Expressions

Sooner or later or one other, any Energy BI developer should write complicated Dax expressions to analyze knowledge. However no person tells you learn how to do it. What’s the method for doing it? What’s one of the simplest ways to do it, and the way supportive can a growth course of be? These are the questions I’ll reply right here.

Introduction 

Generally my purchasers ask me how I got here up with the answer for a selected measure in DAX. My reply is at all times that I comply with a selected course of to discover a answer. 

Generally, the method will not be simple, and I have to deviate or begin from scratch once I  see that I’ve taken the unsuitable route. 

However the growth course of is at all times the identical: 

1. Perceive the necessities. 

2. Outline the maths to calculate the outcome. 

3. Perceive if the measure should work in any or one particular state of affairs.

4. Begin with middleman outcomes and work my method step-by-step till I totally perceive the way it ought to work and might ship the requested outcome. 

5. Calculate the ultimate outcome. 

The third step is essentially the most tough. 

Generally my shopper asks me to calculate a selected lead to a selected state of affairs. However after I ask once more, the reply is: Sure, I will even use it in different eventualities. 

For instance, a while in the past, a shopper requested me to create some measures for a selected state of affairs in a report. I needed to do it dwell throughout a workshop with the shopper’s workforce. 

Days after I delivered the requested outcomes, he requested me to create one other report primarily based on the identical semantic mannequin and logic we elaborated on through the workshop, however for a extra versatile state of affairs. 

The primary set of measures was designed to work tightly with the primary state of affairs, so I didn’t need to change them. Due to this fact, I created a brand new set of extra generic measures. 

Sure, it is a worst-case state of affairs, however it’s one thing that may occur. 

This was simply an instance of how essential it’s to take a while to completely perceive the wants and the doable future use instances for the requested measures. 

Step 1: The necessities 

For this piece, I take one measure from my earlier article to calculate the linear extrapolation of my buyer rely. 

The necessities are:

• Use the Buyer Depend Measure because the Foundation Measure. 
• The consumer can choose the yr to investigate. 
• The consumer can choose every other dimension in any Slicer. 
• The Person will analyze the outcome over time per 30 days. 
• The previous Buyer Depend must be taken because the enter values. 
• The YTD progress price have to be used as the premise for the outcome. 
• Primarily based on the YTD progress price, the Buyer Depend must be extrapolated to the top of  the yr. 
• The YTD Buyer Depend and the Extrapolation have to be proven on the identical Line-Chart.

The outcome ought to seem like this for the yr 2022: 

OK, let’s have a look at how I developed this measure.

However earlier than doing so, we should perceive what the filter context is. 

If you’re already conversant in it, you possibly can skip this part. Or you possibly can learn it anyway to make sure we’re on the similar degree. 

Interlude: The filter context 

The filter context is the central idea of DAX. 

When writing measures in a semantic mannequin, whether or not in Power Bi, a material semantic mannequin, or an evaluation providers semantic mannequin, you will need to at all times perceive the present filter context. 

The filter context is: 

The sum of all Filters which have an effect on the results of a DAX expression. 

Take a look at the next image:

Now, have a look at the next image: 

There are six filters, that have an effect on the filter context of the marked cells for the 2 measures “Sum Retail Gross sales” and “Avg Retail Gross sales”: 

• The Retailer “Contoso Paris Retailer” 
• The Metropolis “Paris” 
• The ClassName “Financial system” 
• The Month of April 2024 
• The Nation “France” 
• The Producer “Proseware Inc.” 

The primary three filters come from the visible. We will name them “Inside Filters”. They management how the Matrix-Visible can develop and what number of particulars we are able to see. 

The opposite filters are “Exterior Filters”, which come from the Slicers or the Filter Pane in Energy BI  and are managed by the consumer. 

The Energy of DAX Measures lies in the opportunity of extracting the worth of the Filter Context and the aptitude of manipulating the Filter context. 

We do that when writing DAX expressions: We manipulate the filter context.

Step 2: Middleman outcomes 

OK, now we’re good to go. 

First, I don’t begin with the Line-Visible, however with a Desk or a Matrix Visible. 

It’s because it’s simpler to see the outcome as a quantity than a line. 

Regardless that a linear development is seen solely as a line. 

Nevertheless, the middleman outcomes are higher readable in a Matrix. 

If you’re not conversant in working with Variables in DAX, I like to recommend reading this piece, the place  I clarify the ideas for Variables: 

The subsequent step is to outline the Base Measure. That is the Measure we need to use to calculate the supposed End result. 

As we need to calculate the YTD outcome, we are able to use a YTD Measure for the Buyer Depend: 

On-line Buyer Depend YTD =
VAR YTDDates = DATESYTD('Date'[Date])
RETURN
CALCULATE(
DISTINCTCOUNT('On-line Gross sales'[CustomerKey])
,YTDDates
)

Now we should think about what to do with these middleman outcomes. 

Because of this we should outline the arithmetic of the Measure. 

For every month, I have to calculate the final recognized Buyer Depend YTD. 

This implies, I at all times need to calculate 2,091 for every month. That is the final YTD Buyer  Depend for the yr 2022. 

Then, I need to divide this outcome by the final month with Gross sales, on this case 6, for June. Then multiply it by the present month quantity. 

Due to this fact, the primary middleman result’s to know when the final Sale was made. We should get the newest date within the On-line Gross sales desk for this. 

In line with the necessities, the Person can choose any yr to investigate, and the outcome have to be calculated month-to-month. 

Due to this fact, the right definition is: I have to first know the month when the final sale was made for the chosen yr. 

The Truth desk comprises a date and a Relationship to the Date desk, which incorporates the month quantity (Column: [Month]).

So, the primary variable can be one thing like this: 

Linear extrapolation Buyer Depend YTD pattern =
// Get the variety of months for the reason that begin of the yr
VAR LastMonthWithData = MAXX('On-line Gross sales'

,RELATED('Date'[Month])
)

RETURN
LastMonthWithData

That is the outcome: 

Maintain on: We should at all times get the final month with gross sales. As it’s now, we at all times get the identical month because the Month of the present row. 

It’s because every row has the Filter Context set to every month. 

Due to this fact, we should take away the Filter for the Month, whereas retaining the Yr. We will do that with ALLEXCEPT(): 

Linear extrapolation Buyer Depend YTD pattern =
// Get the variety of months for the reason that begin of the yr
VAR LastMonthWithData = CALCULATE(MAXX('On-line Gross sales'
,RELATED('Date'[Month])
)
,ALLEXCEPT('Date', 'Date'[Year])
)

RETURN
LastMonthWithData

Now, the outcome seems to be significantly better:

As we calculate the outcome for every month, we should know the month quantity of the present row (Month). We are going to reuse this because the issue for which we multiply the Common to get the linear extrapolation. 

The subsequent middleman result’s to get the Month quantity: 

Linear extrapolation Buyer Depend YTD pattern =
// Get the variety of months for the reason that begin of the yr
VAR LastMonthWithData = CALCULATE(MAXX('On-line Gross sales'
,RELATED('Date'[Month])
)
,ALLEXCEPT('Date', 'Date'[Year])
)
// Get the final month
// Is required if we're trying on the knowledge on the yr, semester, or
quarter degree
VAR MaxMonth = MAX('Date'[Month])
RETURN
MaxMonth

I can go away the primary Variable in place and solely use the MaxMonth variable after the return. The outcome reveals the month quantity per 30 days:

In line with the definition formulated earlier than, we should get the final Buyer Depend YTD for the newest month with Gross sales. 

I can do that with the next Expression: 

Linear extrapolation Buyer Depend YTD pattern =
// Get the variety of months for the reason that begin of the yr
VAR LastMonthWithData = CALCULATE(MAXX('On-line Gross sales'
,RELATED('Date'[Month])
)
,ALLEXCEPT('Date', 'Date'[Year])
)
// Get the final month
// Is required if we're trying on the knowledge on the yr, semester, or
quarter degree
VAR MaxMonth = MAX('Date'[Month])
// Get the Buyer Depend YTD
VAR LastCustomerCountYTD = CALCULATE([Online Customer Count YTD]
,ALLEXCEPT('Date', 'Date'[Year])
,'Date'[Month] = LastMonthWithData
)

RETURN
LastCustomerCountYTD

As anticipated, the outcome reveals 2,091 for every month:

You possibly can see why I begin with a desk or a Matrix when creating complicated Measures. 

Now, think about that one middleman result’s a date or a textual content. 

Exhibiting such a lead to a line visible is not going to be sensible. 

We’re able to calculate the ultimate outcome in accordance with the mathematical definition above. 

Step 3: The ultimate outcome 

We’ve got two methods to calculate the outcome: 

1. Write the expression after the RETURN assertion. 

2. Create a brand new Variable “End result” and use this Variable after the RETURN assertion. The ultimate Expression is that this: 

(LastCustomerCountYTD / LastMonthWithData) * MaxMonth

The primary Variant seems to be like this: 

Linear extrapolation Buyer Depend YTD pattern =
// Get the variety of months for the reason that begin of the yr
VAR LastMonthWithData = CALCULATE(MAXX('On-line Gross sales'
,RELATED('Date'[Month])

)

,ALLEXCEPT('Date', 'Date'[Year])

)
// Get the final month
// Is required if we're trying on the knowledge on the yr, semester, or
quarter degree
VAR MaxMonth = MAX('Date'[Month])
// Get the Buyer Depend YTD
VAR LastCustomerCountYTD = CALCULATE([Online Customer Count YTD]
,ALLEXCEPT('Date', 'Date'[Year])
,'Date'[Month] = LastMonthWithData
)

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

That is the second Variant: 

Linear extrapolation Buyer Depend YTD pattern =
// Get the variety of months for the reason that begin of the yr
VAR LastMonthWithData = CALCULATE(MAXX('On-line Gross sales'
,RELATED('Date'[Month])
)
,ALLEXCEPT('Date', 'Date'[Year])
)
// Get the final month
// Is required if we're trying on the knowledge on the yr, semester, or
quarter degree
VAR MaxMonth = MAX('Date'[Month])
// Get the Buyer Depend YTD
VAR LastCustomerCountYTD = CALCULATE([Online Customer Count YTD]
,ALLEXCEPT('Date', 'Date'[Year])
,'Date'[Month] = LastMonthWithData
)
// Calculating the extrapolation
VAR End result =
(LastCustomerCountYTD / LastMonthWithData) * MaxMonth
RETURN
End result

The outcome is similar. 

The second variant permits us to rapidly swap again to the Middleman outcomes if the ultimate outcome  is inaccurate with no need to set the expression after the RETURN assertion as a remark. 

It merely makes life simpler. 

Nevertheless it’s as much as you which ones variant you want extra. 

The result’s this:

When changing this desk to a Line Visible, we get the identical outcome as within the first determine. The final step can be to set the road as a Dashed line, to get the wanted visualization.

Complicated calculated columns 

The method is similar when writing complicated DAX expressions for calculated columns. The distinction is that we are able to see the outcome within the Desk View of Energy BI Desktop. 

Bear in mind that when calculated columns are calculated, the outcomes are bodily saved within the desk if you press Enter. 

The outcomes of Measures usually are not saved within the Mannequin. They’re calculated on the fly within the Visualizations. 

One other distinction is that we are able to leverage Context Transition to get our outcome after we want it to rely upon different rows within the desk. 

Learn this piece to be taught extra about this fascinating matter: