DAX中的基础表函数

👆点击“博文视点Broadview”，获取更多书讯

本文将介绍DAX中的基础表函数。

表函数是DAX中的一种常规函数，它返回的结果不是一个标量值，而是一个表。当需要编写DAX查询和迭代表的高级计算时，表函数非常有用。本文会介绍相关的计算示例。

本文的目标是介绍表函数的概念，而并非提供所有DAX表函数的详细说明。

《DAX权威指南》一书的第12章和第13章中介绍了更多的表函数。本文将解释DAX中最常见和重要的表函数的作用，以及如何在常见的场景中，包括标量表达式中使用它们。

01

表函数介绍

到目前为止，你已经知晓DAX表达式通常返回单个值，如字符串或数字。我们称这种表达式为“标量表达式”。在定义度量值或计算列时，我们使用的都是标量表达式，如下面的示例所示：

= 4 + 3
= "DAX is a beautiful language"
= SUM ( Sales[Quantity] )

事实上，度量值的首要目标是生成在报表、数据透视表或图表中呈现的结果。

归根结底，所有这些在报表中呈现的数据都来自一个个数字——换言之，即标量表达式。然而，作为标量值计算过程的一部分，你很可能会用到表。例如，如下所示的销售额计算公式使用表作为迭代过程的一部分：

Sales Amount :=

SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

在本例中，SUMX函数用于迭代Sales表（销售表）。因此，尽管最终的计算结果是标量值，但在计算过程中，公式会扫描Sales表。同样的表达式也可以迭代表函数。下面的公式仅计算销量大于1的行对应的销售额：

Sales Amount Multiple Items :=
SUMX (    
    FILTER (
        Sales,
        Sales[Quantity] > 1
    ),
    Sales[Quantity] * Sales[Net Price]
)

在本例中，我们使用FILTER函数来代替对Sales表的引用。直观地说，FILTER是一个基于条件表达式过滤表的函数，在本章后面将详细介绍这个函数。目前，需要注意的是，每当引用一个物理表时，都可以用一个表函数替换该引用。

重要  在前面的代码中，你看到了一个将FILTER函数返回的结果进行求和的示例。这不是最佳做法。在《DAX权威指南》的第4章中，你将学习如何使用CALCULATE函数来实现更灵活、更高效的筛选。本文示例的目的不是提供DAX度量值的最佳实践，而是解释表函数如何在简单表达式中工作。稍后将在更复杂的场景中应用这些概念。

此外，在《DAX权威指南》的第2章中提到过，可以将变量定义为DAX表达式的一部分。当时，我们使用变量来存储标量值。但是，变量也可以存储表。例如，前面的代码使用变量可以这样修改：

Sales Amount Multiple Items :=
VAR
    MultipleItemSales = FILTER ( Sales, Sales[Quantity] > 1 )
RETURN
    SUMX (
        MultipleItemSales,
        Sales[Quantity] * Sales[Unit Price]
)

MultipleItemSales是一个由表函数定义的变量，所以它存储的是一个表。强烈建议你尽可能使用变量，因为变量使代码更容易阅读。通过简单地为表达式指定一个名称，你可以很好地记录并理解代码。

在计算列或迭代中，还可以使用RELATEDTABLE函数检索相关表的所有行。例如，下面的Product表中的计算列可以计算出对应产品的销售额：

'Product'[Product Sales Amount] =
SUMX (
    RELATEDTABLE ( Sales ),
    Sales[Quantity] * Sales[Unit Price]
)

表函数也可以嵌套。例如，下面的Product表中的计算列只计算销量大于1的产品销售额：

'Product'[Product Sales Amount Multiple Items] =
SUMX (
    FILTER (
        RELATEDTABLE ( Sales ),
        Sales[Quantity] > 1
    ),
    Sales[Quantity] * Sales[Unit Price]
)

在示例代码中，RELATEDTABLE函数被嵌套在FILTER函数中。通常，在遇到嵌套函数的调用时，DAX首先计算最内层的函数，然后逐层计算其他层函数，直至最外层的函数。

注意  后面你会了解到，嵌套函数的执行顺序可能会令你感到困惑，因为CALCULATE和CALCULATETABLE函数的计算顺序与FILTER函数不同。在下面的章节中，你将学习到FILTER函数。在第5章中，会对CALCULATE和CALCULATETABLE函数进行描述。

通常，我们不能将表函数返回的结果作为度量值或计算列的值。度量值和计算列都要求结果为标量值。但是，我们可以将表表达式的结果分配给新建表（Calculated Table）。新建表是一个计算表，其值由DAX表达式决定，而不是从数据源加载。

例如，我们可以创建一个包含单价大于3,000元的所有产品的计算表，使用如下表表达式：

ExpensiveProducts =
FILTER (
    'Product',
    'Product'[Unit Price] > 3000
)

新建表在Power BI和Analysis Services中可用，但在Excel Power Pivot（截至2020年12月）中不可用。随着所使用表函数的增多，你会越来越多地通过在度量值中使用计算表和复杂的表表达式来创建更复杂的数据模型。

02

EVALUATE语法介绍

DAX Studio之类的查询工具对于编写复杂的表表达式非常有用。在这种情况下，检查表表达式结果的常用语句是EVALUATE：

EVALUATEFILTER (    'Product',    'Product'[Unit Price] > 3000)

这段查询语句可以运行在任何能执行DAX查询的工具中，比如DAX Studio、Microsoft Excel、SQL Server Management Studio、Reporting Services等。一条DAX查询语句是一个返回表的DAX表达式，与EVALUATE语句一起使用。EVALUATE的语法较为复杂，在《DAX权威指南》的第13章中会全面介绍，这里只介绍比较常用的一种：

[DEFINE { MEASURE <tableName>[<name>] = <expression> }]EVALUATE <table>[ORDER BY {<expression> [{ASC | DESC}]} [, ...]

初始的 DEFINE MEASURE定义了用于本地查询的度量值（也就是说，它们存在于当前查询的生命周期中）。在调试公式时，它会非常有用，因为你可以定义一个局部变量，测试它，一旦它可以按照预期的行为运行，就可以放到模型中。查询语法主要由可选参数组成，最简单的查询是从现有表中检索所有行和列，如图1所示。

EVALUATE Product

图1  DAX Studio执行查询的结果

ORDER BY子句控制排列顺序：

EVALUATEFILTER (    'Product',    'Product'[Unit Price] > 3000)ORDER BY    'Product'[Color],    'Product'[Brand] ASC,    'Product'[Class] DESC

注意  在模型中定义的按列排序（Sort By Column）属性在 DAX 查询中没有效果，并且EVALUATE指定的排列顺序只能使用结果中包含的列。因此，生成动态DAX查询的客户端应该读取模型元数据（metadata）中的按列排序属性，并在查询中包含排序列，然后生成相应的ORDER BY条件。

EVALUATE本身并不是一个强大的语句。DAX查询的强大之处在于其可以使用众多的DAX表函数。在下一节中，你将学习如何通过使用和组合不同的表函数来创建高级计算。

03

理解FILTER函数

之前已经介绍了什么是表函数，现在该全面介绍其中的基础函数了。实际上，通过对基础函数进行组合和嵌套，你已经可以写出许多强大的表达式。我们要介绍的第一个函数是FILTER，它的语法如下： 

FILTER ( <table>, <condition> )

FILTER函数接受一个表和一个逻辑条件作为参数，并返回满足条件的所有行。FILTER既是一个表函数，又是一个迭代器。为了返回最终结果，它对表进行逐行扫描，并计算逻辑条件。换句话说，它迭代了表。

例如，下面的表达式返回Fabrikam的产品（Fabrikam是一个品牌）。

FabrikamProducts =FILTER (    'Product',    'Product'[Brand] = "Fabrikam")

FILTER函数通常用于减少迭代的行数。例如，如果想要计算红色产品的销量，则可以编写一个类似于下面这样的度量值：

RedSales :=SUMX (    FILTER (        Sales,        RELATED ( 'Product'[Color] ) = "Red"    ),    Sales[Quantity] * Sales[Net Price])

你可以在图2中看到结果，以及总销售额。

图2  RedSales只显示红色产品的销售额

RedSales度量值迭代Sales表的一个子集，即与红色产品相关的Sales表的集合。FILTER向现有条件中添加一个条件。例如，本例中RedSales在Audio行的结果显示了同时属于Audio类别和红色产品的销售情况。

你可以在FILTER函数中嵌套另一个FILTER函数。通常，两个嵌套的FILTER函数得到的结果与将它们和AND函数组合在一起得到的结果相同。观察下面两个查询，它们得到了相同的结果：

FabrikamHighMarginProducts =FILTER (    FILTER (        'Product',        'Product'[Brand] = "Fabrikam"    ),    'Product'[Unit Price] > 'Product'[Unit Cost] * 3)

FabrikamHighMarginProducts =FILTER (    'Product',    AND (        'Product'[Brand] = "Fabrikam",        'Product'[Unit Price] > 'Product'[Unit Cost] * 3    ))

然而，由于两个条件具有不同的约束性，不同的组合方式在大型表上的性能表现可能有所不同。如果一个条件比另一个条件更具约束性，则最佳方法是首先在内层的FILTER函数中应用最具约束性的条件。

例如，如果有许多产品使用Fabrikam品牌，但是很少有产品的价格（Price）是其成本（Cost）的3倍，那么下面的查询将对最内层的FILTER应用Unit Price和Unit Cost的筛选条件。通过这种做法，公式首先应用了最严格的筛选条件，以减少检查品牌所需的迭代次数：

FabrikamHighMarginProducts =FILTER (    FILTER (        'Product',        'Product'[Unit Price] > 'Product'[Unit Cost] * 3    ),    'Product'[Brand] = "Fabrikam")

使用FILTER函数，开发人员通常可以生成更容易阅读和维护的代码。例如，你需要计算红色产品的数量，在不使用表函数的情况下，一种可能的实现方法是：

NumOfRedProducts :=SUMX (    'Product',    IF ( 'Product'[Color] = "Red", 1, 0 ))

内部的IF函数根据产品的颜色返回1或0，对上面这个表达式求和将返回红色产品的数量。虽然这段代码可以求出结果，但是有点怪异。更好的写法如下：

NumOfRedProducts :=COUNTROWS (    FILTER ( 'Product', 'Product'[Color] = "Red" ))

这个表达式更好地表明了开发人员的目的。此外，这段代码更易于阅读，而且DAX优化器还能够更好地理解开发人员的意图。因此，优化器会生成更好的查询计划，从而得到更好的性能。

04

ALL和ALLEXCEPT函数介绍

在上一节中，你学习了FILTER函数，当你想限制表的行数时，它是一个有用的函数。有时候我们需要考虑相反的情况，也就是要扩展行数以实现一些特定的计算。在这种情况下，DAX提供了一组为此目的而设计的函数：ALL、ALLEXCEPT、ALLCROSSFILTERED、ALLNOBLANKROW和ALLSELECTED。在本节中，你将学习ALL和ALLEXCEPT函数，而最后两个函数将在本章的后面介绍，ALLCROSSFILTERD函数将在第14章中介绍。

ALL函数根据使用的参数返回表的所有行，或者一个或多个列的所有值。例如，下面的DAX表达式返回一个ProductCopy计算表，其中包含Product表所有行的副本：

ProductCopy = ALL ( 'Product' )

注意  没有必要将ALL函数用于新建表，因为对报表的筛选不会影响新建表。不过，从下面的例子可以看出，ALL函数在度量值中是有用的。

每当我们需要计算百分比或比率时，ALL函数会非常有用，因为它可以忽略报表自动引入的筛选器。假设我们需要一个如图3所示的报表，其中在同一行中显示销售额（Sales Amount）及其占总计的百分比。

图3  报表显示了销售额及其占总计的百分比

Sales Amount度量值迭代Sales表并执行Sales[Quantity]*Sales[Net Price]：

Sales Amount :=SUMX (    Sales,    Sales[Quantity] * Sales[Net Price])

为了计算百分比，我们需要将销售额除以总计。因此，必须要计算销售额的总计值（总销售额），即使在当前显示的是给定类别的情况下也是如此。这可以通过使用ALL函数来实现。实际上，无论在报表中采用何种筛选器，下列度量值始终要计算总销售额：

All Sales Amount :=SUMX (    ALL ( Sales ),    Sales[Quantity] * Sales[Net Price])

在表达式中，这里用ALL (Sales)替换了对Sales表的引用，很好地利用了ALL函数。此时，我们可以通过简单的除法来计算百分比：

Sales Pct := DIVIDE ( [Sales Amount], [All Sales Amount] )

图4中显示了三个度量值的结果。

图4  All Sales Amount度量值始终返回总计值

ALL函数的参数不能是表表达式，它需要的是表名或列名。你已经了解了ALL函数使用表参数时的行为。如果用一列作为参数，结果会怎样呢？在这种情况下，ALL函数返回该列在整个表中的所有不重复值。下面的Categories计算表来自Product表的Category（类别）列：

Categories = ALL ( 'Product'[Category] )

图5中显示了Categories计算表的结果。

图5  使用列作为ALL函数的参数生成该列的不重复值列表

我们可以在ALL函数的参数中指定同一个表中的多列。在这种情况下，ALL函数返回这些列中所有现有值的组合。例如，我们可以通过将Product[Subcategory]列添加到参数中来获得所有类别和子类别的列表，结果如图6所示。

图6  列表包含类别和子类别的所有不重复值组合

Categories =ALL (    'Product'[Category],    'Product'[Subcategory])

所有形式的ALL函数都忽略任何现有的筛选器。我们可以将ALL用作迭代函数的参数，例如SUMX和FILTER，也可以将ALL用作CALCULATE函数中的筛选器参数。CALCULATE函数在第5章中会介绍。

如果想用ALL函数调用一个表的大部分列，但不是所有列，则可以使用ALLEXCEPT函数。ALLEXCEPT函数的语法是用表作为第一个参数，后面跟要排除的列。因此，ALLEXCEPT函数返回包含该表其他列中现有值组合的唯一列表。

通过ALLEXCEPT函数编写的DAX表达式，将自动在结果中包含将来可能出现在表中的任何附加列。例如，我们有一个包含5列（ProductKey、Product Name、Brand、Class、Color）的Product表，下面两个表达式会产生相同的结果：

ALL ( 'Product'[Product Name], 'Product'[Brand], 'Product'[Class] )ALLEXCEPT ( 'Product',               'Product'[ProductKey],               'Product'[Color])

但是，如果在其后面添加两列Product[Unit Cost]和Product[Unit Price]，那么ALL函数会忽略它们，而ALLEXCEPT函数返回的结果将等价于：

ALL (    'Product'[Product Name],    'Product'[Brand],    'Product'[Class],    'Product'[Unit Cost],    'Product'[Unit Price])

换句话说，ALL函数声明了我们想要的列，而ALLEXCEPT函数声明了我们想要从结果中删除的列。ALLEXCEPT主要作为CALCULATE函数的一个筛选器参数用于高级计算中，很少采用这种较简单的公式。因此，为了完整起见，我们在这里介绍了它的作用，在后面的学习中它才会派上用场。

Top类别和子类别 下面举一个将ALL作为表函数的例子。假设我们想要生成一个仪表板，其中显示销售额超过平均值两倍的产品的类别和子类别。要生成此报表，首先需要计算所有子类别的平均销售额，然后在确定该值后，就从子类别列表中搜索销售额超过平均值两倍的子类别有哪些。 下面的代码生成了我们想要的结果。为了更好地了解表函数和变量的强大功能，我们有必要进行更深入的介绍。 第一个变量（Subcategories）存储所有类别和子类别的列表。之后，AverageSales计算所有子类别的平均销售额。最后，TopCategories从子类别中删除销售额不超过AverageSales值两倍的子类别。 结果如图7所示。 在学习了CALCULATE函数和筛选上下文之后，你将能够使用更短、更有效的语法编写相同的计算表达式。在本例中，你已经了解了经过组合的表函数会产生何等强大的效果，这对于制作报表非常有用。

BestCategories =
VAR Subcategories =
    ALL (
        'Product'[Category],
        'Product'[Subcategory]
    )
VAR AverageSales =
    AVERAGEX (
        Subcategories,
        SUMX (
            RELATEDTABLE ( Sales ),
            Sales[Quantity] * Sales[Net Price]
        )
    )
VAR TopCategories =
    FILTER (
        Subcategories,
        VAR SalesOfCategory =
            SUMX (
                RELATEDTABLE ( Sales ),
                Sales[Quantity] * Sales[Net Price]
            )
        RETURN
            SalesOfCategory >= AverageSales * 2
    )
RETURN
    TopCategories

图7  销售额超过平均值两倍的Top子类别

05

理解VALUES、DISTINCT函数和空行

上一节介绍了ALL函数在使用一列作为参数时可以返回所有唯一值列表，DAX还提供了另外两个类似的函数用来返回一列的唯一值，即VALUES和DISTINCT。这两个函数看起来几乎相同，唯一的区别在于它们如何处理表中可能存在的空行。你将在本节后面学习到有关可选的空行的知识，现在让我们专注于这两个函数的功能。

ALL函数总是返回列的所有不同值，而VALUES函数只从可见值中返回所有不同结果。你可以通过观察以下两个度量值来区分这两种行为：

NumOfAllColors := COUNTROWS ( ALL ( 'Product'[Color] ) )NumOfColors := COUNTROWS ( VALUES ( 'Product'[Color] ) )

NumOfAllColors计算Product表中的所有颜色，而NumOfColors只计算当前筛选器下的可见颜色。这两个度量值的结果（按类别切片）如图8所示。

图8  对于给定类别，VALUES函数只返回颜色的一个子集

由于报表按类别进行切片，因此每个给定类别都包含某些（但不是全部）颜色的产品。VALUES函数返回在当前筛选器中计算的列的不同值。如果在计算列或计算表中使用VALUES或DISTINCT函数，则它们与ALL函数的行为相同，因为没有生效的筛选器。但是，当在度量值中使用时，这两个函数在计算时会考虑现有的筛选器，而ALL函数会忽略任何筛选。

如前所述，这两个函数几乎是相同的。现在的重点是理解为什么同一种行为会出现 VALUES和DISTINCT两个变体。其区别在于它们考虑表中存在空行的方式。首先，我们需要理解，如果没有显式地创建空行，为什么表中会有空行存在？

原因是，当关系无效时（注：这里的无效指约束无效），引擎会在关系“一”端的任何表中自动创建一个空行。为了演示该行为，我们从Product表中删除了所有银色产品。因为最初有16种不同的颜色，去掉了一种之后应该还剩15种。而在图9所示的报表中，显示了一些意想不到的结果：NumOfAllColors仍然是16，并且报表的顶端显示了一个没有名称的新行。

图9  第一行显示了空类别，颜色的总数是16，而不是15

因为Product表位于其与Sales表关系中的“一”端，所以Sales表中的每一行在Product表中都有一个相关的行。然而，由于我们故意从Product表中删除了一种颜色的产品，所以现在Sales表中有很多行与Product表不再具有有效关系。请注意，我们没有从Sales表中删除任何一行；我们删除了一种颜色，是为了打破这种对应关系。

为了确保在所有计算中都考虑这些行，引擎自动向Product表中添加了一行，其中的所有列都为空。Sales表中的所有孤立行（没有有效关系的行）都被连接到这个新引入的空行。

重要  尽管Sales表中的多个不同产品在Product表中不再有对应的ProductKey，但是只有一个空行被添加到Product表中。

实际上，在图9中，你可以看到第一行显示了空类别，并且只包含一种颜色。

此数字对应的行在类别、颜色以及表中所有列上都为空。即使你检查表，也不会看到这一空行，因为它是在数据模型加载期间自动创建的。在某一时刻，如果关系再次变为有效，比如你将银色产品添加回去，那么空行将从报表中消失。

DAX中的某些函数将空行作为其结果的一部分，而其他函数则不会这样做。也就是说，VALUES函数将空行视为有效行，并将其显示出来，而DISTINCT函数不返回空行。你可以通过观察下面的新度量值来了解它们的不同之处，它计算的是不同的颜色而不是数值：

NumOfDistinctColors :=COUNTROWS ( DISTINCT ( 'Product'[Color] ) )

结果如图10所示。

图10  NumOfDistinctColors为空行显示为空值，其总数显示为15，而不是16

一个设计良好的模型中不应该存在无效的关系。因此，如果你的模型是完美的，那么这两个函数总是返回相同的值。然而，在处理无效的关系时，你需要注意这种情况，否则可能会编写出错误的表达式。例如，假设要计算每个产品的平均销售额，一种可行的解决方案是计算产品的总销售额，再除以产品的数量，使用以下代码：

AvgSalesPerProduct :=DIVIDE (    SUMX (        Sales,        Sales[Quantity] * Sales[Net Price]    ),    COUNTROWS (        VALUES ( 'Product'[Product Code] )    ))

结果如图11所示。这显然是错误的，因为第一行中有一个过大的、没有意义的数字。

图11  第一行中显示了一个没有名称的类别的巨大值

第一行中显示的数字（类别为空）对应于所有银色产品的销售情况，它们已经不存在于Product表中。这一行与所有不在Product表中的银色产品相关联。DIVIDE的分子是所有银色产品的销售额，DIVIDE的分母是由VALUES函数返回的单个空行。

因此，一个不存在的产品（空行）包含了Sales表中引用的许多其他产品的销售额，而这些产品在Product表中不可用，从而导致了计算结果是一个巨大的数字。造成这个问题是因为存在无效的关系，而不是公式本身。实际上，无论我们创建什么公式，在Sales表中都有许多产品的销售记录在数据库中没有对应的产品信息。然而，研究同一算法的不同表达式如何返回不同的结果，有助于你理解这个例子。请看以下两种变体写法：

AvgSalesPerDistinctProduct :=DIVIDE (    SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ),    COUNTROWS ( DISTINCT ( 'Product'[Product Code] ) ))

AvgSalesPerDistinctKey :=DIVIDE (    SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ),    COUNTROWS ( VALUES ( Sales[ProductKey] ) ))

在第一个变体中，我们使用了DISTINCT函数，而不是VALUES函数。因此，分母的COUNTROWS返回空，结果也为空。在第二个变体中，我们仍然使用VALUES函数，但是这次计算的是Sales[ProductKey]的数量。请记住，有许多不同的Sales[ProductKey]值，它们都与同一个空行相关。结果如图12所示。

图12 当存在无效的关系时，大部分度量值很可能会出现错误，尽管原因各不相同

有趣的是，AvgSalesPerDistinctKey是唯一计算正确的。由于报表是按类别划分产品的，每个类别都有不同数量的无效ProductKey，它们都被归入单个空行中。

正确的方法应该是修复关系，这样就不会有孤立于Product表的销售记录了。最佳准则是模型中不能存在任何使约束无效的关系。如果出于某种原因存在无效的关系，那么你需要非常谨慎地处理空行，以及它的存在可能会对计算产生的影响。

最后要提醒的是，ALL函数总是会返回空行。如果需要从结果中删除空行，则请使用ALLNOBLANKROW函数。

多列情况下的VALUES函数 VALUES和DISTINCT函数只接受单列作为参数，不支持两列或更多的列。ALL和ALLNOBLANKROW函数也有类似的问题。如果需要从不同的列中获取不同的、可见的值组合，则不能使用VALUES函数。在《DAX权威指南》的第12章中，你将了解到：

VALUES ( 'Product'[Category], 'Product'[Subcategory] )

可以通过以下写法实现：

SUMMARIZE ( 'Product', 'Product'[Category], 'Product'[Subcategory] 

在后面的介绍中，你将了解到VALUES和DISTINCT函数通常用作迭代函数的参数。当关系有效时，它们的结果没有任何区别。在这种情况下，你需要将迭代中的空行视为有效行，以确保迭代所有可能的值。根据我们的经验，VALUES函数应该是你的默认选择，只有当你想显式地排除可能的空值时，才考虑使用DISTINCT函数。在本书的后面，你还会学习到如何利用DISTINCT函数代替VALUES函数来避免循环依赖关系。在《DAX权威指南》的第15章中会讨论这种用法。

VALUES和DISTINCT函数也接受表作为参数。在这种情况下，它们表现出不同的行为：

• DISTINCT函数返回表的不同值，不考虑空行。因此，它会从结果中删除重复的行。
• VALUES函数返回表的所有行，不删除重复项，保留可能存在的空行，表中重复的行保持不变。

06

将表用作标量值

尽管VALUES是一个表函数，但由于DAX的一个特性（即具有单行和单列的表可以像标量值一样使用），我们也会经常使用它来计算标量值。假设有一个如图13所示的报表，报表显示了按类别和子类别划分的品牌数量（NumOfBrands）。

图13  报表显示了每个类别和子类别的品牌数量

如果还想在品牌数量的旁边看到品牌名称，一种可行的解决方案是使用VALUES函数来检索不同的品牌，并返回它们的值（而不是对它们进行计数）。这种方案只适用于品牌存在唯一值的情况。实际上，在这种情况下，使用VALUES函数返回结果是可行的，DAX会自动将其转换为标量值。为了确保只有一个品牌，需要使用IF语句来保护代码：

Brand Name :=IF (    COUNTROWS ( VALUES ( Product[Brand] ) ) = 1,    VALUES ( Product[Brand] ))

结果如图14所示。当Brand Name（品牌名称）列包含空值时，表示存在两个或多个不同的品牌。

图14  当VALUES函数返回一行时，我们可以使用它作为标量值，就像在Brand Name度量值中一样

Brand Name度量值使用COUNTROWS函数检查产品表的品牌列是否只选择了一个值。由于在DAX表达式中经常使用这种方式，我们有一个更简单的函数可以检查列中是否只有一个可见值，它就是HASONEVALUE函数。以下是Brand Name度量值（基于HASONEVALUE函数）的一种更好的写法：

Brand Name :=IF (    HASONEVALUE ( 'Product'[Brand] ),    VALUES ( 'Product'[Brand] ))

为了减轻开发人员的工作量，DAX还提供了一个函数，可以自动检查列中是否包含单个值，如果包含，则返回标量值；如果有多个值，则也可以定义需要返回的默认值。这个函数就是SELECTEDVALUE。所以，前面的度量值也可以被定义为：

Brand Name := SELECTEDVALUE ( 'Product'[Brand] )

通过加入第二个可选参数，可以提供一条消息来说明结果包含多个值：

Brand Name := SELECTEDVALUE ( 'Product'[Brand], "Multiple brands" )

图15中显示了这个最新度量值的结果。

图15  如果Brand Name列有多行，则SELECTEDVALUE函数会返回默认值

如果不返回“Multiple brands”之类的消息，而是希望列出所有品牌，那么该如何修改公式？在这种情况下，一种做法是迭代Product[Brand]的值并使用CONCATENATEX函数，这样即便有多个值，也可以得到很好的效果：

[Brand Name] :=CONCATENATEX (    VALUES ( 'Product'[Brand] ),    'Product'[Brand],    ", ")

现在，结果包含由逗号分隔的不同品牌，而不是“Multiple brands”之类的通用消息，如图16所示。

图16  使用CONCATENATEX函数连接表达式，从表中构建文本

07

ALLSELECTED函数介绍

基础表函数集中的最后一个表函数是ALLSELECTED。实际上，ALLSELECTED是一个非常复杂的表函数——可能是DAX中最复杂的表函数。在第14章中会介绍关于它的所有用法。不过，即使在基础情境下，ALLSELECTED也是很有用的一个函数，值得在本章中介绍。

ALLSELECTED函数在检索表或列的值时非常有用（这类值的特点是在当前报表中可见），并且只考虑当前视觉对象之外的所有筛选器。要了解ALLSELECTED函数在何种情况下有用，请查看图17所示的报表。

图17  报表在同一页面中包含一个矩阵视觉对象和一个切片器

Sales Pct的计算方法如下：

Sales Pct :=DIVIDE (    SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ),    SUMX ( ALL ( Sales ), Sales[Quantity] * Sales[Net Price] ))

因为分母使用了ALL函数，所以它总是计算总销售额，不考虑任何筛选器。因此，如果使用切片器来减少所显示的类别数量，则报表仍然基于总销售额计算百分比。例如，图18显示了使用切片器选择某些类别时的情况。

图18  使用ALL函数，百分比仍然是基于总销售额计算的

矩阵中的一些行因为筛选器的作用消失了，但是其余行显示的值没有变化。而且，矩阵中的总计不再是100%。如果你不希望呈现这种结果，也就是百分比不是基于总销售额计算的，而是只计算筛选器选定的值，则需要使用ALLSELECTED函数。

使用ALLSELECTED函数代替ALL函数来编写Sales Pct的代码，分母在计算销售额时只考虑矩阵视觉对象之外的所有筛选器。换句话说，它返回除Audio、Music和TV之外的所有类别的销售额。

Sales Pct :=DIVIDE (    SUMX (        Sales,        Sales[Quantity] * Sales[Net Price]    ),    SUMX (        ALLSELECTED ( Sales ),        Sales[Quantity] * Sales[Net Price]    ))

结果如图19所示。

图19  使用ALLSELECTED函数，在基于销售额计算百分比时只考虑外部筛选器

总计恢复为100%，报表的数字反映的是占可见总计（Visible Total，即只考虑除当前视觉对象之外的所有筛选器）的百分比，而不是占总销售额的百分比。ALLSELECTED是一个强大而有用的函数，但是，它也是一个非常复杂的函数。在本书的后续章节中，你将了解到关于它的所有内容。由于ALLSELECTED函数的复杂性，有时候它会返回令人意外的结果。这里所说的意外的结果并不是指错误的结果，而是指经验丰富的DAX开发人员也难以理解的结果。

在本章介绍的表达式中使用ALLSELECTED时，它是特别有用的函数。

08

结论

正如你在本章中所看到的，基础表函数的功能非常强大，它们允许你创建许多有用的计算。在许多DAX表达式中，FILTER、ALL、VALUES和ALLSELECTED是非常常见的函数。

学习如何组合使用表函数来得到你想要的结果是一项非常重要的能力，因为它会让你实现更高级的计算。此外，当与CALCULATE函数和上下文转换的能力组合使用时，表函数可以生成坚实、优雅且强大的计算过程。在第4章中，我们将介绍计值上下文和CALCULATE函数。在学习了CALCULATE函数之后，你可能会重新阅读本文，使用表函数作为CALCULATE函数的参数，从而充分利用它们的潜力。

▼

本文摘自《DAX权威指南》一书！

▊《DAX权威指南》

[意] Marco，Russo（马尔·科鲁索），Alberto，Ferrari（阿尔贝托·拉里） 著

高飞 译

• DAX里程碑式经典教材，亚马逊4.8分（总分5分）
• 本书的目的，让你真正掌握DAX！
• 微软MVP，Power BI极客 翻译
• 原书作者、DAX之父、ExcelHome 创始人、Power BI MVP力荐

本书是微软DAX语言在商业智能分析、数据建模和数据分析方面的指南。

通过对本书的学习，你将了解如何使用DAX语言进行商业智能分析、数据建模和数据分析；你将掌握从基础表函数到高级代码，以及模型优化的所有内容；你将确切了解在运行DAX表达式时，引擎内部所执行的操作，并利用这些知识编写可以高速运行且健壮的代码。

（京东满100减50，快快扫码下单吧！）

如果喜欢本文欢迎 在看丨留言丨分享至朋友圈 三连

 热文推荐  
详解云安全攻防模型
淘宝、美团、滴滴分别如何搭建大数据平台
Spring Boot企业级真实应用案例
《数智驱动新增长》领读会成功举办


▼点击阅读原文，获取本书详情~