Create model calculations by using DAX – Model the data

2022-09-03
No Comments

Skill 2.3: Create model calculations by using DAX

You used some DAX earlier in the chapter to create calculated tables and calculated columns as well as configure row-level security. In practice, DAX is most often used to create measures in Power BI.

Writing your own formulas is an important skill that allows you to perform much more sophisticated analysis based on your data compared to not using DAX.

In this section, we start by reviewing DAX fundamentals; then we look at CALCULATE, one of the most important functions in DAX, specifically in Time Intelligence or time-related calculations, which we review separately.

DAX can help you replace some columns with measures, allowing you to reduce the data model size. Not all DAX formulas need to be complex, and we review some basic statistical functions in this section as well.

This skill covers how to:

Note Companion File

The completed examples from this section are available in the 2.3 Create.pbix file in the companion files folder.

Create basic measures by using DAX

Although many things can be computed by using calculated columns, in most cases it’s preferable to write measures, because they don’t increase the model size. Additionally, some calculations are simply not possible with calculated columns. For example, to calculate a ratio dynamically, you need to write a measure.

As you saw earlier, quick measures already allow you to perform basic calculations without writing DAX yourself. In this section, you start using DAX to build complex measures.

It’s important to understand that Power BI allows you to aggregate columns in visuals without using measures, a practice sometimes called implicit measures. These can be useful when you want to quickly test how a visual might look or to perform a quick analysis on a column. However, it’s always best practice to create explicit measures by using DAX—even with trivial calculations such as SUM. Here are some reasons it’s preferable to create measures yourself:

  • Implicit measures may provide unexpected results in some cases due to the Summarize by column property. For example, if you have a column that contains product prices and Power BI sets the summarization to SUM, then dragging the column in a visual will not produce meaningful results. Although you can change the summarization in the visual, following this approach means that you need to pay attention to this property every time you use implicit measures.
  • Explicit measures can be reused in other measures. This is beneficial because you can write less code, which saves time and improves the maintainability of your data model.
  • Implicit measures cannot leverage inactive relationships.
  • Implicit measures are not supported by calculation groups.

Need More Review? Calculation Groups

Calculation groups are outside the scope of the exam, but they can be extremely useful in practice. For more information, see “Calculation groups” at https://docs.microsoft.com/en-us/analysis-services/tabular-models/calculation-groups.

Note Learning DAX

Teaching DAX is not the purpose of this book. If you want to learn DAX, The Definitive Guide to DAX by Marco Russo and Alberto Ferrari (Pearson, 2019) is a great explanation of DAX and its use.

Measures are different from calculated columns in a few ways. The main difference is that you can see the results of a calculated column immediately after defining the calculation, whereas you can’t see the results of a measure until you use it in a visual. This behavior allows measures to return different results depending on filters and where they’re used.

Another difference between calculated columns and measures is that calculated column formulas apply to each row of a table, whereas measures work on columns and tables, not specific rows. Therefore, measures most often use aggregation functions in DAX.

There are a few ways to create a measure in Power BI Desktop. Here’s one way:

Go to the Report view.

In the Fields pane, right-click a table in which you want to create a new measure.

Select New measure.

Enter the measure formula and press Enter.

You can also create a measure by selecting New measure on the Home ribbon, but you have to make sure you’ve got the right table selected in the Fields pane; otherwise, your measure may not be created in the correct table. If you do create a measure in the wrong table, instead of re-creating the measure you can move it by performing the following steps:

Go to the Report view.

In the Fields pane, select the measure you want to move.

On the Measure tools ribbon, select the table your measure should be stored in from the Home table dropdown list.

For example, to compute the total profit of Wide World Importers, use the following measure formula:

Click here to view code image

Total Profit = SUM(Sale[Profit])

You can compute total sales, excluding tax, by using the following measure formula:

Click here to view code image

Total Sales Excluding Tax = SUM(Sale[Total Excluding Tax])

If you want to compute the profit margin percentage, there are two ways of doing it. You could use this:

Click here to view code image

Profit % =
DIVIDE(
    SUM(Sale[Profit]),
    SUM(Sale[Total Excluding Tax])
)

Note Using DIVIDE

We’re using DIVIDE in the formula to avoid division by 0. DIVIDE has an optional third ­parameter, which is the value to return in case you divide by 0.

However, this approach involves repeating your own code, which is undesirable because formulas become more difficult to maintain. You can avoid this issue if you reference the measures you created previously:

Click here to view code image

Profit % =
DIVIDE(
    [Total Profit],
    [Total Sales Excluding Tax]
)

Note Formatting Measures

Even though the Profit % measure has a percentage sign in its name, Power BI will format the measure as a decimal number by default. You can change the measure format on the Measure tools ribbon in the Formatting group. Formatting a measure after it’s been created is a great habit to learn.

When you’re referencing measures, it’s best practice to not use table names in front of them. Unlike column names, measure names are unique; different tables may have the same column names, but it’s not possible to have measures that share the same name.

Another feature of DAX that allows you to avoid repeating yourself is variables. Think of a variable as a calculation within a measure. For instance, if you want to avoid showing zeros in your visuals, you could write a measure as follows:

Click here to view code image

Total Dry Items Units Sold =
IF(
    SUM(Sale[Total Dry Items]) <> 0,
    SUM(Sale[Total Dry Items])
)

By using a variable, you can avoid calling SUM twice:

Click here to view code image

Total Dry Items Units Sold =
VAR TotalDryItems = SUM(Sale[Total Dry Items])
VAR Result =
    IF(
        TotalDryItems <> 0,
        TotalDryItems
    )
RETURN
    Result

Variables are especially useful when you want to store computationally expensive values, because variables are evaluated no more than once. As you’ll see later in this chapter, you can use many variables within the same formula.