度量值、计算列和查询

编写 DAX 有三种场景：度量值、计算列和查询。现在你需要学习 DAX 中非常重要的一个概念：计算列和度量值的区别。尽管它们乍一看很相似，因为某些情况下你可以用两种方式得到相同的结果，但实际上它们存在显著的区别，理解这种区别是解锁 DAX 能力的关键之一。

计算列

例如，如果你想在 Excel 中创建一个计算列，你可以简单地移动到表的最后一列，即添加列，然后开始编写公式。其他工具可能有不同的用户界面，但操作类似。


在 Power Pivot 中创建计算列


计算列与表中的任何其他列一样，你可以在数据透视表或其他报表的行、列、筛选器或值中使用它。如果需要，还可以使用计算列来定义关系。定义计算列的 DAX 表达式在它所属表的当前行上下文中计值。任何对列的引用都会返回当前行中该列的值，你不能直接访问其他行的值。


后面你将看到，聚合函数可以为整张表聚合列值。想要获取行子集的值，唯一方法是使用可以返回表的 DAX 函数，然后对其进行操作。通过这种方式，你可以为特定范围的行聚合列值，并可以通过过滤仅由一行组成的表格来操作不同的行。在关于计值上下文的文章中，你将继续了解这部分内容。

关于计算列，需要记住的一个重要概念是，它在内存数据库刷新时计算，然后存储在模型中，报表中的筛选器无法影响它的计算过程。如果你习惯于 SQL 的计算列(非持久化的)可能会觉得有些奇怪，因为后者在查询时计算，并且不使用内存。然而，在表格模型中，所有计算列占用内存，并在表刷新时才进行计算

当你创建非常复杂的计算列时，了解这种行为是有帮助的。计算列占用模型加载的时间而不是查询时间，以获得更好的用户体验。不过，你必须始终记住，计算列使用的是电脑的内存（通常是有限的）。例如，如果计算列使用了一个复杂的公式，你可能会尝试将计算步骤分离到不同的中间列中。虽然这种技术在项目开发中很有用，但在实际应用的时候不是一个好习惯，因为每个中间计算都存储在 RAM 中，占用了较多的内存。

度量值

在 DAX 模型中还有一种定义计算的方法，当你不想沿着表格逐行计算，而是想在上下文环境中对表的多行进行聚合计算时，这种方法非常有用。我们称这些计算为度量值。


度量值使用的表达式通常利用聚合函数（如 SUM、MIN、MAX、AVERAGE 等）生成标量结果，并且结果永远不会存储在模型中。 度量值的使用非常广泛，从简单的列聚合到更复杂的公式（覆盖筛选上下文和/或关系传播的公式）应有尽有。


例如：你可以在销售表中定义名为 GrossMargin 的列来计算毛利：

1. Sales[GrossMargin] = Sales[SalesAmount] – Sales[TotalProductCost]

复制代码

但如果你想显示毛利占销售额的百分比会怎样呢？你可以使用以下公式创建一个计算列：

1. Sales[GrossMarginPct] = Sales[GrossMargin] / Sales[SalesAmount]

复制代码

正如你在下图中看到的，公式在行级别的计算结果是正确的




GrossMarginPct 列以百分比显示毛利率，逐行计算


然而，当你计算某个百分比的总值时，你不能依赖于计算列。实际上，你需要用毛利之和除以销售额之和。因此，在这种情况下，需要基于聚合的结果计算比率；而不能直接使用计算列的加总。换句话说，你计算的是和的比率，而不是比率的和。计算 GrossMarginPct 的正确的方式是使用度量值：

1. GrossMarginPct:=SUM ( Sales[GrossMargin] ) / SUM (Sales[SalesAmount] )

复制代码

然而，正如我们已经说过的，你不能将其输入到计算列中。如果操作的对象是聚合值而不是逐行，则必须创建度量值。

在 Excel 中我们使用“:=”来定义一个度量值，而不是等号(=)，以便更容易区分代码中的度量值和列。而在 Power BI Desktop 创建度量值和计算列都可以直接输入“=”开始

度量值和计算列都使用 DAX 表达式；区别在于计值上下文。度量值是在透视表或报表所在的上下文环境中计算的，而计算列是在它所在表的行级别计算的。单元格的筛选上下文取决于用户对透视表的选择或者 DAX 查询的形态。因此，当你在度量值中使用 SUM(Sales[SalesAmount])时，公式将在该单元格的上下文环境中计算 SalesAmount 列的总和，而当你在计算列中使用 Sales[SalesAmount]时，指的是 SalesAmount 列在当前行的值。


以散点图为例，报表层度量值的计值环境


度量值需要定义在表中，这是 DAX 语言的要求之一。然而，度量值并不真正属于任何表。事实上，你可以将度量值从一个表移动到另一个表，而不影响正常使用，比如你在 Power BI Desktop 中选择任意度量值，在建模选项卡下可以切换它所在的表。

虽然度量值和列在书写的时候对是否添加表名并无强制要求，但从易用性出发，强烈建议你只在列引用的时候添加表名，度量值则无需添加表名，这会减少代码阅读时的歧义。

自动生成的度量值

除了手动编写度量值，还有一种方法可以自动创建度量值，任何列（我们通常使用数值列）都可以通过报表视觉对象或问答进行汇总。 这是 DAX 为模型开发者提供的便利，让你在很多情况下都无需创建度量值。 例如，将销售额列拖入图表后可以选择多种汇总方式（包括 SUM、AVERAGE、MEDIAN、MIN、MAX 等），无需为每种聚合类型手动创建度量值。但是如果你需要在后续计算中引用它们，就必须手动创建。


为自动创建的度量值选择聚合方式

快速度量值

Power BI Desktop 提供一种无需书写代码，快速创建常用甚至复杂度量值的功能，官方是这样介绍快速度量值的：

可使用快速度量 快速、轻松地执行常见的高效计算。 快速度量 根据你在对话框中输入的内容，在后台运行一组 DAX 命令（有现成的 DAX，无需编写），然后显示结果以供你在报表中使用。 最重要的是，可以查看快速度量执行的 DAX，从而开始学习或拓展你自己的 DAX 知识…

快速度量值列表


快速度量值创建非常方便，可以在一些简单的模型和逻辑下使用，但随着你的数据量和模型结构复杂性的增加，快速度量可能带来计算性能甚至逻辑上的隐患。原因是 DAX 是一种非常灵活的语言，你必须结合具体的业务逻辑和模型结构才能写出高效的公式，在这一点，程序还无法胜过人脑。关于快速度量，我的建议是尽量不要使用它。

计算列和度量值的区别

虽然看起来相似，但两者其实有显著不同，计算列在模型刷新时计算并使用当前行作为上下文；用户对透视表的操作不会影响计算列。而度量值在当前上下文环境定义的数据集合上进行操作。例如，在数据透视表中，源表基于单元格的坐标被筛选，并使用这些筛选条件聚合和计算结果。换句话说，度量值总是在计值上下文中对聚合的数据进行操作，其默认的执行模式不引用任何一行。

正确选择计算列和度量值

现在你已经了解了计算列和度量值之间的区别，接下来你可能想知道两者如何选择。其实两者有时可以通用，但在大多数情况下，计算需求决定了最后的选择。


当你想要执行以下操作时，你必须定义一个计算列：

• 需要将计算结果置于 Excel 切片器；透视表行区域、列区域（而不是值区域）；作为 DAX 查询的筛选条件。
  
• 定义严格绑定到当前行的表达式。(例如，计算Price * Quantity 时不能对两列求和或求平均后再相乘)
  
• 对文本或数值做分类时。(例如，度量值的范围，客户的年龄范围：比如 0-18，18-25 等等)。
  

然而，当你想在由用户设定筛选条件的数据透视表值区域中看到计算结果时，你必须定义一个度量值，例如：

• 基于透视表的选择计算利润率百分比。
  
• 存在年份和地区筛选器的情况下，计算一个产品占所有产品的比率。
  

你可以使用计算列和度量值来表示同一计算，即使在这种情况下需要使用不同的 DAX 表达式。例如，你可以将 GrossMargin 定义为一个计算列:

1. Sales[GrossMargin] = Sales[SalesAmount] - Sales[TotalProductCost]

复制代码

也可以定义成度量值

1. GrossMargin:= SUM ( Sales[SalesAmount] ) – SUM( Sales[TotalProductCost])

复制代码

在这种情况下，我们建议你使用度量值，因为其在查询时进行计算，这种方式对模型文件的大小几乎不会有任何影响，这在大型数据集中非常重要。

不过，当模型大小不是问题时，不必纠结于哪一种方法，你可以使用自己更熟悉的方式，不必矫枉过正。

交叉引用

显然，度量值可以引用一个或多个计算列。反之亦然，但可能就不那么直观了，计算列可以引用度量值：通过这种方式，度量值在当前行定义的上下文中计算。这个操作将度量值转换并固化到一个列中，列不会受到用户操作的影响。显然，只有特定的操作才能产生有意义的结果，因为通常一个度量值的计算很大程度上依赖于用户在数据透视表中所做的选择。

查询

编写查询也是 DAX 的常用功能之一，与度量值和计算列不同的是，查询返回表结构的数据，而不是标量值。DAX 查询编写起来与 SQL 类似，SQL 是一种声明式语言。通过使用 SELECT 语句声明要检索的数据集来定义所需的内容，而不必考虑引擎如何运作和检索信息。而 DAX 则是一种函数式语言。在 DAX 中，每个表达式都是一个函数调用，而函数的参数可以依次被其他函数调用。对参数的计算可能导致 DAX 执行非常复杂的查询计划。


例如，如果你只想检索住在欧洲的客户，你可以用 SQL 编写:

1. SELECT
   
2. Customers.CustomerName,
   
3. SUM ( Sales.SalesAmount ) AS SumOfSales
   
4. FROM Sales
   
5. LEFT JOIN Customers
   
6. ON Sales.CustomerKey = Customers.CustomerKey
   
7. WHERE
   
8. Customers.Continent = 'Europe' GROUP BY
   
9. Customers.CustomerName
   

复制代码

使用 DAX，你不需要在查询中声明 WHERE 条件，相对应的，DAX 用特定的函数（FILTER）来筛选结果：

1. EVALUATE
   
2. SUMMARIZE (
   
3.     FILTER ( Customers, Customers[Continent] = "Europe" ),
   
4.     Customers[CustomerName],
   
5.     "SumOfSales", SUM ( Sales[SalesAmount] )
   
6. )
   

复制代码

可以看出，FILTER 是这样一个函数：它只返回住在欧洲的客户，查询得到了预期的结果。函数嵌套的顺序和函数的类型对最终结果以及引擎的性能都有很大的影响。在 SQL 中也是这样，即便你相信查询优化器可以找到最优的查询计划。在 DAX 中，虽然查询优化器做的也很好，但代码的编写者不能据此掉以轻心，仍要以写出运行良好的代码为责任。

示例内容 EVALUATE 是用于声明查询的函数，在基础表函数中会详细介绍

编写查询最常用的工具是DAX Studio，你可以用它连接 Power Pivot，Power BI Desktop 和 SSAS 表格模型进行语句的调试和编写，DAX Studio 是 DAX 开发人员的必备工具，访问前面给出的链接可以了解它的使用方法。


DAX Studio 界面


除此之外，你也可以在 Excel 和 Power BI 中直接运行查询。

在 Excel 中编写 DAX 查询

Excel 不能像 Power BI Desktop 那样直接在模型中新建表，不过你可以使用一种叫链接回表的方法来解决这个问题，链接回表的操作步骤和限制比较多，建议只在必须借助 DAX 才能完成计算的情况下使用。




创建链接回表的步骤


注意事项：

• 链接回表必须在 Excel 工作簿中暂存，所以数据量上限 100 万行
  
• 导出时可以选择一个数据量较少的表，减少导出消耗的时间
  
• 第 4 步中选择的表是由 PQ 导入的查询表，从表格直接导入模型的表无法作为链接回表
  

链接回表操作繁琐，不是一种优雅的解决方案，是一种为了解决新建表问题不得已而为之的策略

在 Power BI Desktop 中编写 DAX 查询


通过建模选项卡 – 新建表，将查询结果生成一张表。此功能不需要使用 EVALUATE 声明查询，这是它和另外两种方式的区别。




在 Power BI Desktop 中使用查询

顶起出售广告位

锄禾日当午，发帖真辛苦。谁知坛中餐，帖帖皆辛苦！

老大，这个说的很好，这个真不错！

无论是不是沙发都得回复下

云发教育是我遇到最好的机构，和其他机构有本质区别

顶起出售广告位

看起来好像不错的样子

忙完了，来看看视频

呵呵，低调，低调！