DAX裸加和与CALCULATE中包装的总和之间的差异 [英] DAX Difference between naked SUM and sum wrapped in CALCULATE

问题描述

裸 SUM 与包裹在 CALCULATE 中的总和有什么区别？

What is the difference between naked SUM and sum wrapped in CALCULATE?

Measure1 = SUM( tab[col1]) )
Measure2 = CALCULATE ( SUM( tab[col1]) ) )

更新。

回答其他SO问题，该问题使用包装的计算。问题的作者对此做了解释，但我仍然不明白。这是作者建议的度量标准：

I was given an answer to other SO question, which uses wrapped calculate. The author of the question gave explanation to it which I still do not understand. Here is the measure the author proposed:

Expected Result =
SUMX (
    VALUES ( Unique_Manager[Manager] ),
    VAR SumBrand = CALCULATE ( SUM ( Budget_Brand[BudgetBrand] ) )
    VAR SumProduct = CALCULATE ( SUM ( Budget_Product[BudgetProduct] ) )
    RETURN
        IF ( ISBLANK ( SumProduct ), SumBrand, SumProduct )
)

以及解释：

（...）请注意，我将总和打包在CALCULATE中。这样做是为了使
执行从SUMX内部的行上下文（特定于
的Manager）到在
BudgetBrand和BudgetProduct上使该Manager作为筛选器上下文的上下文转换。将这些总和存储为变量会使
成为一条更具可读性的IF行，并且仅要求对SumProduct进行一次
的计算，而不是两次。

(...) Note that I've wrapped the sums in CALCULATE. This is done to perform the context transition from the row context inside SUMX (the particular Manager) to having that Manager as a filter context on BudgetBrand and BudgetProduct. Storing these sums as variables makes for a more readable IF line and only requres SumProduct to be computed once instead of twice.

我知道什么是过滤器上下文。但是我不明白什么是上下文过渡。还有其他转换吗？

I know what is filter context. But I do not understand what is context transition. Are there any other transitions?

推荐答案

此答案解决了 CALCULATE 的一般用法，并涵盖了主题

This answer addresses the use of CALCULATE generally and covers the topics of filter context, row context, and context transition.

按照下面的原始响应，[Measure1]和[Measure2]的行为相同。有关 CALCULATE 的更详细说明，请参见对此答案的编辑。

As defined in the original question, [Measure1] and [Measure2] would behave identically, per the original response below. See the edit to this answer for a fuller treatment of CALCULATE.

通常，如果您正在寻找行上下文中的表达式，您将使用带有单个参数的 CALCULATE 引起上下文转换（行上下文->过滤上下文）。

In general, if you're looking at an expression in row context, you'll use CALCULATE with a single argument to cause a context transition (row context -> filter context).

我看到的一个常见的初学者错误是表达式中的多余/不必要的 CALCULATE s。 CALCULATE 仅在想要影响第一个参数的过滤器上下文时才应使用。这有两种一般形式：

A common beginner mistake I see is redundant/unnecessary CALCULATEs in expressions. CALCULATE should be used only when you want to affect the filter context of the first argument. This takes two general forms:

1. 您要使用args 2-N来添加，删除或更改过滤器上下文。


2. 您想要转换行上下文以过滤上下文。

两者可能会合在一起。

上面的推论是，除非调用站点位于行上下文中，否则永远不要将 CALCULATE 与一个arg一起使用。

A corollary to the above is that you should never use CALCULATE with one arg unless the call-site is in a row context.

编辑：根据评论和更新的问题

Edit: based on comments and updated question

似乎有些混乱在这个问题上。因此，在进入上下文转换之前，我将首先进行清理。

It seems there's some confusion in this question. So I'll clear that up first, before getting into context transition.

注意：无论我指的是 CALCULATE ，您还可以阅读 CALCULATETABLE ，其行为几乎相同。第一个用于标量表达式，第二个用于表表达式。

Note: wherever I refer to CALCULATE below, you can also read CALCULATETABLE, which behaves nearly identically. The first is for scalar expressions and the second is for table expressions.

度量不仅是一个命名的DAX表达式。度量是一个命名的DAX表达式，在其周围隐含了一个 CALCULATE 。因此，如果要用其表达式替换对度量的引用，则不仅要进行简单的文本替换，还应编写 CALCULATE（< measure body>）。

我尽量不要猜测问题的不同含义，如果询问的形式是自洽的。我相信您想问：

I try not to guess at different meanings of a question if the form it is asked in is self-consistent. I believe you meant to ask:

以下DAX表达式之间有什么区别：

What is the difference between the following DAX expressions:

SUM（'tab'[col1]）

和

计算（SUM（'tab'[col1]））

这与您所提出的问题本质上有所不同，因为您要询问的是两个完全定义的度量，而不是两个DAX代码段。度量的行为相同，因为它们的扩展在逻辑上是等效的：

This is materially different than the question you asked, because you were asking about two fully defined measures, not two snippets of DAX. The measures will behave identically, because their expansions are logically equivalent:

//Measure 1 definition:
Measure1 = SUM ( 'tab'[col1] )

// Measure 1 expands to the following, and you would use this if you were
// replacing a reference with code:
//Expansion1:
CALCULATE ( SUM ( 'tab'[col1] ) )

//Measure2 definition and expansion:
Measure2 = CALCULATE ( SUM ( 'tab'[col1] ) )

//Expansion2:
CALCULATE ( CALCULATE ( SUM ( 'tab'[col1] ) ) )

因此，您的度量在语义上（尽管不完全相同）。两者都以 SUM 的形式包装在 CALCULATE 中。 [Measure2]扩展时恰好有一个额外的 CALCULATE 。

So, your measures are semantically (though not textually) identical. Both perform as a SUM wrapped in a CALCULATE. [Measure2] just happens to have an extra CALCULATE upon expansion.

那么有什么用计算吗？很多。作为参考，当我进行DAX培训时， CALCULATE 以及过滤器和行上下文是一个数小时的时段。

So what does CALCULATE do? A lot. For reference, when I give DAX trainings, CALCULATE and filter and row context are a multi-hour segment.

CALCULATE 执行以下操作。

1. 执行上下文转换。它创建一个新的过滤器上下文，在其中评估其第一个参数表达式。这个新的过滤器上下文包含以下内容（合并到单个过滤器上下文中）：

1. Performs context transition. It creates a new filter context within which to evaluate its first argument expression. This new filter context consists of the following (merged into a single filter context):

a。 CALCULATE

b的调用站点中存在任何外部过滤器上下文。 CALCULATE

b. Whatever row context exists at the call-site of CALCULATE

的调用点上存在任何行上下文都将评估参数2-N（称为setfilters）来修改（添加，删除或修改现有的）步骤（1）中的过滤器上下文，最后

Evaluates args 2-N (known as setfilters) to modify (add to, remove from, or modify existing) the filter context from step(1), and finally

计算arg1中的表达式由步骤（1）和（2）确定的新过滤器上下文。

Evaluates the expression in arg1 in the new filter context determined by steps (1) and (2).

因此，这引出了两个问题，即：

So, this begs a couple questions, namely:

1. 什么是过滤器上下文？


2. 什么是行上下文？


3. 将行上下文转换为过滤器上下文是什么意思？

因此，首先过滤上下文。筛选器上下文来自多个DAX函数，包括 CALCULATE ， CALCULATETABLE ， SUMMARIZE ， SUMMARIZECOLUMNS 和 GROUPBY 。此列表并不旨在详尽无遗，但涵盖了一些非常常见的功能。

So, first, filter context. Filter context comes from several DAX functions, including CALCULATE, CALCULATETABLE, SUMMARIZE, SUMMARIZECOLUMNS, and GROUPBY. This list is not intended to be exhaustive, but does cover some very common functions.

每当您在报表工具中与表格模型互动时，例如Excel数据透视表或Power BI报表，您在GUI中的操作会生成查询，这些查询可用于填充任何可视化对象。从这些（和其他）报告工具的角度来看，过滤器上下文来自：

Whenever you interact with a Tabular model in a reporting tool, e.g. Excel pivot tables or Power BI reports, your actions in the GUI generate queries which are used to populate any visual. From the perspective of these (and other) reporting tools, filter context comes from:

• 行/列/轴标签（请勿混淆）数据透视表行以提供行上下文-不会）


• 数据透视表过滤器


• 切片器


• 其他视觉效果作为交叉过滤的选择


• 视觉/页面/报表/追溯/工具提示过滤器

• Row/Column/Axis labels (don't confuse a pivot table row for contributing row context - it does not)
• Pivot table filters
• Slicers
• Other visuals' selections as cross-filtering
• Visual/Page/Report/Drill-through/Tooltip filters

您可以将过滤器上下文视为一组表 [Column]->值映射。无论选择了什么文字值，或满足选择条件的任何条件，都会成为过滤器上下文。

You can think of filter context as a set of 'Table'[Column]->values maps. Whatever literal values are selected, or meet a selection criteria become the filter context.

例如，让我们考虑一个矩阵视觉图形，在行上带有日历 [年]，列上的日历 [MonthName]，产品 [Category] ​​= Cothing的切片器以及日历 [Year]> 2015的页面级过滤器。我们将查看在第三行和第四列的矩阵中评估的度量[M]的过滤器上下文（2018年4月）

For example, let's consider a matrix visual with 'Calendar'[Year] on rows, 'Calendar'[MonthName] on columns, a slicer of 'Product'[Category]="Clothing", and a page level filter of 'Calendar'[Year]>2015. We'll look at the filter context for a measure, [M], being evaluated in the matrix on the third row and fourth column (April, 2018)

Filter Context:
'Calendar'[Year]=2018
'Calendar'[Year]>2015
    => 'Calendar'[Year] IN {2016, 2017, 2018, ..., N} // for whatever years exist in the calendar
'Calendar'[Month]="April"
'Product'[Category]="Clothing"

矩阵的每个单元格都会根据年和月的交集，但其余部分保持不变。对于底部的总计行，过滤器上下文将没有来自矩阵的特定年份，但仍会受到页面级过滤器的影响。对于右侧的总计列，将没有月份的上下文，但是会有特定的年份。对于矩阵右下角的Grant total单元格，唯一的过滤器将是：

Each cell of the matrix would have its own filter context based on the intersection of year and month, but the rest would remain the same. For the grand total row at the bottom, the filter context would have no specific year from the matrix, but would still be affected by the page level filter. For the grand total column at the right, there would be no month context, but there would be a specific year in context. For the grant total cell at the bottom right of the matrix, the only filters would be:

'Product'[Category]="Clothing"
'Calendar'[Year]>2015 //from the page-level

总而言之，过滤器上下文与您可能理解的内容非常一致。我发现对于大多数人来说，单独过滤上下文是有道理的。

In summary, filter context is pretty much aligned with what you probably understood it as. I have found that for most people, filter context on its own makes sense.

现在用于行上下文。每当我们迭代一个表时，就存在行上下文。您会在两个地方找到行上下文：

Now for row context. Row context exists whenever we are iterating a table. You will find a row context in two places:

1. 向表中添加计算列时


2. 在迭代器函数中，例如：
   
   
   
   
   • -X函数（ SUMX ， AVERAGEX 等...）
   
   
   • 过滤器
   
   
   • 添加列
   
   

1. When adding a calculated column to a table
2. In an iterator function, e.g.:
   • -X functions (SUMX, AVERAGEX, etc...)
   • FILTER
   • ADDCOLUMNS

每当我们谈论行上下文时，我们都在谈论迭代。您可以想到以下for循环：

Whenever we talk about row context, we're talking about iteration. You can think of a for-loop like:

//pseudocode
for row in table:
    <expression>

您可能还认为行上下文类似于SQL游标，它会迭代表的行。它在很大程度上等效于一个快进的只读游标。

You might also consider row context as analogous to a SQL cursor, iterating the rows of a table. It would be largely equivalent to a fast-forward, read-only cursor.

我们一次考虑一行。行上下文由要迭代的表的列中的一组文字值组成。

We consider one row at a time. The row context consists of the set of literal values in the columns of the table being iterated.

因此，给定一个表T，其中包含列（Id，Amount，日期）， SUMX（'T'，< expression）中的行上下文由'T'[Id]，'T'[Amount]，和'T'[Date]。您可以通过< expression> 中的列引用来引用任何这些值。您还可以将表值函数用作迭代器的第一个参数，例如 SUMX（VALUES（'T'[Date]），< expression>）。在这种情况下，我们迭代的表是 VALUES（'T'[Date]）的返回值，这是'T'[Date]中唯一值的集合柱。在这种情况下，行上下文仅包含来自'T'[Date]的值-其余的'T'不在行上下文中。

Thus, given a table, 'T' with columns (Id, Amount, Date), the row context in SUMX ( 'T', <expression ) consists of the specific values of 'T'[Id], 'T'[Amount], and 'T'[Date]. You can refer to any of these values by column reference in <expression>. You can also use a table-valued function as the first argument to an iterator, e.g. SUMX ( VALUES ( 'T'[Date] ), <expression> ). In this case, the table we iterate is the return of VALUES ( 'T'[Date] ), which is the set of unique values in the 'T'[Date] column. In this case, the row context consists only of a value from 'T'[Date] - the rest of 'T' is not in the row context.

注意：当我们处于行上下文中时，我们可以按名称引用一列而不对其进行汇总-这在DAX中除行上下文之外其他任何地方都无效。

Note: when we are in a row context we can refer to a column by name without aggregating it - this is not valid anywhere in DAX except in a row context.

注2：基本诸如 SUM 或 COUNTROWS 之类的聚合函数与行上下文没有交互。因此，对于下面的表和表达式，我们将看到可能没有意义的结果：

Note2: Basic aggregation functions such as SUM or COUNTROWS have no interaction with row context. So, for the table and expressions below we will see results that likely have no meaning:

//Table, 'T' with schema as above
{
  (1, 10, 2019-02-01),
  (2, 20, 2019-02-01),
  (3, 30, 2019-03-01),
  (4, 40, 2019-03-02)
}

//Add calculated column to 'T'
C = SUM ( 'T'[Amount] )
// Result would be 100 on every row - the total of 'T'[Amount]

//Measure on a card visual with no other filters:
M = SUMX ( 'T', SUM ( 'T'[Amount] ) )
// Result would be 400, which is the sum of 'T'[Amount] calculated once per row
// and summed together

//M2 on card with no other filters
M2 = SUMX ( VALUES ( 'T'[Date] ), SUM ( 'T'[Amount] ) )
// Result is 300, which is the sum of 'T'[Amount] calculated once per unique date
// and summed together

当我们在行上下文中并且希望该行上的值有助于过滤上下文时，我们可以包装一个ag聚集在 CALCULATE 中，以将行上下文转换为过滤器上下文。

When we are in a row context and we want the values on the row to contribute to filter context, we can wrap an aggregate in a CALCULATE to transform the row context into a filter context. This is referred to as context transition.

// Same table as above:
M3 = SUMX ( VALUES ( 'T'[Date] ), CALCULATE ( SUM ( 'T'[Amount] ) ) )
// result on card would be 100, the actual total

我们可以将计算分解为以下迭代：

We can break the calculation down into the following iteration:

// Input table would be {2019-03-02, 2019-02-01, 2019-03-01}
//Iteration1:
1. Row context: 'T'[Date]=2019-03-02
2. CALCULATE transitions 'T'[Date] value to Filter context: 'T'[Date]=2019-03-02
3. SUM is evaluated in filter context from step (2)
4. Result of iteration1 = 40

//Iteration2:
1. Row context: 'T'[Date]=2019-02-01
2. CALCULATE transitions 'T'[Date] value to Filter context: 'T'[Date]=2019-02-01
3. SUM is evaluated in filter context from step (2)
4. Result of iteration1 = 30 //note both [Amount]s for 2019-02-01 contribute to this

//Iteration3:
1. Row context: 'T'[Date]=2019-03-01
2. CALCULATE transitions 'T'[Date] value to Filter context: 'T'[Date]=2019-03-01
3. SUM is evaluated in filter context from step (2)
4. Result of iteration1 = 30

// Final result - combine iteration results with sum:
40 + 30 + 30 = 100

请注意，过滤器上下文会自动导航模型中的关系。行上下文仅由要迭代的表中的值组成。如果需要在行上下文中导航关系，则可以使用 RELATED 或 RELATEDTABLE ，或者可以转换将行上下文转换为具有 CALCULATE 或 CALCULATETABLE 的过滤器上下文。

Note that filter context automatically navigates relationships in the model. Row context consists only of the values in the table being iterated. If you need to navigate a relationship in a row context, you can use RELATED or RELATEDTABLE, or you can transform the row context into filter context with CALCULATE or CALCULATETABLE.

因此，在您的链接示例中：

So, in your linked example:

Expected Result =
SUMX (
    VALUES ( Unique_Manager[Manager] ),
    VAR SumBrand = CALCULATE ( SUM ( Budget_Brand[BudgetBrand] ) )
    VAR SumProduct = CALCULATE ( SUM ( Budget_Product[BudgetProduct] ) )
    RETURN
        IF ( ISBLANK ( SumProduct ), SumBrand, SumProduct )
)

SumBrand 是当前行上下文中 Unique_Manager [Manager]的 Budget_Brand [BudgetBrand]的总和，即，该管理器是迭代中当前行的值。同样， SumProduct 是行上下文中经理的'Budget_Product'[BudgetProduct]的总和。

SumBrand is the sum of 'Budget_Brand'[BudgetBrand] for the 'Unique_Manager'[Manager] in the current row context, i.e. the manager that is the value of current row in iteration. Similarly, SumProduct is the sum of 'Budget_Product'[BudgetProduct] for the manager in row context.

您可以就像定义以下内容一样容易：

You could just as easily define the following:

Brand Budget = SUM ( 'Budget_Brand'[BudgetBrand] )

Product Budget = SUM ( 'Budget_Product'[BudgetProduct] )

Expected Result =
SUMX (
    VALUES ( 'Unique_Manager'[Manager] ),
    VAR SumBrand = [Brand Budget]
    VAR SumProduct = [Product Budget]
    RETURN
        IF ( ISBLANK ( SumProduct ), SumBrand, SumProduct )
)

我可能会进行如下重构，以便仅在需要时才计算品牌预算：

I would probably refactor as follows, so that you only calculate the brand budget if you need it:

Expected Result =
SUMX (
    VALUES ( 'Unique_Manager'[Manager] ),
    VAR SumProduct = [Product Budget]
    RETURN
        IF ( ISBLANK ( SumProduct ), [Brand Budget], SumProduct )
)

同或机智尽管进行了重构，但是上述引用度量的版本在语义上与内联 CALCULATE（SUM（...））的版本相同。

With or without that refactoring, though, the version above referencing measures is semantically identical to the version that inlines CALCULATE ( SUM ( ... ) ).

这是因为，如本编辑部分前面所述，以下两个等效：

This is because, as stated earlier in this edited section, the following two are equivalent:

Measure = SUM ( 'tab'[col1] )

CALCULATE ( SUM ( 'tab'[col1] ) )

我希望这对理解我为什么会有所帮助像我一样勇敢地回答您的原始问题。作为度量，您的两个表达式在语义上是等效的。作为孤立的表达式，它们不是。

I hope this is helpful to understand why I was so brave as to answer your original question as I did. As measures, your two expressions are semantically equivalent. As isolated expressions, they are not.

这篇关于DAX裸加和与CALCULATE中包装的总和之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！