The best way to Develop Advanced 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 find out how 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 improvement 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 all the time that I observe a selected course of to discover a resolution. 

Typically, the method isn’t easy, and I need to deviate or begin from scratch once I  see that I’ve taken the unsuitable route. 

However the improvement course of is all the time 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 state of affairs.

4. Begin with middleman outcomes and work my manner step-by-step till I absolutely 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 troublesome. 

Typically 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 can 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 stay throughout a workshop with the shopper’s group. 

Days after I delivered the requested outcomes, he requested me to create one other report based mostly on the identical semantic mannequin and logic we elaborated on throughout 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. Subsequently, I created a brand new set of extra generic measures. 

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

This was simply an instance of how vital it’s to take a while to totally 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 depend. 

The necessities are:

• Use the Buyer Depend Measure because the Foundation Measure. 
• The consumer can choose the yr to research. 
• The consumer can choose another dimension in any Slicer. 
• The Consumer will analyze the end result over time monthly. 
• The previous Buyer Depend ought to be taken because the enter values. 
• The YTD progress charge have to be used as the idea for the end result. 
• Based mostly on the YTD progress charge, the Buyer Depend ought to be extrapolated to the tip of  the yr. 
• The YTD Buyer Depend and the Extrapolation have to be proven on the identical Line-Chart.

The end result ought to appear to be 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. 

In case you are already acquainted with it, you may skip this part. Or you may 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 Energy Bi, a material semantic mannequin, or an evaluation providers semantic mannequin, you need to all the time 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 are able to name them “Inner Filters”. They management how the Matrix-Visible can develop 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 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 is because it’s simpler to see the end result 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. 

In case you are not acquainted with working with Variables in DAX, I like to recommend studying this piece, the place  I clarify the ideas for Variables: 

The following step is to outline the Base Measure. That is the Measure we need to use to calculate the supposed Outcome. 

As we need to calculate the YTD end result, we will 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 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 Depend YTD. 

This implies, I all the time 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 end result by the final month with Gross sales, on this case 6, for June. Then multiply it by the present month quantity. 

Subsequently, 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 Consumer can choose any yr to research, and the end result have to be calculated month-to-month. 

Subsequently, the proper definition is: I need to first know the month when the final sale was made for the chosen yr. 

The Truth desk accommodates 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 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 end result: 

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

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

Subsequently, we should take away the Filter for the Month, whereas retaining the 12 months. We are able to 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 end result appears to be like a lot better:

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

The following 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 wanting on the knowledge on the yr, semester, or
quarter stage
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 end result exhibits the month quantity monthly:

Based on the definition formulated earlier than, we should get the final Buyer Depend YTD for the most recent 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 wanting on the knowledge on the yr, semester, or
quarter stage
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 end result exhibits 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. 

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

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

Step 3: The ultimate end result 

Now we have two methods to calculate the end result: 

1. Write the expression after the RETURN assertion. 

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

(LastCustomerCountYTD / LastMonthWithData) * MaxMonth

The primary Variant appears to be like 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 wanting on the knowledge on the yr, semester, or
quarter stage
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 wanting on the knowledge on the yr, semester, or
quarter stage
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 Outcome =
(LastCustomerCountYTD / LastMonthWithData) * MaxMonth
RETURN
Outcome

The end result is similar. 

The second variant permits us to shortly change again to the Middleman outcomes if the ultimate end result  is wrong without having 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 complicated 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 while you press Enter. 

The outcomes of Measures aren’t 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 after we want it to rely upon different rows within the desk. 

Learn this piece to study extra about this fascinating subject: 

Conclusion 

The event course of for complicated expressions all the time follows the identical steps: 

1. Perceive the necessities – Ask if one thing is unclear. 

2. Outline the mathematics for the outcomes. 

3. Begin with middleman outcomes and perceive the outcomes. 

4. Construct on the middleman outcomes one after the other – Don’t attempt to write multi function step.

5. Resolve the place to write down the expression for the ultimate end result. 

Following such a course of can prevent the day, as you don’t want to write down the whole lot in a single step. 

Furthermore, getting these middleman outcomes means that you can perceive what’s taking place and discover the Filter Context. 

This may make it easier to study DAX extra effectively and construct much more complicated stuff. 

However, remember: Regardless that a sure stage of complexity is required, a superb developer will maintain it so simple as doable, whereas sustaining the least quantity of complexity. 

References 

Right here is the article talked about originally of this piece, to calculate the linear interpolation.

Like in my earlier articles, I exploit the Contoso pattern dataset. You possibly can obtain the  ContosoRetailDW Dataset without cost from Microsoft right here.

The Contoso Information may be freely used beneath the MIT License, as described right here. I modified the dataset to shift the info to modern dates.