Replace implicit measures with explicit measures – Model the data

2021-12-03
No Comments

Replace implicit measures with explicit measures

It is sometimes possible to replace some numeric columns with measures, which can reduce the size of the data model. In our Wide World Importers example, there are several columns that could be replaced with measures.

For example, the Total Chiller Items and Total Dry Items columns in the Sale table show quantity of chiller and dry items, respectively. Essentially, these columns show filtered quantities depending on whether an item is a chiller or a dry item.

Before you replace the two columns with measures, create the following measure, which you’ll reference and build upon later:

Click here to view code image Total Quantity = SUM(Sale[Quantity])

You can now create the following two measures and use them instead of columns:

Click here to view code image

Total Chiller Items (Measure)
 =
CALCULATE(
    [Total Quantity],
    ‘Stock Item'[Is Chiller Stock] = TRUE
)
Total Dry Items (Measure)
 =
CALCULATE(
    [Total Quantity],
    ‘Stock Item'[Is Chiller Stock] = FALSE
)

If you remove the Total Chiller Items and Total Dry Items columns from the model, you’ll make it smaller and more efficient.

Another example of a column that can be replaced by a measure is Total Including Tax from the Sale table. Since Total Excluding Tax and Tax Amount added together equals Total Including Tax, you can use the following measure instead:

Click here to view code image

Total Including Tax (Measure) =
SUMX(
    Sale,
    Sale[Total Excluding Tax] + Sale[Tax Amount]
)

Again, once you have the measure, removing the Total Including Tax column would reduce the size of the data model.

Use basic statistical functions

As mentioned previously, it’s best practice to create explicit measures even for basic calculations such as SUM, because you can build upon them to create more complex measures. You’ve already used SUM in our previous examples; here are several other basic statistical measures that are frequently used:

  • AVERAGE
  • MEDIAN
  • COUNT
  • DISTINCTCOUNT
  • MIN
  • MAX

All these functions take a column as a reference and produce a scalar value. In addition, every function except DISTINCTCOUNT has an equivalent iterator function with the X suffix—for instance, SUMX is the iterator counterpart of SUM. Iterators take two parameters: a table to iterate through, and an expression to evaluate for each row. The evaluated results are then aggregated according to the base function; for example, SUMX will sum the results. When you’re learning the difference, it can be helpful to create sample tables similar to the examples shown earlier to visually compare the nuances of the different functions.

Need More Review? Statistical Functions In DAX

There are over 60 statistical functions in DAX, and describing each one is out of the scope of this book. For an overview, see “Statistical functions” at https://docs.microsoft.com/en-us/dax/statistical-functions-dax.