Extreme DAX-第3章 DAX 的用法

Power BI 模型的真正强大之处在于通过使用 DAX 语言进行计算。虽然许多 Power BI 用户专注于模型并试着完全避开使用 DAX，但是除了最简单的基础聚合运算以外，其他所有的计算都需要通过 DAX 来实现。而且，你迟早会在 Power BI 中遇到更复杂的计算需求。根据我们的经验，典型的情况会是：你精心制作的一个 Power BI 报告初稿，会引出有关这些数据的越来越多、越来越复杂的问题。

本书的第二部分旨在为您提供一些启示，让您更好地了解 DAX 可以用来做什么，以及如何使用 DAX 来解决实际业务问题。在我们深入研究第二部分所介绍的场景之前，我们仍然有一些基础知识要介绍。在本章中，我们将简要介绍 DAX 在 Power BI 中的不同用法。

• 计算列
• 计算表
• 度量值
• 安全筛选器
• DAX 查询

除此之外，我们还将讨论如何使用 DAX 创建日期表。本章最后总结了一些使用 DAX 的最佳实践。

3.1 计算列

计算列（calculated column）是通过执行 DAX 计算，在 Power BI 模型的表中新建一个数据列。举个简单的例子：通过将销售的产品数（Quantity）乘以每个产品的价格（Price）来计算销售额（Amount）。注意，在 DAX 中列名要写在方括号内。

Amount = [Quantity] * [Price]

如图 3.1 是生成的计算列。

图3.1 计算列

在搭建 Power BI 模型的过程中，新建一些计算列是添砖加瓦的最简单直接的办法。尤其是当您经常使用 Excel，这个操作会非常自然且顺手，因为大多数 Excel 用户借助 Excel 工作时都是直接在列中写公式进行计算。但我们强烈建议：尽量不要使用计算列，除非你有什么万不得已的理由。如下给出解释。

• 计算列创建的新数据会占用模型中的空间。上一章我们讨论过，列越多，模型就越大而且速度越慢。
• 如果出于某种原因，你需要从模型中删除一张表并以另一种方式再次创建这个表（你可能不相信自己会这么傻，但是请放心，总会有这么一天的），所有的计算列都会随着你的删除动作一并消失，然后，从头再来。
• 用于创建计算列的列（比如上一个示例中的 [Quantity] 和 [Price] 列）需要保留在模型中，但这一列可能并没有其他的用途。在此示例中，你可以好好想一下 [Price] 列还能用来做什么。或许，可以计算每个产品的平均价格？答案是否定的：平均价格应按销售的产品数量加权，因此 [Price] 列的直接平均值是不正确的。正确的做法是：将总销售额除以销售的产品总数，并且计算过程根本用不到 [Price] 列。
• 计算列中的计算结果是静态的：仅在创建列或者刷新 Power BI 模型时这些值才会被计算。这与 DAX 和 Power BI 报表的动态特性相悖。

计算列的问题在于，大多数情况下，这些操作属于数据准备层面，或者属于我们在第1章“商业智能中的 DAX”中讨论的五层模型中的“数据预处理”层。在进行数据预处理这一道工序时，有很多比 DAX 更好的工具，比如 Power Query。最佳模型是销售交易记录表中保留 [Quantity] 和 [Amount] 这两列，删除 [Price] 列。

当然，不使用计算列这一规则也有一些例外，当您使用 DAX 处理更高级的方案时，可能会遇到这些例外情况。

• 有时，在创建复杂的 DAX 计算时，您会发现其中一部分实际上是固定不变的，基于此，它确实可以用计算列来实现。如果这是一个复杂的计算，而且在报告的使用过程当中需要反复地计算这个结果，那么通过计算列来实现，您可以获得显著的性能提升。不过，您还是应该先考虑在“数据预处理”层中创建列！
• 有一些计算被用来生成模型中的列，如果这些计算使用 Power Query 这样的“数据预处理”层中的工具很难实现，而使用恰当的 DAX 函数却可以很简单地实现。在这种情况下，通过使用计算列，不仅可以节省开发时间，而且数据刷新的性能也会大大提高。这种情况一般发生在所需列的值是某些复杂聚合的结果时。但是，如果您遇到这种情况，还是应该先深入思考一下，假设没有计算列，这个问题能否得到解决！

总而言之，除非有很好的理由，否则还是不要使用计算列。

3.2 计算表

计算表（Calculated table）与计算列相当：它们将数据直接添加到 Power BI 模型中，只不过是以整个表的形式。若要创建计算表，通常需要特殊的 DAX 表函数。在第4章“上下文和筛选”中我们将简单介绍一些表函数，并且在本书第二部分，我们将一起深入学习这些 DAX 表函数；。

若要在 Power BI 模型中创建一个简单的计算表，可以使用表构造函数。如下的表达式仅由大括号之间的值列表组成，它创建一个包含一列的表。

Example = {1,2,3}

此公式的结果是一个名为 Example 的表，只包含一个 [Value] 列，如图3.2所示。

图3.2 使用表构造函数创建的计算表

请注意，表构造函数不会对创建的表提供太多的设定。生成的列名为 Value，并且 Value 列的数据类型与所提供的值一致（很明显，在大多数时候是相当准确的）。如果提供的值是不同类型的数据，那么它将自动选择一个可以存储所有值的数据类型。例如：

Example2 = {1, 2, "3"}

此公式生成的表中 Value 列是文本数据类型。

表构造函数允许创建具有多个列的表，方法是按行提供一系列值的列表，每一行用括号分隔，代码如下。

Example3 = {(1, "Red"), (2, "Green"), (3, "Blue")}

如图3.3所示是以上公式的结果呈现。

图3.3 具有两列的表构造函数

我们也可以使用 DATATABLE 函数来创建表，这样的表可以自定义列名并且对数据类型也可以严格把控。该函数的参数是一系列列名和数据类型对，以及包含表中每一行的值的列表。DATATABLE 函数有两个奇怪的特性：首先，数据类型的名称与 Power BI 模型中使用的数据类型的名称不同（比如：INTEGER表示整数类型，STRING表示文本类型等），并且，一行中的值必须包含在大括号中，而不是像表构造函数中那样用小括号分隔。图3.3 中的表，如果使用 DATATABLE 来创建，可以得到更好的列名显示，公式如下。

Example4 =
DATATABLE(
"Number", INTEGER,
"Color", STRING,
{
{1, "Red"},
{2, "Green"},
{3, "Blue"}
}
)

计算表通常用于在 Power BI 模型中创建日期表或日历表。本章稍后会对这些内容展开讨论。

计算列的一些问题同样也适用于计算表：计算表会增加 Power BI 模型的大小，并且你可能正在执行一些实际上是数据准备层面的工作。但是，与计算列相反，计算表不会与模型的其他元素紧密耦合。当你删除那些与计算表相关的用于计算的列或表时，您将收到错误提示；但是只要再次添加这些表或列，这些错误也就没了。

通常，我们的建议是，如果您想要的表可以在五层模型的“数据预处理”层中得到，则不要使用计算表。当您无权参与“数据预处理”层面的工作时（比如，使用集中管理的数据仓库），因为有些表数据仓库并没有提供，或者数据仓库中根本无法储存这样的表，那么此时就可以使用计算表。

3.3 度量值

度量值（Measures），或在某些早期模型版本中叫做计算字段（calculated fields），无疑是 Power BI 模型中最强大的元素，没有之一。实际上，我们在 Power BI 模型上执行的大部分工作都可以归结为设计并应用 DAX 度量值。

在 Power BI 报表中使用事实表中的数字列时，列的值将被执行聚合运算。常见的基本聚合运算包括：求和（sum）、平均值（average）、最小值（minimum）、最大值（maximum）、计数（count）、非重复计数（distinct count）以及一些统计聚合，如标准差（standard deviation）、方差（variance）和中位数（median）。基本聚合运算的种类因数据类型而异，比如，对于日期列，只能选择“最早”、“最新”、“计数”和“非重复计数”这些聚合运算，而不能是其他的。以这种方式使用列时，Power BI 模型会在后台自动创建一个隐式度量值（implicit measure）：隐式度量值是一个聚合函数，能够根据选择的方式对列中的值进行聚合运算。

在实际业务场景中，大部分所需的见解都需要通过复杂的聚合运算来实现，基本的聚合运算完全无法满足要求。相当一部分人（包括 Excel 用户和数据仓库开发人员）会通过生成（计算）列，来创建能提供所需结果的数据，同时还得保证只需要一个基本聚合就能实现。例如，在 Excel 模型和数据仓库中，您可能会遇到一个指示器（indicator），该指示器确定某一行数据是否属于“当前年初至今”。同样，这是一个静态解决方案，不会让您得到两个月前的年初至今数据。

因此，度量值隆重登场了。DAX 允许你通过编写公式创建显式度量值（explicit measure）来实现自己的个性化聚合。例如，前面的计算列部分中讨论的加权平均价格可以通过 DAX 度量值来实现，公式如下。

Average Price = SUM(fSales[Amount]) / SUM(fSales[Quantity])

在此公式中，假定具有销售交易记录的表叫做 fSales 表。

除法运算符 “/”通常由另一个 DAX 函数 DIVIDE 替换，代码如下。
Average Price =DIVIDE(SUM(fSales[Amount]),SUM(fSales[Quantity]))
DIVIDE的优点是当0作为除数时的处理方式比较优雅[1]。这是 DAX 度量值的额外优势的一个简单示例，使用适当的 DAX 函数而不是对列进行基本聚合。

DAX 度量值应当作为您为 Power BI 模型提升智能水平的默认选项。度量值不会向模型添加数据，因此可以使模型一直保持精简与快捷。但是，由于计算是在用户查看报表时按需完成的，因此必须努力创建最高效的计算方式。在本书的第二部分，我们不仅关注如何使用 DAX 度量值解决业务方案，还将重点阐述如何创建高效的 DAX 度量值。

对于平时经常使用 DAX（尤其是 DAX 度量值）的人来说，有一些基本的概念需要透彻的理解。其中包括 DAX 上下文、通过上下文转换进行 DAX 筛选以及 DAX 表函数的概念。我们将在第4章 “上下文与筛选”中详细讨论这些概念。

3.4 DAX安全筛选器

DAX 还可用于在 Power BI 模型中实现安全性。当用户检索报表时，他们将能够通过该报表查看模型提供的所有结果。在许多情况下，需要根据其角色或身份限制用户看到的内容。例如，请看下面的 DAX 安全表达式。

Customer[Region] = "Europe"

为特定安全角色设置时，此 DAX 安全筛选器将使该角色中的用户只能查看欧洲区域中的客户以及与这些客户相关的数据。

我们将在第5章进一步介绍使用 DAX 实现的安全性。

3.5 DAX查询

使用 DAX 的最后一种方法是用作查询语言。当你使用 Power BI 可视化报表时不需要用到此功能，但面向关系型数据库的经典报表工具主要依赖于从数据库中检索自定义数据集来呈现报表。这些的常见数据源是数据仓库或其他数据库；但发布 Power BI 数据集形式的 Power BI 模型也可以以这种方式使用。请注意，截止到本书编写时，你需要具有 Power BI 高级版（Premium）许可证才能执行此操作，每容量（per capacity）或每用户（per user）都可以。

DAX 查询的一个特定用例是在 Power BI 分页报表中。分页报表是使用 Power BI 报表生成器（Power BI Report Builder）开发的，并且可以连接到已发布的 Power BI 模型。其他所有的用例都是在 Power BI Desktop 中开发。

如图3.4所示，Power BI 报表生成器与 Power BI 模型建立连接时，需要填写 DAX 查询以从中检索数据。

图3.4 在 Power BI 报表生成器中编写 DAX 查询

在 Excel 中使用 Power Pivot，作为默认的数据透视表输出的替代方法，可以使用 DAX 查询从 Power Pivot 模型中检索数据，。

与计算表一样，DAX 查询需要表表达式。在本例中，函数 EVALUATE 用于计算表表达式并返回表。下面的表达式返回完整的 Customer 表：

EVALUATE( Customer )

在表表达式中，可以使用所有的 DAX 函数，包括可用于从模型中检索特定聚合结果的 DAX 度量值。唯一的限制是：表达式的最终结果一定是表。

3.6 日期表

几乎所有的 Power BI 模型都包含与日期相关的数据。因此，日期表（或日历表，或者你喜欢怎么称呼它都可以）是 Power BI 模型中的很常见的组成部分。由于 DAX 时间智能函数的存在，日期表在模型中具有特殊的地位（有关于这些函数的详细信息，请查看第 4 章“上下文和筛选”）。

日期表必须包含要分析的日期区间中的所有日期，并且每个日期占用一行。建议以模型中的最小年份作为日期表的开端，并以最大年份结束[2]。日期表必须具有日期列，该列是日期表的唯一键（您也可以自己设置此列的名称）。表中的其他列是每天的属性，如年、月、季度、工作日等。

可以使用“标记为日期表”选项标记包含日期的表。这样，包含日期的列将被认定为正式的日期列：

图3.5 将表标记为日期表

在第 4 章中，我们将讨论时间智能函数，届时，您将了解将一个表标记为日期表有什么优势。

3.6.1 创建日期表

从技术上讲，日期表与其他表没有什么不同。您可能在某个地方有可用的日历数据，那么此时，只需将日期导入 Power BI 模型即可。当然，还有一些其他方法，比如在 Power Query 中的通过输入一些参数（例如，表应跨越哪些年份）来创建日期表，不过本书并不打算对此展开讨论。

在本书中，我们将重点介绍如何使用 DAX 公式通过计算表的方式创建一个日期表。有两个 DAX 函数专门用于执行此操作：CALENDAR 和 CALENDARAUTO。这两个函数都返回一个包含日期的单列表。

CALENDARAUTO 函数将搜索整个模型，并从数据类型为“日期”"或“日期时间”的所有列（不包括计算列和计算表中的列）中查找最小日期和最大日期。日期范围从找到的最小日期所在年份的第一天开始，一直持续到最大日期所在年份的最后一天。虽然这听起来很方便，但您必须意识到，当模型包含诸如出生日期或像2199年12月31日这样奇怪的异常值时，它将创建一个跨越数十年甚至几百年的巨大表格。

因此，更好的选择是 CALENDAR。该函数有两个参数，即要创建的日期表的第一天和最后一天，代码如下。

CALENDAR(
DATE(2021, 1, 1),
DATE(2023, 12, 31)
)

由于该函数生成的结果是单个 Date 列，因此想要得到一个合适的日期表还需要添加更多其他的列。这可以通过使用计算列来完成，但是您可以通过使用 ADDCOLUMNS 函数在一个公式中就得到所有的列，代码如下。

Date =
ADDCOLUMNS(
CALENDAR(
DATE(2021, 1, 1),
DATE(2023, 12, 31)
),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "mmmm", "En-US"),
"MonthNr", MONTH([Date]),
"Year/Month", FORMAT([Date], "yyyy-mm")
)

在上面的公式中，ADDCOLUMNS 函数获取 CALENDAR 函数的结果并向其添加列。您必须为添加的每一列提供一个名称，同时还得有一个提供相应值的表达式。该公式提供了一个使用 FORMAT 函数的范例，该函数可用于应用基于某些值的各种格式，在本例中为日期值，同时还可以自定义设置区域格式。

以上公式的结果如图3.6所示。

图3.6 使用 DAX 公式创建的日期表

在实际模型中，为了更好地匹配新数据的加载，日期表的开始日期和结束日期一般而言是需要设置为动态的。例如，您可以使用 MAX(fSales[OrderDate]) 在 fSales 表中查找最新日期，并将该值用作日期表的结束日期。您还可以使用 DAX 在事实表中查找最后一个订单日期年份的最后一天。我们就不详细展开说明了。

3.7 使用 DAX 的最佳实践

使用 DAX 时，您将受益于以下一些最佳实践。应用这些方法将有助于你创建轻便快捷的模型，长远来看能够让你更轻松地维护模型，并帮助你更好地支持那些从你模型中创建 Power BI 报表或其他输出的人。

3.7.1 首先考虑使用 DAX 度量值

如果在上文中没有足够地表达清楚，那么容我再重复一边：您的主要 DAX 工具应该是度量值。度量值是高度动态的，它们不会使模型变得更臃肿，并且没有哪个计算不能通过度量值来实现。

根据经验，除非你找到了足够好的理由，否则，尽量别碰计算列和计算表！

3.7.2 使用显式度量值

我们建议创建显式 DAX 度量值，而不是直接在可视化报表中使用（事实）表中的数字列，原因如下。

• 在报表中使用列时，Power BI 模型无论如何都会创建度量值，并且很容易自己执行此操作。
• 显式度量值可以指定明确的名称，例如总销售额（Total sales）而不是总和（Amount），也不是 Excel 的 Power Pivot 中的汇总（Sum of Amount）。
• 还可以为度量值指定输出格式。例如，可以设定 Total sales 度量值的显示不带货币符号和小数但使用千位分隔符。此格式可以与从数据类型派生的格式不同。
• 显式度量值可用作更复杂计算的构建基块（见下文）。隐式度量值要么无法使用，要么不方便使用，因为它们无法更改。

不直接使用事实表中的数字列还有其他的优点，即不会有使用不正确聚合的风险。就像我们之前讨论的平均价格度量值那样，如果只是向视觉对象添加列，就很容易出错。

3.7.3 使用基本度量构建代码块

在 DAX 公式中，度量值可以调用，以便在计算中使用这些度量值的结果。使用基本度量值（事实表中最简单的数字列的聚合）作为代码块来构建 DAX 度量值有助于逐步创建一系列更复杂的计算。

使用基本度量值使您不必一遍又一遍地考虑如何计算基础的结果。我们看到很多人这样做。此外，基本度量值可以让您轻松地调整业务逻辑。通常情况下，在开发 Power BI 解决方案的后期阶段，总是会有一些额外的业务逻辑出现。举个例子，起初您可能会被告知“销售额是所有发票金额的总和”。但是，当你费尽千辛万苦终于做出了一个比较满意的 Power BI 模型时，你如果听到“哦，等等，X 类型的发票不计入销售额，应将其排除在外”时，你会不会崩溃呢？您需要将所有与销售相关的度量值全部重做一遍，以将 X 剔除在外。但是如果你使用基本度量值，您只需在基本度量值的公式中排除类型 X 发票即可。

3.7.4 隐藏模型元素

在设计 Power BI 模型时你可能会认为创建报表的人也只有你一个。但是实际上，其他人可能会基于您的模型来构建自己的报表。对于你们所有人来说，最好隐藏模型中会遮盖有用表、列和度量值的元素。

• 关系中的外键列应当隐藏：主键上相同的值，并且会正确地筛选关系的另一端。
• 不在报告中展示的技术（键）列应当隐藏。
• 我们建议隐藏事实表：所有的外键列都应当隐藏，数据格式为数字的列不应直接使用，而应通过显式度量来使用。除此以外，您的事实数据表中可能还有其他列，我们建议您考虑将它们移动到适当的筛选表或者完全删除它们。（事实表中的某些列可能仅用于筛选，而不会向用户公开；它们可以保留在事实表中。）
• 在书写复杂 DAX 度量值时，往往会有一些进行中间计算的 DAX 度量值，他们应当隐藏起来。

有策略地隐藏 Power BI 模型中的一些元素将会避免部分混淆，并减少作为模型设计者的你因“模型不起作用”而收到的问题数。

3.7.5不要将数据和度量值混在一起——请改用度量值表

DAX 度量值始终具有主表，该表是向模型设计人员显示度量值位置的表。更重要的是，在创建 Power BI 报表时，报表设计人员可以在模型的“字段”（Fields）窗格中看到度量值。我们观察到许多人将度量值放在包含所要聚合的列的事实表中。虽然这对于简单明了的度量值（如基本聚合）是可行的，但我们建议不要这样做，原因如下。

• 更复杂的度量值将聚合来自不同表中的列，此时无论将哪个表作为主表都将产生歧义。
• 更重要的是，与计算列一样，如果需要删除一个表并重新创建这个表，您将丢失该表下的所有度量值。

我们建议将所有度量值存储在一个或多个专用的度量值表中。这些表不包含数据，而只用来存放度量值。虽然我们说过最好不要创建计算表，但是度量值表是一个例外。创建度量值表的最简单方法是使用以下公式创建一个计算表。

Results = ROW("ZZ", "OK")

这将创建一个名为 Results 的表，其中包含一列 ZZ 和一行数据。该单行的 ZZ 列中的值是文本“OK”。这个单独的一列必须得存在，因为连一列数据都没有的表并不是表；但是当你隐藏该列时，Power BI 会自动将其识别为度量值表，并将其放置在“字段”窗格的顶部。这使得度量值很容易被找到。该列命名为 ZZ，这样它就会处在度量值表的最底部，而不是一堆度量值中间。此列及其中的值永远不会被使用，因此您可以将“OK”替换为您喜欢的任何内容。结果如图3.7所示。

图 3.7 Power BI Desktop 的数据视图（左）和报表视图（右）中的度量值表

您也可以在 Power Query 中创建度量值表，例如，通过“输入数据”选项。其工作方式也相同：隐藏数据列并添加度量值以使表移动到“字段”窗格的顶部。

但是，在撰写本书时，用于度量表的图标略有不同：通过 Power Query 导入表时，将使用特定的度量表图标，但将其创建为计算表时，将使用计算表的通用图标，如图3.8所示。

图3.8 计算表生成的度量值表（顶部）和导入生成的度量值表（底部）

对于复杂模型，可以在模型视图中使用“显示文件夹”对度量值进行分组。您甚至可以决定使用多个度量值表。例如，我们有时会对所有基本度量值使用单独的度量值表，这些度量值仅用作更高级别计算的构建基块。通过执行此操作，可以一次性隐藏或显示所有的构建基块度量值，只需手动隐藏 ZZ 列。

3.7.6 表类型

建议您明确区分我们在本章和上一章中讨论过的表的类型。除了已经讨论过的三种类型之外，还有另一种表类型，即：辅助表。

• 事实表包含要聚合的主要数据，但不在报表中使用其中的列，处于隐藏状态。
• 筛选表（或维度表）包含要筛选模型结果的所有属性。
• 度量值表不包含任何数据，只包含 DAX 度量值，位于字段列表的顶部。
• 帮助表是用于驱动特定报告行为（如报告时间段的选择）的小表。您将在第六章 “动态可视化”中了解有关辅助表的更多信息。

您无需通过为这些表设置特定的名称来区分这些表的类型。例如，我们并不喜欢在筛选表名称中添加 dim 前缀，比如 dimCustomer、dimCalendar 等。这对使用您模型的用户来说并不是很友好，他们可能会琢磨 dim 到底意味着什么。你应该尽可能地让 Power BI 模型的元素为自己代言，用户一眼就能看出来那是用来干什么的。隐藏事实表、使用度量值表并为筛选表提供描述性名称会呈现一个比较好的结果，即“字段”窗格在顶部展示可用的（计算的）结果，也就是度量值，在底部罗列那些用于筛选这些结果的所有属性，它们都很有条理地成组展示（作为模型设计者，您知道这些属性就是表）。

总结

在本章中，你已了解 DAX 在 Power BI 模型中的不同用法：计算列、计算表、度量值、安全规则和查询。主要结论是，DAX 度量值是（或者说应该是）从模型生成有价值结果的主要方式，实际上，本书的其余部分我们将重点介绍 DAX 度量值。我们为您提供了一些使用 DAX 的最佳做法：避免使用计算列，使用显式 DAX 度量值，创建简单的 DAX 度量值并将其用作更高级计算的构建基块，使用度量值表，以及隐藏可能使报表设计者感到困惑的模型元素（即使这是您自己）。

下一章将介绍的可能是使用 DAX 时要理解的最重要的概念：上下文和筛选。之后，我们将整装待发，一起去探索第二部分的高级 DAX 业务案例。

1. 1 译者注：0作为除数时，如果使用“/”，得到的结果是“∞”，而使用DIVIDE函数会显示空白。↑
2. 1 译者注：例如，假设事实表中包含的最小日期和最大日期分别为2019年2月14日和2022年5月20日，则该日期表的日期范围最好设定为2019年1月1日至2022年12月31日。↑