How to Develop Complex DAX Expressions

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

Introduction 

Typically 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 observe a selected course of to discover a answer. 

Typically, the method will not be simple, and I need to deviate or begin from scratch once I  see that I’ve taken the unsuitable path. 

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

1. Perceive the necessities. 

2. Outline the mathematics to calculate the end result. 

3. Perceive if the measure should work in any or one particular situation.

4. Begin with middleman outcomes and work my approach step-by-step till I totally perceive the way it ought to work and may ship the requested end result. 

5. Calculate the ultimate end result. 

The third step is essentially the most tough. 

Typically my shopper asks me to calculate a selected lead to a specific situation. However after I ask once more, the reply is: Sure, I may even use it in different situations. 

For instance, a while in the past, a shopper requested me to create some measures for a selected situation in a report. I needed to do it reside throughout a workshop with the shopper’s crew. 

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 throughout the workshop, however for a extra versatile situation. 

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

Sure, it is a worst-case situation, 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 circumstances 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 Rely Measure because the Foundation Measure. 
• The consumer can choose the yr to research. 
• The consumer can choose every other dimension in any Slicer. 
• The Person will analyze the end result over time per thirty days. 
• The previous Buyer Rely must be taken because the enter values. 
• The YTD progress fee should be used as the premise for the end result. 
• Based mostly on the YTD progress fee, the Buyer Rely must be extrapolated to the top of  the yr. 
• The YTD Buyer Rely and the Extrapolation should be proven on the identical Line-Chart.

The end result ought to appear to be this for the yr 2022: 

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

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

In case you are already aware of it, you possibly can skip this part. Or you possibly can learn it anyway to make sure we’re on the similar stage. 

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 cloth semantic mannequin, or an evaluation providers semantic mannequin, you 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. 

Have a look at the next image:

Now, take 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 “Economic 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 broaden and what number of particulars we will 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 potential for extracting the worth of the Filter Context and the potential 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 is because it’s simpler to see the end result as a quantity than a line. 

Although a linear development is seen solely as a line. 

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

In case you are not aware of 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 wish to use to calculate the meant Consequence. 

As we wish to calculate the YTD end result, we will use a YTD Measure for the Buyer Rely: 

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

Now we should take into account what to do with these middleman outcomes. 

Which means we should outline the arithmetic of the Measure. 

For every month, I need to calculate the final identified Buyer Rely YTD. 

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

Then, I wish to divide this end result 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. 

Based on the necessities, the Person can choose any yr to research, and the end result should be calculated month-to-month. 

Due to this fact, the right definition is: I need 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 will likely be one thing like this: 

Linear extrapolation Buyer Rely 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 end result: 

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 is 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 12 months. We will do that with ALLEXCEPT(): 

Linear extrapolation Buyer Rely 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 end result seems significantly better:

As we calculate the end result 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 Rely 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 wanting on the information on the yr, semester, or
quarter stage
VAR MaxMonth = MAX('Date'[Month])
RETURN
MaxMonth

I can depart the primary Variable in place and solely use the MaxMonth variable after the return. The end result reveals the month quantity per thirty days:

Based on the definition formulated earlier than, we should get the final Buyer Rely YTD for the newest month with Gross sales. 

I can do that with the next Expression: 

Linear extrapolation Buyer Rely 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 wanting on the information on the yr, semester, or
quarter stage
VAR MaxMonth = MAX('Date'[Month])
// Get the Buyer Rely YTD
VAR LastCustomerCountYTD = CALCULATE([Online Customer Count YTD]
,ALLEXCEPT('Date', 'Date'[Year])
,'Date'[Month] = LastMonthWithData
)

RETURN
LastCustomerCountYTD

As anticipated, the end result reveals 2,091 for every month:

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

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

Exhibiting such a lead to a line visible won’t be sensible. 

We’re able to calculate the ultimate end result in keeping with the mathematical definition above. 

Step 3: The ultimate end result 

We now have two methods to calculate the end result: 

1. Write the expression after the RETURN assertion. 

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

(LastCustomerCountYTD / LastMonthWithData) * MaxMonth

The primary Variant seems like this: 

Linear extrapolation Buyer Rely 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 wanting on the information on the yr, semester, or
quarter stage
VAR MaxMonth = MAX('Date'[Month])
// Get the Buyer Rely 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 Rely 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 wanting on the information on the yr, semester, or
quarter stage
VAR MaxMonth = MAX('Date'[Month])
// Get the Buyer Rely YTD
VAR LastCustomerCountYTD = CALCULATE([Online Customer Count YTD]
,ALLEXCEPT('Date', 'Date'[Year])
,'Date'[Month] = LastMonthWithData
)
// Calculating the extrapolation
VAR Consequence =
(LastCustomerCountYTD / LastMonthWithData) * MaxMonth
RETURN
Consequence

The end result is similar. 

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

It merely makes life simpler. 

But it surely’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 end result as within the first determine. The final step will likely be to set the road as a Dashed line, to get the wanted visualization.

Advanced calculated columns 

The method is similar when writing advanced DAX expressions for calculated columns. The distinction is that we will see the end result 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 once you press Enter. 

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

One other distinction is that we will leverage Context Transition to get our end result once we want it to depend upon different rows within the desk. 

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