理解派生列

显示全部楼层 · 2021-2-21 18:52:14

派生列（或称作扩展列）是添加到现有表中的列。投影函数可以添加派生列，比如你可以通过 ADDCOLUMNS 和 SUMMARIZE 来获得派生列。在 ADDCOLUMNS 函数一文中，我们介绍过派生列的概念，你也可以使用 SUMMARIZE 创建派生列，比如下面的 Products 是派生列：

EVALUATE
SUMMARIZE(
Product,
Product[Product Name],
"Products", COUNTROWS( Product )
)

复制代码

结果中的 Product Name 列来自 Product 表的同名列，它继承了原始列的沿袭（Lineage），是原生列。SUMMARIZE 函数创建的表可以包含多个原生列，并且只保留组合的不重复值，它功能类似于编辑查询中的分组，在分组/连接函数中你会详细了解 SUMMARIZE 函数。

如果只想获得产品名称和有效日期的所有组合，可以编写以下查询

EVALUATE
SUMMARIZE(
Product,
Product[Product Name],
Product[Available Date]
)

复制代码

计算派生列

接下来，当你需要基于当前查询新建派生列计算某个指标的时候，ADDCOLUMNS 和 SUMMARIZE 都可以使用，但是出于性能考虑，建议始终选择 ADDCOLUMNS。例如，您可以使用以下两种方法来添加年份信息：

EVALUATE
SUMMARIZE(
Product,
Product[Product Name],
Product[Available Date],
"Year Production", YEAR( Product[Available Date] )
)

复制代码

EVALUATE
ADDCOLUMNS(
SUMMARIZE(
Product,
Product[Product Name],
Product[Available Date]
),
"Year Production", YEAR( Product[Available Date] )
)

复制代码

两种写法返回相同的结果

最佳实践

在绝大部分情况下，建议你始终用 ADDCOLUMNS 计算派生列，不要用 SUMMARIZE，除非遇到以下例外情形：

需要在一个或多个分组列上使用 ROLLUP 计算每组的总计
派生列使用了某些非常特殊的表达式

------------- 避免使用这种写法 ------------------
SUMMARIZE( <table>, <group_by_column>, <column_name>, <expression> )
----------------- 推荐写法 ----------------------
ADDCOLUMNS(
SUMMARIZE( <table>, <group by column> ),
<column_name>, CALCULATE( <expression> )
)

复制代码

派生列的数据沿袭

DAX 有一个违反直觉的限制：派生列可以用于分组，可以用于过滤（作为 FILTER 的第二参数），但你无法根据派生列创建新的计算指标（计算列或度量值）。例如，下面的查询在 Internet Sales 表中添加派生列，它返回对数表达式计算的单价范围。单价在 0 和 1 之间的销售被分组为 1，单价在 1 和 10 之间的销售被分组为 10，单价在 10 和 100 之间的销售被分组为 100，以此类推。

EVALUATE
ADDCOLUMNS(
'Internet Sales',
"Price Level", POWER( 10, 1 + INT( LOG10( 'Internet Sales'[Unit Price] ) ) )
)

复制代码

使用 SUMMARIZE 可以对派生列 Price Level 的结果进行分组，这样你可以方便的查看当前所有销售记录总共被分成了那些组：

EVALUATE
SUMMARIZE(
ADDCOLUMNS(
'Internet Sales',
"Price Level", POWER( 10, 1 + INT( LOG10( 'Internet Sales'[Unit Price] ) ) )
),
[Price Level]
)
ORDER BY [Price Level]

复制代码

分组后的派生列

到目前为止一切正常，而一旦你想基于派生列计算新列时，问题就出现了，在某些方式下查询会得到错误的结果，原因是派生列不具有数据沿袭，无法有效的筛选数据模型。

EVALUATE
SUMMARIZE(
ADDCOLUMNS(
'Internet Sales',
"Price Level", POWER( 10, 1+INT( LOG10( 'Internet Sales'[Unit Price] ) ) )
),
[Price Level],
"Total Sales", SUM( 'Internet Sales'[Sales Amount] ) //2020 年 3 月更新：此处可正常计值
)
ORDER BY [Price Level]

复制代码

无论哪一种 Price Level，派生列的总销售额始终返回 Internet Sales 表所有销售额的总和，这就是问题所在。原因是 Price Level 列不属于 Internet Sales 表，而是在一个与 Internet Sales 无关的单独的虚拟表中，来自这个虚拟表的筛选上下文无法筛选 Internet Sales 表。

2020 年 3 月更新：SUMMARIZE 的行为在最近发生了一些变化，上面的公式可以得到预期的结果，稍后会补充关于这方面的详细介绍。

你可以尝试换一种写法，下面的查询用 CALCULATE 和 ADDCOLUMNS 计算 Total Sales，但结果仍然是错的，原因同上。

EVALUATE
ADDCOLUMNS(
SUMMARIZE(
ADDCOLUMNS(
'Internet Sales',
"Price Level", POWER( 10, 1 + INT( LOG10( 'Internet Sales'[Unit Price] ) ) )
),
[Price Level]
),
"Total Sales", CALCULATE( SUM( 'Internet Sales'[Sales Amount] ) )
)
ORDER BY [Price Level]

复制代码

由于 Price Level 所在的虚拟表和 Internet Sales 表没有建立关系，为了得到正确的结果，我们需要将有效的筛选条件作为 CALCULATE 的筛选参数，这个筛选条件的逻辑是判断 Internet Sales 表的每行记录是否属于当前的 Price Level。

EVALUATE
ADDCOLUMNS(
SUMMARIZE(
ADDCOLUMNS(
'Internet Sales',
"Price Level", POWER( 10, 1 + INT( LOG10( 'Internet Sales'[Unit Price] ) ) )
),
[Price Level]
),
"Total Sales",
CALCULATE(
SUM( 'Internet Sales'[Sales Amount] ),
FILTER(
'Internet Sales',
[Price Level]
= POWER( 10, 1 + INT( LOG10( 'Internet Sales'[Unit Price] ) ) )
)
)
)
ORDER BY [Price Level]

复制代码

DEFINE
MEASURE 'Internet Sales'[Price Band]
= POWER( 10, 1 + INT( LOG10( VALUES( 'Internet Sales'[Unit Price] ) ) ) )
EVALUATE
ADDCOLUMNS(
SUMMARIZE(
ADDCOLUMNS(
'Internet Sales',
"Price Level", [Price Band]
),
[Price Level]
),
"Total Sales",
CALCULATE(
SUM( 'Internet Sales'[Sales Amount] ),
FILTER( 'Internet Sales', [Price Level] = [Price Band] )
)
)
ORDER BY [Price Level]

复制代码

两种写法都返回了正确的结果，简洁版本避免了重复引用相同的表达式，写法更优雅。

你需要在基于派生列分组的后续计算中使用适当的筛选上下文，因为派生列本身无法作为筛选上下文过滤它所在的表

派生列的命名

我们通常使用非限定名称（只定义列名，不加表名）来命名派生列。

EVALUATE
FILTER (
ADDCOLUMNS (
'Product Category',
"Subcategories", CALCULATE ( COUNTROWS ( 'Product Subcategory' ) ),
"Products", CALCULATE ( COUNTROWS ( Product ) )
),
[Products] > 500
)

复制代码

Products 列和 Subcategories 列使用了非限定名称，也就是没有加表名作为标识符。外层的 FILTER 在引用 Products 列时使用了一种引用度量值的语法，这在引用派生列时很常见。在这种情况下添加的列具有列的语义，但因为不具备数据沿袭，并不能筛选模型，因此它没有真正意义上的完全限定名。但是，你仍然可以使用完全限定的名称定义列名，如下所示：

EVALUATE
FILTER (
ADDCOLUMNS (
'Product Category',
"Subcategories", CALCULATE ( COUNTROWS ( 'Product Subcategory' ) ),
"'Product Category'[Products]", CALCULATE ( COUNTROWS ( Product ) )
),
'Product Category'[Products] > 500
)

复制代码

使用完全限定名并不会将列添加到表中；它只是使语法能够在后续表达式(例如 FILTER)中通过引用完全限定的名称引用这些列。

列名在 ADDCOLUMNS 的结果中必须是唯一的。因此，对于那些未出现在结果中的数据模型的列，我们可以在结果中重写它们的名称，并且可以输出更多带有相同名称但不同表名的列。此外，你使用的表名不必是数据模型中相关连的表，可以将添加列前的表名看作占位符，注意必须使用数据模型的现有表名，同时建议你保持前后一致的命名和避免重复命名导致原有的列被覆盖的情况。例如，下面的语法是有效的，虽然我们很少这么写，并且它对理解结果也没有帮助(在 Product Category 中添加两列更直观)：

EVALUATE
ADDCOLUMNS (
'Product Category',
"Product Subcategory'[Rows]", CALCULATE ( COUNTROWS ( 'Product Subcategory' ) ),
"Product[Rows]", CALCULATE ( COUNTROWS ( Product ) )
)

复制代码

因为派生列不属于模型中的任何表，我们在引用的时候通常使用非限定名称

重名导致的问题

在本文派生列的数据沿袭一节的最后，简洁版写法在查询的开头定义了一个名为[Price Band]的度量值，你可能会好奇，如果度量值和派生列的名称都使用[Price Level]会发生什么？即使这么做在语法上没有问题，它也会使查询难以阅读，并得到错误的结果。实际上，如果我们尝试使用[Price Level]而不是[Price Band]来命名这个度量值，查询是这样的：

DEFINE
MEASURE 'Internet Sales'[Price Level]
= POWER( 10, 1 + INT( LOG10( VALUES( 'Internet Sales'[Unit Price] ) ) ) )
EVALUATE
ADDCOLUMNS(
SUMMARIZE(
ADDCOLUMNS(
'Internet Sales',
"Price Level", [Price Level]
),
[Price Level]
),
"Total Sales",
CALCULATE(
SUM( 'Internet Sales'[Sales Amount] ),
FILTER( 'Internet Sales', [Price Level] = [Price Level] )
)
)
ORDER BY [Price Level]

复制代码

以这种方式编写的查询不能得到正确的结果，错误的原因是高亮行 FILTER 第二参数两侧始终引用的是派生列，而非 Define Measure 定义的本地度量值，所以每行都返回 True。

重名导致查询返回错误的结果

在这个案例中，EARLIER 不能解决问题。这个查询的问题在于，作为一种最佳实践，我们习惯在引用度量值时使用非限定名称（比如[Sales Amount]），引用列时使用完全限定名称（比如 Sales[Sales Amount]），原因是在表格模型中，度量值不能与数据模型的任何表的任何列具有相同的名称。不加表名可以让度量值在查询中容易识别，即使这不是严格要求的。但是，在查询中定义本地度量值时，表名+列名的写法可以覆盖模型中的同名列。

这个案例中，本地度量值和派生列使用了相同的名称[Price Level]，高亮行的条件判断引用的[Price Level]都来自派生列，为了将两者区分开来，我们引用’Internet Sales'[Price Level]作为度量值，因为派生列不属于任何物理表，我们不在它的前面加表名。