DAX：概述ALL函数

简单的说，当ALL用作表函数时，忽略应用到表上的任何过滤器，并返回数据表；当ALL用作CALCULATE和CALCULATETABLE函数中修饰器时，ALL函数从扩展表中移除已经应用的过滤上下文。

注意自动存在（auto-eixist）对ALL()函数的影响。

正常情况下，包含 ALL() 函数的 DAX 表达式，会忽略已应用到表或指定列上的任何过滤器。但是，由于auto-exists机制的存在，在某些特殊的情况下，事实并非总是如此。自动存在可以优化过滤，以减少某些 DAX 查询所需处理的数据量。

自动存在导致ALL() 出现意外结果的情况是：在同一表中的两个或更多列上进行过滤（例如使用切片器），并且在同一个表上有一个使用 ALL() 的度量（Measure）。在这种情况下，自动存在会把多个过滤器合并为一个，对现有的数据进行过滤，使得ALL()只能对合并之后的值组合进行过滤。由于DAX会首先合并数据，然后再根据合并之后的值组合重新计算度量，因此，计算的结果是基于筛选值（即合并之后的值）而不是预期的所有值。

换句话说，auto-exist会自动应用过滤器，当ALL()用作CALCULATE或CALCULTETABLE的修饰器时，产生的效果相当于auto-exist把额外的过滤器添加到CALCULATE或CALCULTETABLE函数中。

一，ALL函数的定义

ALL 函数用于清除表上的所有过滤器，返回表中的所有行，或者一列的所有值，适用于对表中的所有行进行汇总计算。注意，此函数只能用于基础表（base table），不能用于表表达式或列表达式。

ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )

ALL 函数，用于移除应用到表或列上的过滤器上下文，跟 REMOVEFILTERS 函数的作用相同。当在 CALCULATE 或 CALCULATETABLE 的过滤器参数中直接调用时，不会返回任何数据表，仅用于从过滤器上下文中删除所有过滤器。

以下注释在使用 ALL 作为表表达式时有效：

使用表参数，ALL 返回表的所有行，包括任何重复的行。
使用单个列参数，ALL 返回该列的所有唯一值。
使用两个或多个列参数，ALL 返回多个列中值的所有唯一组合。
在每种情况下，ALL 都会在结果中包含为无效关系生成的额外空白行。

二，深入理解ALL函数

在DAX中，下面两个Measure是完全相同的。RedSalesCompact是布尔过滤器，而RedSalesExtended是扩展语法结构。通常RedSalesCompact会被翻译成（translate）扩展语法，也就是说，CALCULATE的过滤器是table。

RedSalesCompact :=
CALCULATE (
    [SalesAmount],
    Product[Color] = "Red"
)
 
RedSalesExtended :=
CALCULATE (
    [SalesAmount],
    FILTER ( ALL ( Product[Color] ), Product[Color] = "Red" )
)

1，计算百分比

计算百分比的常见做法是将给定度量除以删除某些过滤器的相同度量。例如，针对所有颜色的销售额百分比的正确表达式如下所示：

PctOverAllColors :=
DIVIDE (
    [SalesAmount],
    CALCULATE (
        [SalesAmount],
        ALL ( Product[Color] )
    )
)

这个Measure的含义可以描述为：ALL 返回一个包含所有颜色的表；此表表示要在 CALCULATE 的新过滤器上下文中使用的有效颜色。强制所有颜色可见等同于从颜色列中删除任何和所有过滤器。

这段描述分为两段，两端都是错误的，并不是说描述是完全错误的。它在大多数时候都是准确的，但并非总是如此。

上面 PctOverColors 度量中 ALL 行为的正确描述确实简单得多：ALL removes any active filters from the Color column.

在正确的描述中，没有关于 ALL 结果的陈述，事实上，ALL不返回表，并且在包含所有值的表和删除过滤器之间没有等价关系。事实要简单得多：过滤器被移除。乍一看，这是一个非常迂腐的细节。但是，当用于更复杂的 DAX 表达式时，这种微小的差异可能会产生截然不同的结果。

2，计算已经出售的产品

例如，让我们考虑这两个度量：NumOfProducts 计算产品总数，而 NumOfProductsSold 仅计算已售出的产品，利用表过滤。

NumOfProducts :=
DISTINCTCOUNT ( Product[ProductName] )
 
NumOfProductsSold :=
CALCULATE (
    [NumOfProducts],
    Sales
)

NumOfProducts 很简单，而 NumOfProductsSold 需要额外的 DAX 知识，因为它基于表扩展。因为表被用作 CALCULATE 中的过滤器参数，所以过滤器上下文包含 Sales 的扩展版本的所有列。

DEFINE
    MEASURE Sales[NumOfProducts] = DISTINCTCOUNT ( Product[Product Name] )
EVALUATE
ROW (
    "NumOfProducts", [NumOfProducts],
    "NumOfProductsSold", CALCULATE ( [NumOfProducts], Sales )
)

执行的结果是：

NumOfProducts: 2,517
NumOfProductsSold: 1,170

在存在过滤上下文的情况下，两种度量都将它们的计算限制在当前过滤上下文中。例如，通过添加一个过滤红色产品的外部 CALCULATETABLE，查询变为：

DEFINE
    MEASURE Sales[NumOfProducts] =
        DISTINCTCOUNT ( Product[Product Name] )
EVALUATE
CALCULATETABLE (
    ROW (
        "NumOfProducts", [NumOfProducts],
        "NumOfProductsSold", CALCULATE ( [NumOfProducts], Sales )
    ),
    'Product'[Color] = "Red"
)

执行的结果是：

NumOfProducts: 99
NumOfProductsSold: 51

到目前为止，一切都按预期进行。如果需要根据总计计算当前上下文中的值，会发生什么情况？例如，红色产品的数量除以产品总数，红色产品的销售数量与销售的产品总数之比，生成此报告：

可以这样编写代码：

PercOfProducts =
DIVIDE (
    [NumOfProducts],          -- Number of products
    CALCULATE (
        [NumOfProducts],      -- Number of products
        ALL ( Sales )         -- filtered by ALL Sales
)
 
PercOfProductsSold =
DIVIDE (
    CALCULATE (
        [NumOfProducts],      -- Number of products
        Sales                 -- filtered by Sales
    ),     
    CALCULATE (
        [NumOfProducts],      -- Number of products
        ALL ( Sales )         -- filtered by ALL Sales
    )  
)

令人惊讶的是，这段代码并没有产生上面的报告。相反，结果看起来像这样：

在 PercOfProductsSold 列中，红色产品的百分比是错误的。

3，ALL在CALCULATE函数中的作用

首先，了解将 ALL 用作过滤去除器与将 ALL 用作表函数之间的细微差别至关重要。让我们从头开始：

ALL 是一个表函数，它返回一个表或一组列的所有行。每当实际需要该结果时，这是 ALL 的正确行为。在非常特殊的 CALCULATE 过滤器情况下——并且仅在这种特殊情况下——ALL 不用于从表中检索值。相反，ALL 用于从过滤器上下文中删除过滤器。虽然函数名相同，但函数的语义完全不同。

当用作 CALCULATE 过滤器时，ALL 会删除一个过滤器，它不返回表结果。
为 ALL 的不同语义使用不同的名称是个好主意。一个非常合理的名称应该是 REMOVEFILTER。
事实上，自 2019 年 10 月以来，Microsoft 在 Analysis Services 2019 和 Power BI 中引入了 REMOVEFILTERS 功能。REMOVEFILTERS 类似于 ALL，但它只能用作 CALCULATE 中的过滤器参数。虽然 REMOVEFILTERS 可以替代 ALL，但不能替代用作 CALCULATE 修饰符的 ALLEXCEPT 和 ALLSELECTED。

通过检查 PercOfSoldProducts 的分母，让我们更好地理解它：

PercOfProductsSold =
DIVIDE (
    CALCULATE (
        [NumOfProducts],      -- Number of products 
        Sales                 -- filtered by Sales
    ),     
    CALCULATE (
        [NumOfProducts],      -- Number of products
        ALL ( Sales )         -- filtered by ALL Sales
    )  
)

在这种情况下，ALL 是 CALCULATE 的过滤器参数。因此，它充当 REMOVEFILTERS，而不是 ALL。当 CALCULATE 计算分母中的过滤器时，它会找到 ALL。 ALL 要求从扩展的销售表中删除任何过滤器，其中包括 Product[Color]。因此，过滤器被删除，但没有结果返回给 CALCULATE。

因为没有返回结果，所以扩展的 Sales 表没有被 CALCULATE 用作过滤器。下面的代码是等价的，这里是使用 REMOVEFILTERS 函数而不是 ALL 的相同代码：

PercOfProductsSold =
DIVIDE (
    CALCULATE (
        [NumOfProducts],          -- Number of products
        Sales                     -- filtered by Sales
    ),     
    CALCULATE (
        [NumOfProducts],          -- Number of products
        REMOVEFILTERS ( Sales )   -- with filters removed by Sales
    )  
)

使用 ALL ( Sales ) 并不意味着“使用 Sales 中的所有行进行过滤”。这意味着，“从销售中删除任何过滤器”。通过对公式读取方式的这一小改动，现在很明显，如果不应用过滤器，产品数量就是产品总数。因此，分母总是计算 2,517 而不是 1,170。这解释了为什么百分比从 4.36% 上升到 2.03%。

这种行为肯定看起来很奇怪。然而，正如 DAX 通常的情况一样，这种行为一点也不奇怪，就是这样。如果它不符合我们的期望——那么问题是我们的知识有限，而不是行为本身。

此时，看看如何正确编写公式很有趣。如图所示，ALL 是不够的，因为它不返回其值，它只删除过滤器。一个选项是仍然使用 ALL，但将它移到外部 CALCULATETABLE 中。通过这样做，ALL 仍然表现得像 REMOVEFILTERS，但 CALCULATETABLE 强制返回结果：

PercOfProductsSold =
DIVIDE (
    CALCULATE (
        [NumOfProducts],
        Sales
    ),
    CALCULATE (
        [NumOfProducts],
        CALCULATETABLE ( ALL ( Sales ) )
    )
)

在 ALL 之外使用 CALCULATETABLE 看起来像个把戏，但事实并非如此。它实际上改变了公式的语义，明确表示需要 ALL ( Sales ) 的结果才能过滤公式。使用不太优雅的公式可以获得类似的行为：

PercOfProductsSold =
DIVIDE (
    CALCULATE (
        [NumOfProducts],
        Sales
    ),
    CALCULATE (
        [NumOfProducts],
        FILTER ( ALL ( Sales ), 1 = 1 )
    )
)

在这种情况下，是 FILTER 强制返回 ALL (Sales) 的结果，方法是使用具有始终计算为 TRUE 的条件的虚拟过滤器。

值得注意的是，所有用作过滤器参数的表实际上都是扩展表。因此，删除过滤器的操作不仅会影响基表，还会影响整个扩展表。 ALL ( Sales ) 在 Sales 的扩展版本上充当 REMOVEFILTERS，从表和所有相关维度中删除过滤器。

在 ALLEXCEPT 的情况下，此行为尤为重要。考虑以下措施：

NoFilterOnProduct =
    CALCULATE (
        [Sales Amount],
        ALLEXCEPT ( Sales, Sales[ProductKey] )
    )

在此示例中，ALLEXCEPT 使用一列和三个表作为参数。可以使用包含在用作第一个参数的表的扩展版本中的任何表或列。

参考文档：

Manageing ALL functions in DAX

posted @ 2023-04-20 15:11 悦光阴阅读(563) 评论(0) 编辑收藏举报

刷新页面返回顶部

悦光阴

请不要假装很努力，因为结果不会陪你演戏。