Power Pivot 系列 (4) - DAX 查询

Power Pivot 通过 DAX 查询可以实现从不同的视角查看数据。但在 Excel 中编写 DAX 查询却不太方便，所以本篇在讲解 DAX 查询用法的时候，以 DAX Studio 作为工具。关于 DAX Studio 请自行在网上搜索，我的上一篇也有介绍。

本篇的示例数据来自 《DAX 圣经》这本书，示例数据我已经上传到 github，文章的末尾有链接，方便大家学习。

DAX 查询一般从 EVALUATE 关键字开始，可以把 DAX 查询语句理解为 EVALUATE 关键字引导的表达式构成的语句。比如我要查询 Sales 表的所有数据，DAX 查询语句为：

相当于 SQL 语句的 SELECT * FROM Sales 第一行是注释。

DAX 查询的 ORDER BY 关键字引导的表达式对查询结果进行排序。升序为 ASC，降序为DESC 。

DAX 支持按多个字段排序：

SQL 语句选择指定字段很直观： SELECT A, B FROM sometable 。DAX 查询选择指定字段用 SUMMARIZE 函数。 SUMMARIZE 函数第一个参数为 table 的名称，后面跟若干个字段，即可以选择指定的列：

DAX 查询结果的界面：

数据筛选用 FILTER 函数， FILTER 函数第一个参数为 table 名称，第二个参数为筛选表达式，返回值为 table。比如我们要筛选出 Product 表中所有 Class 为 Economy 的数据：

数据透视表就是分组计算，如果我们要数据透视表的逻辑，但并不需要数据透视表的格式，使用 DAX 查询的分组计算作为输出就非常合适。分组计算用到 SUMMARIZE 函数的标准用法。 SUMMARIZE 函数语法如下：

函数的第一个参数是 table 名称；第二组参数是一系列列名称，根据列名进行分组，比如先按照客户，再按照产品名称等等；第三组参数由 name 和 expression 成对构成，比如 name 为 toal quantity, expression 为 SUM([Quantity]，就根据 Quantity 列来计算合计数。假设我们需要按客户来计算销售的数量：

多字段分组：先按照客户，再按照产品分组计算销售数量的合计：

前面的示例都是基于一个表，接下来讲解多表关联的 DAX 查询。Power Pivot 中表的关系在关系图视图中维护，关系维护好后，在 DAX 查询时， 表的关系都为左连接且不能修改为其它连接方式 。这种机制虽然降低了灵活性，但却让 DAX 基于多表的查询语法变得非常简单。

比如我们要查询基于客户名称和产品名称的销售数量明细。我们刚才讲过，返回指定字段用 SUMMARIZE 函数：

查询结果截图如下：

这个查询涉及到 3 个表的关联，相同功能的 SQL 语句要复杂得多。同理，基于多个表的分组计算，也是只需要选择某个表的字段，而不需要关注表的关系。我们来对基于客户和产品计算销售数量合计的查询进行变更：

查询的截图如下：

嵌套使用 FILTER 和 SUMMARIZE 函数能达到这种效果。先用 SUMMARIZE 函数返回一个包含指定列的表，然后用 FILTER 函数基于这个计算表进行筛选：

添加列在 Power Pivot 中非常容易，但我们也可以在 DAX 查询中使用 ADDCOLUMNS 函数来添加列。 ADDCOLUMNS 函数的语法如下：

根据函数的语法，我们知道，可以一次添加多个列。下面的示例添加了一个计算列：计算出每一行的销售金额（单价 * 数量）：

在 DAX 查询中，可以使用 VAR 定义变量，使用变量能够简化 DAX 查询语句的编写。定义变量需要在 EVALUATE 之前用 DEFINE 关键字引导，用 VAR 定义变量。比如，我们先定义一个按客户的国别和产品品牌分组计算销售数量的表，将这个表保存在变量 groupedSales 中，然后对销售按品牌进行筛选：

DAX 查询也可以定义度量值。度量值用 MEASURE 关键字定义，MEASURE 返回一个标量值。比如我们要按照品牌计算出销售额，先定义一个度量值，然后再基于品牌来作为筛选上下文计算。这种方法相对难懂，仅为了介绍定义度量值的方法。注意下面 DAX 查询中度量值的表达方法。

github - sample data

1.新建度量值：自带筛选上下文，度量值在模型级别（不属于任何一个表），聚合层面计算，指标体系

2.新建列：自带行上下文，新建列在表级别（隶属于具体表），行层面计算，维度体系

3.新建列时，公式中引用度量值，可以保持原度量值自带的筛选上下文，然后在度量值上再附加行上下文（由新建列提供）

4.新建列时，公式中使用聚合函数，则只有行上下文（由新建列提供），需要通过编辑公式以后，再增加筛选上下文（比如案例：计算超过当前行销售数量的行数），可以在新建列上面附加应用筛选上下文，否则显示的结果只是个固定值（聚合函数计算结果）

5.新建度量值时，公式编辑中引用 列：直接把 '表'[列名] 写上去会报错，原因是直接引用的不是一个单一值，需要结合聚合函数再引用才可以

本次记录在看到大神所做案列模板中关于新建表内容之后，仅为个人理解：

在PowerBI中关于这几个函数的表述如上，那么问题来了，大神模板中addcolumns函数的第一个参数是distinct（列名），为什么不是表呢？那selectcolumns函数后是否也可以用表达式代替第一个参数“表”？身为小白的我，顺利成章的看不懂了。

新建表 = ADDCOLUMNS(DISTINCT('示例表'[订单ID]),"客户id",CALCULATE(FIRSTNONBLANK('示例表'[客户ID],'示例表'[客户ID])))

第一个参数为“表”时候，addcolumns()函数的意义为，返回这个表，并在表后增加相对应的列；第一个参数为“返回表的表达式”时候，第一列为满足表达式的原表列，然后再新增相对应的其他列。(这里用calculate(firstnoblank()) 函数，是因为将订单ID去重之后，原来的客户ID列已经不能与新的订单ID列一一对应，需要用筛选器函数calculate对其进行相应条件的筛选以满足与新的订单ID 列对应关系。

Selectcolumns () 经试验，第一个参数为表达式时候，后边新建列名称所对应的表达式也会出错，故不可用。

通过上述方法，我的理解是，表达式返回的一列姑且认为是只有一列的表，这样来看的话，addcolumns()函数的第一个参数“表”也算是满足了；另外addcolumns函数是在原表上新建列再返回到一个新表中，而selectcolumns则是在空白表新建列，故当第一个参数为表达式时候，addcolumns可以调用原表中其他列，而selectcolumns仅能调用表达式所生成的那一列。

另外，新建表对原订单金额求和时候需筛选器函数与calculate()函数与sum()函数嵌套使用(这时候sum求和的上下文只是同一订单行中的上下文)，否则就相当于调用了原表中一个度量值sum(金额)(上下文为原表所有行上下文)

---