Comparing Explicit Measures to Calculation Groups in Tabular Models

在表格模型中比较显式度量值与计算组

With the advent of UDFs and their combination with calculation groups, I see a lot of discussion about not creating explicit measures but instead offering calculation groups to report creators. 随着用户定义函数（UDF）的出现及其与计算组的结合，我看到许多关于“不再创建显式度量值，而是向报表创建者提供计算组”的讨论。

Introduction

引言

With the advent of UDFs, we should rethink how to use calculation groups. Both are essential features when simplifying a semantic model by modularizing logic and reducing business logic duplication. While UDFs are very useful for standardizing business logic and having it only once per data model, calculation groups are useful for report designers to apply business logic to measures. 随着 UDF 的出现，我们应该重新思考如何使用计算组。在通过模块化逻辑和减少业务逻辑重复来简化语义模型时，这两者都是必不可少的功能。虽然 UDF 在标准化业务逻辑并确保每个数据模型仅保留一份逻辑方面非常有用，但计算组对于报表设计者将业务逻辑应用于度量值则更为有效。

Calculation Groups are visible to Report Designers, but UDFs are usable only within DAX expressions and are not usable on the Front-End. You can find resources on combining UDFs and calculation groups in the References section below. The question is whether we should add explicit measures or only offer calculation groups to the users. 计算组对报表设计者可见，而 UDF 仅能在 DAX 表达式中使用，无法在前端直接调用。你可以在下方的参考部分找到关于结合使用 UDF 和计算组的资源。问题在于，我们应该添加显式度量值，还是仅向用户提供计算组？

Here is an example: I need to offer the possibility to calculate the previous year’s value. I can offer a calculation item that the user can use to obtain the desired result. I can add an explicit measure for the PY result. The question is, which one offers more flexibility and is easier to use? This is the question that I will try to answer here. 举个例子：我需要提供计算去年（PY）数值的功能。我可以提供一个计算项供用户获取所需结果，也可以添加一个用于 PY 结果的显式度量值。问题是，哪种方式更灵活、更易于使用？这就是我在此试图回答的问题。

The user’s view

用户视角

First, who is the user? There are two groups of users: 首先，用户是谁？主要有两类用户：

• Report designers who use our semantic models and need to understand the semantic model easily.
• 报表设计者：他们使用我们的语义模型，并需要轻松理解该模型。
• Report consumers must understand what we show in the visualizations without much room for interpretation.
• 报表消费者：他们必须能够直接理解我们在可视化图表中展示的内容，而无需过多的解读。

In the end, we must support both user groups when we build a semantic model. In the conclusion section below, you will find my primary guideline when designing a semantic model. But first, let’s see the effects of the two approaches for our users. 最终，我们在构建语义模型时必须同时支持这两类用户。在下方的结论部分，你将找到我在设计语义模型时的主要准则。但首先，让我们看看这两种方法对用户的影响。

Using Matrix Visualizations

使用矩阵可视化

First, I built a Matrix. The Matrix should contain the Calendar hierarchy as Rows and the Measures for Online Sales, PY, and PM on the columns. In addition, I want to slice the results by Brand. First, I did it with Calculation Items: 首先，我构建了一个矩阵。矩阵的行包含日历层级，列包含在线销售额、PY（去年）和 PM（上月）的度量值。此外，我想按品牌对结果进行切片。首先，我使用计算项实现了这一点：

(Figure 1 – Matrix with the needed results. The Calculation Items must be filtered to include only the needed Items) （图 1 – 包含所需结果的矩阵。必须对计算项进行筛选，仅包含所需的项）

The result is as needed. Notice that I must filter the calculation items to exclude the PY (Week) Item, as it would cause an error when used with Quarters and Months. Next, I did it with explicit Measures: 结果符合预期。请注意，我必须筛选计算项以排除 PY (Week) 项，因为它在与季度和月份一起使用时会导致错误。接下来，我使用显式度量值实现了同样的效果：

(Figure 2 – The same Matrix as before, but this time with explicit Measures) （图 2 – 与之前相同的矩阵，但这次使用的是显式度量值）

As you can see, the results are identical. But notice that the first column shows the measure’s name instead of the name of the calculation item, as you can see in the first screenshot. Explicit Measures allows me to modify the name displayed in the visual. For example, the Measures for PY and PM have a different Name: 如你所见，结果是相同的。但请注意，第一列显示的是度量值的名称，而不是像第一张截图那样显示计算项的名称。显式度量值允许我修改在视觉对象中显示的名称。例如，PY 和 PM 的度量值可以有不同的名称：

(Figure 3 – The Labels of the explicit measures with the original names) （图 3 – 使用原始名称的显式度量值标签）

This is impossible when using calculation items. The Visuals always show the names of the calculation items, and I cannot rename them. I don’t even see the name of the original measure. Therefore, I must add a meaningful title to the Visual. But I recommend doing this anyway. 在使用计算项时，这是不可能实现的。视觉对象总是显示计算项的名称，我无法重命名它们。我甚至看不到原始度量值的名称。因此，我必须为视觉对象添加一个有意义的标题。不过，无论如何我都建议这样做。

Using other visualization types (columns or bars)

使用其他可视化类型（柱状图或条形图）

Next, I did it with column visuals: 接下来，我使用柱状图进行了尝试：

(Figure 4 – Column visuals with calculation items (top) and explicit measures (bottom)) （图 4 – 使用计算项（上）和显式度量值（下）的柱状图）

The top visual uses the calculation items, and the bottom visual contains the explicit measures. Here, we have the same situation as before: I must add a filter on the calculation items for the top visual. I can rename the measures in the bottom visual. But the results are still the same. I left the default title and legend position. You can see that they need to be changed, as they contain duplicate information. Moreover, in the top variant, you can see the term “Time Function” in the Subtitle, which is meaningless to any report consumer. Besides the titles and subtitles, the differences are even smaller compared to the matrix visual. 上方的视觉对象使用计算项，下方的包含显式度量值。这里的情况与之前相同：我必须为上方的视觉对象添加计算项筛选器。我可以重命名下方视觉对象中的度量值。但结果仍然相同。我保留了默认的标题和图例位置。你可以看到它们需要更改，因为它们包含重复信息。此外，在上方变体中，你可以在副标题中看到“Time Function”字样，这对任何报表消费者来说都是毫无意义的。除了标题和副标题外，与矩阵视觉对象相比，它们的差异甚至更小。

Pivot tables in Excel

Excel 数据透视表

Now, let’s look at how it works in Excel Pivot tables: But here we have an issue with the PY Calculation Item: 现在，让我们看看它在 Excel 数据透视表中是如何工作的：但这里我们在 PY 计算项上遇到了一个问题：

(Figure 5 – Excel PivotTable while using Calculation items. Note the missing PY values for the first shown year, even though there are values) （图 5 – 使用计算项时的 Excel 数据透视表。注意第一个显示的年份缺少 PY 值，尽管实际上是有数据的）

What doesn’t change is the need to filter the calculation items to keep only the needed items: 不变的是，仍然需要筛选计算项以仅保留所需的项：

(Figure 6 – Filter out the calculation item PY (Weeks), as it isn’t needed) （图 6 – 筛选掉不需要的计算项 PY (Weeks)）

As you can see, the PY column is Empty, even though there is data for the year 2022. When trying this with explicit measures, I got this result: 如你所见，PY 列是空的，尽管 2022 年有数据。当尝试使用显式度量值时，我得到了以下结果：

(Figure 7 – Excel PivotTable while using explicit measures. The PY issue persists (marked in red), but doesn’t appear when using measures using classic time intelligence (marked in green)) （图 7 – 使用显式度量值时的 Excel 数据透视表。PY 问题依然存在（红色标记），但在使用经典时间智能度量值时不会出现（绿色标记））

Even with explicit measures, the PY issue persists. I then added a PY measure using the classic time intelligence, and it worked, as shown above with the values highlighted in green. This points to an issue with Excel and calendar-based time intelligence. But I can still rename the measure names, like in Power BI. Therefore, there is no difference between the two variants. 即使使用显式度量值，PY 问题依然存在。随后我添加了一个使用经典时间智能的 PY 度量值，它成功了，如上图绿色高亮部分所示。这指向了 Excel 与基于日历的时间智能之间的问题。但我仍然可以像在 Power BI 中那样重命名度量值。因此，这两种变体之间没有区别。

The user’s view – again

再次审视用户视角

When we look at the report consumer, we can create the same reports without seeing a difference. At least for the simple examples that I showed above. For the report designer, it’s a different story. This type of user must know how to use the data model and the calculation groups. This is a hindrance to self-service BI, where developers make the data model available, and other users create their own reports. Good documentation on how to use the data model, along with education and training, is imperative when using only calculation groups instead of… 当我们审视报表消费者时，我们可以创建相同的报表而看不出区别。至少对于我上面展示的简单示例是这样。但对于报表设计者来说，情况就不同了。这类用户必须知道如何使用数据模型和计算组。这对于自助式 BI 来说是一个障碍，因为在自助式 BI 中，开发人员提供数据模型，而其他用户则创建自己的报表。当仅使用计算组而不是……时，关于如何使用数据模型的良好文档以及教育和培训是必不可少的。