2.3 Power Pivot数据模型管理界面

2.3.1 从Pivot Table到Power Pivot

在外观上，Power Pivot超级数据透视表与传统Excel数据透视表（Pivot Table）几乎没什么区别，甚至连创建过程都基本类似，那为什么有些人做出来的就是普通的传统Excel数据透视表，有些人做出来的却是功能强大的Power Pivot超级数据透视表呢？秘诀就在于在“创建数据透视表”对话框中的一个关键设置：勾选“将此数据添加到数据模型”复选框。

创建数据透视表的过程如下：选择数据源，依次选择“数据”→“插入”→“数据透视表”命令，弹出“创建数据透视表”对话框，如下图所示。在这个对话框下方有一个“将此数据添加到数据模型”复选框，在默认情况下，这个复选框是不被勾选的。在不勾选“将此数据添加到数据模型”复选框的情况下，单击“创建数据透视表”对话框中的“确定”按钮，生成的是普通的、功能有限的传统Excel数据透视表；在勾选“将此数据添加到数据模型”复选框的情况下，单击“创建数据透视表”对话框中的“确定”按钮，生成的是Power Pivot超级数据透视表。

尽管在外观上，Power Pivot超级数据透视表和传统Excel数据透视表并没有明显的区别，但是，由于勾选了“将此数据添加到数据模型”复选框，因此Power Pivot超级数据透视表与传统Excel数据透视表的内在生成方式完全不同，二者的能力也有天壤之别。

在下图中，在Power Pivot超级数据透视表值区域中计算的是图书销售册数的“非重复计数”，而非销售总册数的和。在传统Excel数据透视表中是没有“非重复计数”功能的，而在Power Pivot超级数据透视表中，实现“非重复计数”功能只是牛刀小试。

回到本节的主题，为什么在勾选了“创建数据透视表”对话框中的“将此数据添加到数据模型”复选框后，会使数据透视表忽然具有如此神奇的功能呢？这是因为在勾选“将此数据添加到数据模型”复选框后，Excel会将数据源中的数据先加载到Power Pivot专用数据库中，使我们可以在将数据展示到数据透视表之前，利用Power Pivot数据模型管理界面对数据进行各种自定义的灵活操作。

如果要查看Power Pivot背后的数据模型（Power Pivot专用数据库），那么选择“Power Pivot”→“数据模型”命令，进入Power Pivot数据模型管理界面，如下图所示。

注意：如果Excel界面上没有Power Pivot菜单项，则需要在Excel中进行如下设置：选择“文件”→“选项”命令，在弹出的“Excel选项”对话框中选择“加载项”选项，在“管理”下拉列表中选择“COM加载项”选项，如下图所示。然后单击旁边的“转到”按钮，在弹出的“COM加载项”对话框中勾选“Microsoft Power Pivot for Excel”复选框，最后单击“确定”按钮。

Power Pivot数据模型管理界面分成上、中、下三部分，上面部分是菜单区，中间部分是刚刚导入模型中的数据，下面部分的单元格是DAX表达式编辑区，即我们写入各种超级强大的DAX表达式的地方，如下图所示。

在Power Pivot数据模型管理界面左下角，可以看到刚刚导入数据的表被Power Pivot数据模型管理界面自动命名为“区域”，而不是Excel工作表的默认名称，如果不喜欢这个名称，那么我们可以对它重命名，但此处我们使用这个默认名称。

在Power Pivot数据模型管理界面，我们可以对来自数据源的数据进行初步观察和研究。

在数据模型管理界面的数据表中，我们可以对数据进行筛选和增加新列等操作，增加的新列在Power Pivot的术语中称为计算列。

在DAX表达式编辑区中，我们可以编写各种实现复杂数据分析运算的DAX表达式。Power Pivot数据模型管理界面相当于Power Pivot数据模型的控制面板，是我们调取Power Pivot强大功能的地方。

需要注意的是，在Power Pivot数据模型管理界面中的筛选操作结果并不会传递到由此生成的Power Pivot超级数据透视表中。Power Pivot数据模型管理界面中的筛选操作只是用于观察后台数据和初步调试DAX表达式的，与由此生成的Power Pivot超级数据透视表没有任何联系。

2.3.2 Power Pivot中的自定义运算方法

我们先从一个最简单的DAX表达式开始：利用Power Pivot和DAX表达式实现传统Excel数据透视表中已有的一个功能，即利用DAX中的SUM（）函数对图书销售册数进行汇总。

首先创建一个Power Pivot超级数据透视表，注意在“创建数据透视表”对话框中勾选“将此数据添加到数据模型”复选框；然后选择“Power Pivot”→“数据模型”命令，进入Power Pivot数据模型管理界面，我们看到，数据已经加载到Power Pivot数据模型管理界面中了。注意，如果在创建数据透视表时没有勾选“将此数据添加到数据模型”复选框，那么在Power Pivot数据模型管理界面中是看不到任何数据的。

Power Pivot数据模型管理界面中的数据和数据源是动态链接的，也就是说，如果数据源中的数据发生变化，那么在单击Power Pivot数据模型管理界面菜单栏中的“刷新”按钮后，Power Pivot数据模型管理界面中的数据也会跟着发生变化。

接下来在DAX表达式编辑区中任意一个单元格中输入如下DAX表达式：

上述操作如下图所示。

观察发现，在输入“=SUM（”之后的左单引号（'）时，Power Pivot会提示数据模型中可用的字段名称，这大大提高了输入DAX表达式的速度，大家要充分利用这个特性。

在输入DAX表达式后，Power Pivot会自动给DAX表达式起一个默认名称“度量值1”。这里需要大家记住：在Power Pivot中，将写在Power Pivot数据模型管理界面下方单元格中、起汇总计算作用的DAX表达式称为度量值表达式或度量值。度量值表达式存储于Power Pivot超级数据透视表值区域中。我们可以自行定义度量值表达式的名称（DAX表达式冒号前面的部分），为了容易理解，我们将“度量值1”改成“my总册数”。

凭借对Excel工作表函数的使用经验，我们推测DAX表达式“my总册数”的作用是对数据源中的“T3销售册数”字段进行累加操作，相当于在数据透视表中增加了一个新的“值”字段（可以拖曳至Power Pivot超级数据透视表值区域中的字段）。下面，我们来看看将这个DAX表达式添加到Power Pivot超级数据透视表值区域中的效果。单击Power Pivot数据模型管理界面菜单栏中的“数据透视表”按钮，在弹出的下拉列表中选择“数据透视表”命令，如下图所示。

现在我们进入了Power Pivot超级数据透视表界面。在“数据透视表字段”视图中拖曳字段，生成如下图所示的数据透视表。此时，在“数据透视表字段”视图中，可以看到一个新的字段“my总册数”，这就是刚才在Power Pivot数据模型管理界面中设计的DAX表达式。该DAX表达式字段现在已经被拖曳至Power Pivot超级数据透视表值区域中了。

我们发现，数据透视表值区域中的数值和直接将数据源中的“T3销售册数”字段拖曳至数据透视表值区域中的默认求和结果一致。

注意，虽然这只是一个最简单的度量值表达式，但是它留给我们的想象空间却是巨大的。在传统Excel数据透视表中，值区域中的汇总方式只能在已有的几种方式中选择，而在Power Pivot超级数据透视表中，我们可以用DAX函数自定义设计数据汇总方式。

2.3.3 Power Pivot中的度量值表达式与计算列公式

前面已经介绍过，Power Pivot数据模型管理界面下面部分的单元格是DAX表达式编辑区，我们将写在这里的DAX表达式称为度量值表达式。可以将度量值表达式简单地理解为“一种自定义的数据透视表值区域汇总方式”。

当Power Pivot的数据源为单个表时，度量值表达式可以写在DAX表达式编辑区中的任意单元格中，度量值表达式与其所在单元格的位置无关，只需度量值表达式引用了数据源中正确的字段。

在传统Excel数据透视表中，如果数据源中的某个字段是数字类型，那么当我们将这个字段拖曳至数据透视表值区域中时，默认的汇总方式是求和。在Power Pivot超级数据透视表中，我们可以设计DAX表达式模拟传统数据透视表的这个行为。

在Power Pivot数据模型管理界面中，除了可以在DAX表达式编辑区中输入度量值表达式，还可以在Power Pivot数据模型管理界面中的数据表最右侧添加新的计算列。

在Power Pivot中，我们将Power Pivot数据模型管理界面中的数据表中新增加的列称为计算列。计算列一般用于Power Pivot超级数据透视表筛选环境中（如行标题、列标题、切片器、筛选字段等）。

在下图中，我们为数据表添加了一个新的计算列，用于生成图书大类代码。具体操作方法为，在数据表最后的空白列中任选一个单元格，输入计算列公式“=LEFT（'区域'[T0大类K]，1）”，按Enter键即可得到结果。这个DAX函数和Excel工作表函数的名称和用法基本相同。在DAX中，很多简单函数与Excel工作表函数的名称和用法基本相同。计算列的默认名称一般是“计算列+序号”，为了使计算列的名称更加友好，可以双击列名，将默认列名重命名为自己喜欢的名称，如这里将计算列的名称重命名为“大类代码”。

2.3.4 最重要的函数——CALCULATE（）函数

前面介绍了在Power Pivot超级数据透视表值区域中自定义汇总方式的基本方法，即设计DAX表达式。接下来，我们用DAX表达式实现更加灵活的Power Pivot超级数据透视表值区域汇总方式。利用这种方式，我们可以在Power Pivot超级数据透视表值区域中实现大部分复杂的汇总分析。下面我们隆重介绍一个非常重要的DAX函数——CALCULATE（）函数。

我们知道，数据透视表值区域中每个单元格中的内容虽然看起来只是一个简单的数值，但每个数值背后，都对应着一个由该单元格所处的数据透视表筛选环境下的数据源子集，该数值便是在该数据源子集的基础上汇总计算而来的。换句话说，数据透视表值区域中每个单元格中的数值，都是由它对应的数据源子集经过某种特定的汇总计算的结果。

由此我们推想：如果有一种方法能根据实际需求修改数据透视表值区域中的单元格所对应的数据透视表筛选环境，从而得到数据源的任意子集的汇总值，那么我们还有什么做不到的事情？DAX就是用于做这个的。而在DAX中，最重要的函数就是CALCULATE（）函数。

CALCULATE（）函数是一个汇总计算函数，该函数的特别之处在于，CALCULATE（）函数在执行汇总计算之前，不仅能够识别其当前所处的数据透视表筛选环境，还能对其所处的数据透视表筛选环境进行修改。

事实上，CALCULATE（）函数的汇总计算是在新的“叠加的数据透视表筛选环境”下进行的。该函数通常使用两个参数，第一个参数为“自定义的汇总计算”，第二个参数为“对当前筛选环境的修改”，其语法格式如下：

CALCULATE（）函数的语法看起来并不复杂，但是，这里需要强调一点，那就是CALCUCATE（）函数的内部运算逻辑：先对当前数据透视表筛选环境进行修改，再执行自定义的汇总计算。之所以强调CALCULATE（）函数的内部运算逻辑，是因为它的内部运算逻辑与参数的出场顺序是相反的，这与我们熟知的Excel工作表函数的工作方式完全不同，大家一定要留意。在CALCULATE（）函数进行汇总计算时，先从第二个参数开始。在CALCULATE（）函数中，第二个参数称为筛选器参数，作用是修改CALCULATE（）函数所处的当前数据透视表筛选环境；第一个参数称为汇总参数，作用是进行汇总计算。

CALCULATE（）函数中的筛选器参数可以省略。在只有汇总参数的情况下，CALCULATE（）函数默认接受当前数据透视表筛选环境，也就是说，如果将CALCULATE（）函数用于数据透视表值区域中，那么CALCULATE（）函数会在其当前所处的数据透视表筛选环境下进行汇总计算。

我们从最简单的情况开始，研究只有汇总参数的CALCULATE（）函数的应用。例如，在前面介绍的DAX表达式“my总册数”外面包裹一个CALCULATE（）函数，并且将新的DAX表达式命名为“总册数CALCU”，如下图所示。

返回Power Pivot超级数据透视表界面，将新设计的DAX表达式拖曳至数据透视表值区域中，计算结果如下图所示。我们看到，以下两个DAX表达式的计算结果并没有什么不同。

上述DAX表达式的计算结果之所以完全相同，是因为在Power Pivot中有如下特性：

当一个DAX表达式被拖曳至数据透视表值区域中时，会隐含地在该DAX表达式外面包裹一个CALCULATE（）函数。

因此DAX表达式：

在被拖曳至数据透视表值区域中时，会隐含地在该DAX表达式外面包裹一个CALCULATE（）函数，变成：

按照这个规律，我们设计的新的DAX表达式：

在被拖曳至数据透视表值区域中时，也会隐含地包裹一个CALCULATE（）函数，变成：

其效果相当于只包裹了一个CALCULATE（）函数。因此，这两个DAX表达式得到了相同的计算结果。

事实上，CALCULATE（）函数在应用中很少只使用一个参数，因为这并不能施展它的计算能力，因为如果CALCULATE（）函数只使用汇总参数，那么与直接将汇总参数拖曳至Power Pivot超级数据透视表值区域中的计算结果相同。本书接下来会频繁地使用带有两个参数的CALCULATE（）函数，因为CALCULATE（）函数的筛选器参数能够修改CALCULATE（）函数所处的数据透视表筛选环境，从而使CALCULATE（）函数能够在新的数据透视表筛选环境中实现各种灵活的汇总计算。