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

问题描述

裸SUM和包裹在CALCULATE中的sum有什么区别?

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) 以将该 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.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 除非调用站点在一个行上下文中.

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> ).

A measure is not just a named DAX expression. A measure is a named DAX expression with an implicit CALCULATE wrapped around it. Thus, if you want to replace a reference to a measure with its expression, you don't just do a simple text replacement, you would write 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] ) ) )

因此，您的度量在语义上(尽管不是在文本上)是相同的.两者都作为包装在 CALCULATE 中的 SUM 执行.[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.

那么 CALCULATE 是做什么的呢?很多.作为参考，当我进行 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):

一个.CALCULATE

b.CALCULATE

评估 args 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

在由步骤 (1) 和 (2) 确定的新过滤器上下文中计算 arg1 中的表达式.

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:

• 行/列/轴标签(不要混淆数据透视表行以贡献行上下文 - 它不会)
• 数据透视表过滤器
• 切片机
• 其他视觉对象的选择作为交叉过滤
• 视觉/页面/报告/钻取/工具提示过滤器

您可以将过滤上下文视为一组 'Table'[Column]->values 映射.无论选择什么文字值，或满足选择条件，都将成为过滤器上下文.

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.

例如，让我们考虑一个矩阵视觉对象，其中行上包含日历"[Year]，列上包含日历"[MonthName]，切片器为产品"[Category]="Clothing"，页面级过滤器为'日历'[年份]>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"

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

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 等...)
   • 过滤器
   • ADDCOLUMNS

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.

因此，给定一个包含列(Id、Amount、Date)的表 'T'，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

当我们在行上下文中并且我们希望行上的值有助于过滤上下文时，我们可以将聚合包装在 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 裸 SUM 和 CALCULATE 中包含的总和之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！