When DAX Optimizer analyzes a Tabular model, it does not consider the impact of the calculation groups. Indeed, the first version of DAX Optimizer does not analyze the impact of calculation groups. We want to support such analysis in the future, but one of the problems to solve is how to evaluate the possible application of each calculation item.

This article explains how to evaluate the performance of calculation groups by creating corresponding measures that produce an equivalent query plan.

Evaluating a calculation item cost

For example, suppose a model has 20 measures and two calculation groups, each one with five calculation items. In that case, we have 720 possible combinations: every measure can be evaluated with any combination of the existing calculation items, plus an additional “calculation group non-selected” combination that produces the formula 20x6x6.

Assuming such an analysis would take place (what to do if the combinations are too many?), the next challenge is evaluating the results correctly. Should we provide the same weight to each combination? Or should we prioritize certain combinations based on other elements?

An alternative approach could be analyzing each calculation item in an isolated way. Still, because of the different metrics produced, analyzing calculation groups separately from measures would be better. As you can imagine, this is an area where we are still investigating which approach can provide helpful results.

In the meantime, if you want to evaluate the cost of a calculation item, you can create a measure that implements the same code of an existing calculation item. For example, consider the following calculation item:

PY =
CALCULATE (
    SELECTEDMEASURE (),
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

You can create a measure with the same code and replace the SELECTEDMEASURE function with a measure reference that invokes one of the measures of the model – possibly a significative one used with the calculation item:

Test PY = 
CALCULATE (
    [Sales Amount],
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)

You could also create multiple measures for the same calculation item, one for each measure you want to test with the same type of calculation. By comparing the memory and CPU cost of the new measure for the calculation item with the corresponding costs of the original measure, you can get an idea about the additional cost introduced by the calculation item. Unfortunately, the relevance score might not be so helpful because the new measure is undoubtedly not referenced by any other measure.

Calculation items in existing measures

Unfortunately, if a measure in the model applies a calculation item to other measures, the calculation item cost is ignored. For example, consider the following measure:

Sales YTD =
CALCULATE (
    [Sales Amount],
    'Calc group'[Calc item] = "YTD"
)

The cost applied by the YTD calculation item is ignored, so you will see that Sales YTD has the exact cost as Sales Amount. We know this is not ideal, which is another area where DAX Optimizer should improve in future versions.

Conclusions

Calculation groups require particular attention when you optimize a Tabular model with DAX Optimizer. You can isolate the code of the calculation items you want to analyze and copy the code in specific measures that the service can analyze. You can find significant bottlenecks in the code of a calculation item by using this technique, even though you should consider the issues detected as potentially more dangerous because they are reported in the test measures and could be combined with other measures in many different combinations.

We will work to improve the support for calculation group optimization in future versions of DAX Optimizer. Please provide us feedback about your use cases so we can better prioritize how to face the challenges described in this article.