The 4 Step Thinking Process Behind Building A DAX Measure

A guide to convert business problems into elegant DAX solutions

Mar 17, 2024

I got asked a question last week.

How do you know where to start building a measure?

I thought it was a great question that doesn’t get discussed a lot, so I decided to outline my personal thinking process in this article:

Step #1: Define business problem and dream outcome
Step #2: Brainstorm what you need to know to solve the problem
Step #3: Think in tables and start from inside out
Step #4: Assemble the building blocks on plate

Before jumping into the steps in more detail, you should realize DAX is a lot like a ladder 🪜. To hold a ladder together you need to have two rails and the individual steps.

You need to know how DAX technically works (= left rail), you need to know the thinking process of creating a measure (= right rail), and you need to know the functions to be able to climb the ladder (= individual steps).

If you’re missing any one of the above the ladder will fall apart.

This article will focus on the structured thinking process, but if your problem is instead knowledge about functions or what’s happening under the hood, take a look at my previous posts:

Step #1: Define business problem and dream outcome

Obviously, to build a solution, you need to have a problem.

I will use the same real life example that was the inspiration for this article: Which products have seen the biggest decrease in delivered weight 🏋️ during previous 5 years?

This problem turns out to be deceivingly tricky (especially with thousands of products) because the decrease in volume might have happened last year, four years ago, or anything in between.

The first attempt to solve the problem looked like this:

This is nice, but it fails to meet the dream outcome.

The user needs to be able to easily sort the products based on highest impact using a single column.

After twisting and turning the table for a while you come to the conclusion it’s a dead end. You need a custom measure to solve this problem…

Step #2: Brainstorm what you need to know to solve the problem

Now, stop and think about this problem for a second.

Use common sense 🤔. What do you need to know to calculate the impact? Turns out, you only need to know two things:

What is the current (previous 12 months) delivered weight
What was the delivered weight on the best year ever

If you simply compare these two numbers for each customer & product combination, you can see how big the change in delivered weight has been compared to best year.

Now you know the building blocks of your calculation.

Step #3: Think in tables and start from inside out

Now it’s time to create the actual measure(s).

For context, the data model behind this example is really simple with just four tables: Customer, Product, Date, and Orders.

I have two rules when it comes to actually writing a measure:

Think in tables — Since everything in DAX is a table, think how you can convert your problem into a table.
Build the measure in layers from inside out (think of an onion🧅) — It would be really hard to write a formula starting from the top level function.

This next example will illustrate both of these rules in action.

Measure #1: Delivered Weight On Best Year

To tackle this, we want to create a table that consist of two columns: year and delivered weight and pick the row with maximum delivered weight.

The first step is to create a one column table with unique years. This first layer is easily created using VALUES.

VALUES ( 'Date'[Year] )

Next, we need to add the already existing [Delivered Weight] measure as the second column to our table. ADDCOLUMNS is here to help us do that.

ADDCOLUMNS ( 
    VALUES ( 'Date'[Year] ),
    "@Weight", [Delivered Weight]
)

To find out what is the maximum value in the [@Weight] column, we will use the MAXX function.

MAXX (
    ADDCOLUMNS ( 
        VALUES ( 'Date'[Year] ), 
        "@Weight", [Invoiced Weight] 
    ),
    [@Weight]
)

Finally, to make sure possible time filtering done by the report user does not affect the calculation result, we remove any filters from Date table using CALCULATE and REMOVEFILTERS.

Delivered Weight On Best Year = 
CALCULATE (    
    MAXX (
        ADDCOLUMNS ( 
            VALUES ( 'Date'[Year] ), 
            "@Weight", [Delivered Weight] 
        ),
        [@Weight]
    ),
    REMOVEFILTERS ( 'Date' )
)

Know someone that might like this article?

Measure #2: Delivered Weight Previous 12 Months

Time to tackle next building block.

For this, we first need to define from which point in time do we calculate the previous 12 months. In this example we decided to use the last delivery date as the anchor ⚓️. I will store that value in a variable.

VAR _LastDeliveryDate =
    CALCULATE (
        MAX ( 'Orders'[DeliveryDate] ),
        REMOVEFILTERS ( 'Orders' )
    )

Since I’m lazy, I’m just going to copy the previous 12 months time intelligence pattern from my measure documentation file (discussed in previous article), and reuse the already existing [Delivered Weight] measure.

Delivered Weight Previous 12 Months = 
VAR _LastDeliveryDate =
    CALCULATE (
        MAX ( 'Orders'[DeliveryDate] ),
        REMOVEFILTERS ( 'Orders' )
    )

VAR _Prev12MonthWeight =
    CALCULATE (
        [Delivered Weight],
        REMOVEFILTERS ( 'Date' ),
        DATESINPERIOD ( 'Date'[Date], _LastDeliveryDate, -12, MONTH )
    )
RETURN
    _Prev12MonthWeight

That’s it. We’re done with creating the measures.

Step #4: Assemble the building blocks on plate

Does the chef 🧑‍🍳 just throw the ingredients on a plate in a fancy restaurant?

Of course not, and you should neither. The final step is the assemble our solution into an easy to understand visualization before we serve it to the end user.

Since we have already created [Delivered Weight Previous 12 Months] and [Delivered Weight On Best Year] measures, we can simply reuse them to compute the difference. Alternative approach would be to compute everything inside one measure.

Delivered Weight Change Compared To Best Year =
[Delivered Weight Previous 12 Months] - [Delivered Weight On Best Year]

The only thing left at this point is to sort the table using the new measure, and add little finishing touch using conditional formatting with data bars, and voilà, our work here is done! ✅ This is how it looks:

Final solution with perfectly sorted table

Now, you could of course further enhance this report but the point of this article was only to demonstrate the thinking process behind the building a measure.

Let me know in the comments what is your process in creating DAX measures?

Power BI Insider

Discussion about this post