The way to Develop Complicated DAX Expressions

In some unspecified time in the future 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 

Generally my shoppers 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. 

Generally, the method will not be easy, and I have to deviate or begin from scratch after I  see that I’ve taken the flawed path. 

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

5. Calculate the ultimate outcome. 

The third step is essentially the most troublesome. 

Generally my shopper asks me to calculate a selected end in a selected situation. However after I ask once more, the reply is: Sure, I may also use it in different eventualities. 

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

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 in the course of 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 need to change them. Due to this fact, I created a brand new set of extra generic measures. 

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

This was simply an instance of how vital 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 person can choose the yr to research. 
• The person can choose every other dimension in any Slicer. 
• The Consumer will analyze the outcome over time monthly. 
• The previous Buyer Rely ought to be taken because the enter values. 
• The YTD progress price should be used as the premise for the outcome. 
• Based mostly on the YTD progress price, the Buyer Rely ought to be extrapolated to the tip of  the yr. 
• The YTD Buyer Rely and the Extrapolation should be proven on the identical Line-Chart.

The outcome ought to appear like 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 acquainted with it, you possibly can skip this part. Or you possibly can learn it anyway to make sure we’re on the identical 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 cloth semantic mannequin, or an evaluation providers semantic mannequin, you will 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, 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 “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 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 person. 

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. 

Though a linear development is seen solely as a line. 

Nonetheless, 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 subsequent step is to outline the Base Measure. That is the Measure we need to use to calculate the meant Consequence. 

As we need to calculate the YTD outcome, 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 that we should outline the arithmetic of the Measure. 

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

This implies, I all the time need to calculate 2,091 for every month. That is the final YTD Buyer  Rely 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 Consumer can choose any yr to research, and the outcome should 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 incorporates 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 Rely YTD pattern =
// Get the variety of months because the begin of the yr
VAR LastMonthWithData = MAXX('On-line Gross sales'

,RELATED('Date'[Month])
)

RETURN
LastMonthWithData

That is the outcome: 

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’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 are able to do that with ALLEXCEPT(): 

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

RETURN
LastMonthWithData

Now, the outcome appears a lot 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 Rely YTD pattern =
// Get the variety of months because the 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 outcome reveals the month quantity monthly:

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

I can do that with the next Expression: 

Linear extrapolation Buyer Rely YTD pattern =
// Get the variety of months because the 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 Rely 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’ll be able to see why I begin with a desk or a Matrix when growing complicated Measures. 

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

Displaying such a end in a line visible won’t be sensible. 

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

Step 3: The ultimate outcome 

We have now two methods to calculate the outcome: 

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 appears like this: 

Linear extrapolation Buyer Rely YTD pattern =
// Get the variety of months because the 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 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 because the 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 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 outcome is identical. 

The second variant permits us to shortly change 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 identical when writing complicated DAX expressions for calculated columns. The distinction is that we will see the outcome within the Desk View of Energy BI Desktop. 

Remember that when calculated columns are calculated, the outcomes are bodily saved within the desk once 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 will leverage Context Transition to get our outcome once we want it to rely upon different rows within the desk. 

Learn this piece to study extra about this fascinating matter: