excel做pq分析（DAX实战手把手教你使用表函数生成销售表和汇总表）

前言

常见的DAX函数返回的是一个值，比如SUM，MAX等，称为值函数，与此同时，DAX中还有很多返回表的表函数，比如FILTER，ALL，VALUES，DISTINCT这些都是比较常用的表函数，新建度量值、新建列都是需要返回一个值，如果用返回表的表达式来创建，将会报错，而新表就是利用DAX创建一个表，它使用的就是返回表的表达式。今天我们来介绍的就是除了上述所说的表函数外，还有一些生成笛卡尔积和返回汇总表的表函数，他们也是我们工作中会经常用来做辅助计算的。

用于生成笛卡尔积表的两个函数

首先，我们来生成一张销售表，在这个过程中，我们将会简单介绍一下生成笛卡尔积表的函数GENERATE和CROSSJOIN。

函数示例一：GENERATE应用示例

销售表 =
GENERATE (
ADDCOLUMNS (
CALENDAR ( DATE ( 2019, 1, 1 ), TODAY () ),
"月", MONTH ( [Date] ),
"销量", RANDBETWEEN ( 0, 300 )
),
DATATABLE ( "门店", STRING, { { "北京" }, { "上海" }, { "广州" }, { "深圳" } } )
)

在实例的这个函数中，GENERATE，ADDCOLUMNS，DATATABLE，是主要需要说明的三个生成表的函数。

ADDCOLUMNS函数（DAX）

ADDCOLUMNS(<table>, <name>, <expression>[,<name>, <expression>]…) 

参数：

table：任何返回表的 DAX 表达式。

name：给予此列的名称，包含在双引号内。

expression：任何返回要填充 name 的单个标量值的 DAX 表达式。

返回值：

返回具有DAX表达式指定的新列的表。

ADDCOLUMNS顾名思义，添加列，在什么上添加列，在某个原始表中添加所需要的列。所以这个函数的第一个参数为表或返回表的表达式，在该表中添加列；第二个参数和第三个参数分别为要添加的列的列名和列值；有的时候只添加一列并不能满足我们的需求，那怎么办呢？诶，ADDCOLUMNS这个函数可以一直往后面添加列，它的第四个参数和第五个参数就分别为要继续添加的列的列名和列值；以此类推……

ADDCOLUMNS(
CALENDAR ( DATE ( 2019, 1, 1 ), TODAY() ),
"月", MONTH ( [Date] ),
"销量", RANDBETWEEN ( 0, 300 )
)

就是生成了一个在含有一列日期的日期表的基础上添加了列名为“月”和“销量”的列。如下：

DATATABLE函数（DAX）

DATATABLE (ColumnName1, DataType1, ColumnName2, DataType2...,{{Value1, Value2...}, {ValueN, ValueN 1...}...})

参数：

ColumnName：任何返回表的 DAX 表达式。

DataType：数据类型。一个枚举，其中包含：INTEGER、DOUBLE、STRING、BOOLEAN、CURRENCY、DATETIME

Value：单个自变量。它将 Excel 语法用于一维数组常量，该常量嵌套为提供数组的数组。此参数表示将在表中的一组数据值

返回值：

声明内联值集的表。

DATATABLE顾名思义，数据表，DATATABLE函数的参数：DATATABLE (列名1, 数据类型1, 列名2, 数据类型2..., {{值1, 值2...},{值N, 值N 1...}...})

前面的参数用来指定 列名 和 这一列的数据类型，后面 {}包含了数据，每一行都放在一对 { } 中，且用逗号分隔。数据类型支持：string，integer，currency，double，datetime，boolean（布尔型，也就是 true 或 false）。

DATATABLE( "门店", STRING, { { "北京" }, { "上海" }, { "广州" }, { "深圳" } } )

就是生成了一个列名为“门店”，数据类型为文本型，值集包含了北京上海广州深圳的一列。如下：

GENERATE 函数 (DAX)

GENERATE(<table1>, <table2>)

参数:

table1任何返回表的 DAX 表达式。

table2任何返回表的 DAX 表达式。

返回值：

一个表以及一个笛卡尔积，后者是在 table1 中的每行与通过在 table1 中的当前行的上下文中计算 table2 所得到的表之间计算获得的。

GENERATE函数的参数非常简单，就是两个表，但它的内部计算逻辑其实比较复杂，初识这个函数的时候我们只需要了解它并不是简单的分别计算了两张表再合并到一起，而是它第一个参数表的每一行，为第二个表的表达式提供了行上下文，在每一行上分别计算第二个表的表达式。

GENERATE (
ADDCOLUMNS (
CALENDAR ( DATE ( 2019, 1, 1 ), TODAY () ),
"月", MONTH ( [Date] ),
"销量", RANDBETWEEN ( 0, 300 )
),
DATATABLE ( "门店", STRING, { {"北京" }, { "上海"}, { "广州" }, { "深圳" } } )
)

就是生成了一个前两个表相乘的笛卡尔积表。如下：

函数示例二：CROSSJOIN应用示例

与GENERATE类似的还有另一个可以生成笛卡尔积表的函数叫做CROSSJION。

CROSSJOIN函数（DAX）

CROSSJOIN(<table>,<table>[, <table>]…)

参数：

table：任何返回数据表的 DAX 表达式数

返回值：

返回一个包含这些参数的所有表中所有行的笛卡尔积的表。新表中的各列是所有参数表中的所有列。

 销售表1 = CROSSJOIN('销售表','dim商品名称')

CROSSJOIN的参数与GENERATE 函数 的参数类似，就是两个表，返回的结果即为两表相乘的交叉积。在这个公式中，CROSSJOIN的第一个参数是销售表，第二个参数是一列含有商品A，商品B，商品C，商品D的商品名称维度表，交叉相乘结果如下：

至此，我们已经介绍了四个生成表的函数，ADDCOLUMNS，DATATABLE， CROSSJOIN和 GENERATE，并且写出了一个接下来我们会用到的销售表。

问题：如果要看每个月每个门店的销售额，返回一个汇总表，有几种计算方法？

方法一：第一个比较容易想到的返回汇总表的函数应该是SUMMARIZE

SUMMARIZE函数（DAX）

SUMMARIZE(<table>,<groupBy_columnName>[, <groupBy_columnName>]…[, <name>,<expression>]…)

参数：

Table：任何返回数据表的 DAX 表达式。

groupBy_columnName：可选）现有列的限定名称，将使用该列中找到的值创建摘要组。此参数不能是表达式。

Name：给予总计或汇总列的名称，包含在双引号内。

Expression：任何返回单个标量值的 DAX 表达式，其中，表达式将计算多次（针对每行/上下文）。

返回值：

针对一系列组所请求的总计返回摘要表，即其中包含 groupBy_columnName 参数的选定列和由名称参数设计的汇总列的表。

 销售表3 = SUMMARIZE('销售表1','销售表1'[门店],'销售表1'[月],"月销量",SUM('销售表1'[销量]))

1、SUMMARIZE第一个参数是表，第二个参数是某一列，先不写其他参数列时，会返回该表的不重复列表。用于提取维度表，同VALUES和DISTINCT的用法类似。

dim门店 = SUMMARIZE('销售表','销售表'[门店])
dim门店 = VALUES('销售表','销售表'[门店])
dim门店 =DISTINCT('销售表','销售表'[门店])

以上表达式都是提取维度表的做法，可以提取到不重复的门店名称。

2、当我们为SUMMARIZE添加第三个参数列时，它会返回这些列的有效组合，但不同于上述GENERATE或CROSSJOIN返回的是笛卡尔积一样，它返回的只是原销售表中存在的组合。

3、所以当我们继续为SUMMARIZE添加第三、四个参数分别是列名和列值表达式时，它会自动计算并返回分组的汇总表。如下，也就是我们想得到的每个月每个门店的销售额的汇总表。

方法二：利用ADDCOLUMNS和CROSSJOIN的组合返回汇总表

销售表2 = ADDCOLUMNS(CROSSJOIN(VALUES('销售表1'[月]),VALUES('销售表1'[门店])),"月销量",CALCULATE(SUM('销售表1'[销量])))

在此我们可以巩固一下之前所说的ADDCOLUMNS和CROSSJOIN的用法，CROSSJOIN的两个参数分别是销售表1中的月份和门店，CROSSJOIN生成了月份和门店的笛卡尔积，ADDCOLUMNS为这个笛卡尔积表添加了列名为”月销量”，列值为各月各门店销量总计的列，至此，每个月每个门店的销售额的汇总表的第二个方法我们也完成了。

* PowerPivot工坊原创文章，转载请注明出处！

---

延伸阅读：

重点函数 | 表函数与DAX查询
工坊实验室 | ISINSCOPE函数的两个应用场景

工坊实验室 | CALCULATE的嵌套使用

重点函数 | SELECTEDVALUE用法介绍

重点DAX函数 | LASTNONBLANK用法介绍

---

如果您想深入学习微软Power BI，欢迎登录网易云课堂试听学习我们的“从Excel到Power BI数据分析可视化”系列课程。或者关注我们的公众号（PowerPivot工坊）后猛戳”在线学习”。

---

长按下方二维码关注“Power Pivot工坊”获取更多微软Power BI、PowerPivot相关文章、资讯，欢迎小伙伴儿们转发分享~

,