如何在Excel中使用DAX解决各种常见的数据查询问题？

HI，大家好，我是汪站长。

在DAX系列前面的教程中，咱们先后学习了什么是数据模型、计算列和度量；什么是行上下文、筛选上下文、行上下文转换筛选上下文；什么是聚合函数、迭代函数、条件判断函数、筛选器函数；如何使用CALCULATE函数实现各种情况下的数据查询，如何使用RANKX实现各种数据排名等等等等。

那么，现在，是时候学习一下EVALUATE语句了。

1，基本用法

根据DAX函数返回结果的不同，可以划分为两种类型：标量表达式和表表达式。

标量表达式的意思是表达式的结果为单个值，典型如度量。而表表达式呢，顾名思义，表达式的结果是一张表。比如，你已经非常熟悉的FILTER函数，就是一个典型的表表达式。

在Excel中，如果我们需要查看表表达式的运行结果有2种方法。

一种是将数据加载到数据模型，然后再通过自定义加载项的DAX Studio编写EVALUATE语句。另外一种是使用「现有链接」将所需数据加载到数据模型，再通过→【表】→【编写DAX】进行操作。

关于DAX Studio的使用方法，我们后面会有个单章单独聊一下，这里就先给大家分享下第2种方法。

假设有一个工作簿，名称为”Excel星球”，里面有张工作表，名称为”数据源”。

打开这个工作簿，在【数据】选项卡下依次点击【现有链接】→【浏览更多】，在打开的【选取数据源】对话框中选中目标工作簿，最后单击【打开】命令按钮。

在【选择表格】对话框中，选中目标工作表（本例为数据源）并确定，在弹出的【导入数据】对话框中，显示方式保持【表】不变，放置位置选中【新工作表】，并勾选【将此数据添加到数据模型】复选框。

这样一来，我们就将数据源表加载到了Power Pivot，并以超级表的形式在工作表显示，此时系统默认的是显示数据源表的全部数据。

如果我们需要对数据源按班级列进行排序展示，可以右键单击超级表的任意单元格，在右键快捷菜单中依次点击【表格】→【编辑DAX】

在【编辑DAX】对话框中，命令类型切换为”DAX”。在表达式对话框输入以下查询语句，确定即可。

EVALUATE'数据源'ORDER BY '数据源'[班级]

查询结果如下：

以上就是使用DAX实现数据表格查询的一个完整流程。其中使用到了EVALUATE语句，它的简化版语法如下：

EVALUATE <table>[ORDER BY {<expression> [{ASC | DESC}]}[, …]

EVALUATE 子句，包含用于生成查询结果的表表达式。

ORDER BY子句是可选的，表示对查询结果的指定字段进行排序。

例如，返回整表查询，可以使用以下语句：

EVALUATE '数据源'

2，常用查询DAX函数

这节给大家分享一下有哪些常用的DAX表查询函数套路，这包含了选取表的局部行列数据，执行条件查询、条件求和、条件计数，对数据进行分类汇总、单列或多列去重、排名和排序等。

1，选取指定列数据

比如只选取数据源表的姓名和成绩两列数据。

可以使用SELECTCOLUMNS函数。

该函数第1参数为表，其余为配对性质参数，比如，2参为列名，3参则为2参的列内容。

参考代码如下：

EVALUATESELECTCOLUMNS (    '数据源',    "姓名", '数据源'[姓名],    "成绩", '数据源'[成绩])

2，条件查询

条件查询可以使用FILTER函数，这个函数你已经非常熟悉了，不需要解释。

查询班级为1班的数据明细：

EVALUATEFILTER('数据源','数据源'[班级]="1班")

查询班级为1班且成绩及格的数据明细：

EVALUATEFILTER (    '数据源',    '数据源'[班级] = "1班"        && '数据源'[成绩] >= 60)

3，数据去重

单列数据去重可以使用VALUES或SUMMARIZE函数。

以下两个代码均可提取不重复的班级名单。

EVALUATEVALUES('数据源'[班级])或者EVALUATESUMMARIZE('数据源','数据源'[班级])

多列数据去重通常使用SUMMARIZE函数。该函数第1参数是表，其余参数指定了分组列。

以下代码可以查询班级和科目不重复的数据明细：

EVALUATEVALUES('数据源'[班级])或者EVALUATESUMMARIZE('数据源','数据源'[班级])

4，分类汇总

SUMMARIZE函数也可以执行分类汇总功能，只是效率不高，并不推荐使用了。

在Power Pivot Excel中可以使用：

ADDCOLUMNS+SUMMARIZE组合。

比如汇总各个班级的成绩总分：

EVALUATEADDCOLUMNS (    SUMMARIZE ( '数据源', '数据源'[班级] ),    "总分",     CALCULATE ( SUM ( '数据源'[成绩] ) ))

SUMMARIZE对数据源按班级分类，得到班级唯一值组成的单列表。ADDCOLUMNS迭代表的每一行，执行第5行代码的表达式。第5行代码是一个CALCULATE函数，它将行上下文转换为筛选上下文，即筛选不同班级的成绩执行SUM聚合运算。

同样的套路，以下代码可以统计每个班级的学员数量。

EVALUATEADDCOLUMNS (    SUMMARIZE ( '数据源', '数据源'[班级] ),    "人数",     CALCULATE ( DISTINCTCOUNT ( '数据源'[姓名] ) ))

5，条件求和查询

数据分类汇总后，我们就可以执行条件查询。

比如查询1班的成绩总分：

EVALUATEADDCOLUMNS (    SUMMARIZE (        FILTER (            '数据源',            '数据源'[班级] = "1班"        ),        '数据源'[班级]    ),    "总分",        CALCULATE (            SUM ( '数据源'[成绩] )        ))

第4至第7行代码是FILTER函数，它筛选数据源班级为1班的数据作为SUMMARIZE的第1参数。

同样的套路，以下代码可以查询1班和2班的成绩总分：

EVALUATEADDCOLUMNS (    SUMMARIZE (        FILTER (            '数据源',            '数据源'[班级]                IN {                "1班",                "2班"            }        ),        '数据源'[班级]    ),    "总分",        CALCULATE (            SUM ( '数据源'[成绩] )        ))

6，排名查询

使用TOPN函数可以返回表的指定行数，以此可以解决名次查询的问题。

比如查询全校学生总分前三名的数据明细：

EVALUATEVAR a =    ADDCOLUMNS (        SUMMARIZE (            '数据源',            '数据源'[班级],            '数据源'[姓名]        ),        "总分",            CALCULATE (                SUM ( '数据源'[成绩] )            )    )RETURN    TOPN (3,a,[总分], 0)ORDER BY [总分] DESC

第2至第13行代码定义了一个变量a，按班级和姓名分类统计学生的总分。

第15代码是TOPN函数，它有4个参数，分别是获取的行数、表、排序字段和排序方式。本例是对表a的总分字段降序排序，并返回前3行数据，也就是前3名学生的数据明细。

7，排名计算

在DAX系列教程前面的章节我们详细讲解了RANKX函数。

如果需要对每个学生的成绩总分进行排名计算，参考代码如下：

EVALUATEVAR a =    ADDCOLUMNS (        SUMMARIZE (            '数据源',            '数据源'[班级],            '数据源'[姓名]        ),        "总分",            CALCULATE (                SUM ( '数据源'[成绩] )            )    )RETURN    ADDCOLUMNS (        a,        "排名",RANKX (a,[总分])    )ORDER BY [总分] DESC

第2至第13行代码定义了一个变量a，按班级和姓名分类汇总每个学生的总分。

第15至第18行代码添加一列，使用RANKX迭代每行按总分字段计算排名。

……

以上是全局排名，如果需要按班级内部进行排名，参考代码如下：

EVALUATEADDCOLUMNS (    SUMMARIZE (        '数据源',        '数据源'[班级],        '数据源'[姓名]    ),    "总分",        CALCULATE (            SUM ( '数据源'[成绩] )        ),    "班级排名",        RANKX (            VALUES ( '数据源'[姓名] ),            CALCULATE (                SUM ( '数据源'[成绩] )            )        ))ORDER BY    [班级],    [班级排名]

8，排名再计算

通过以上的代码，有些朋友可能多少已经意识到，表表达式未必是一个结果，也可能是一个过程。构建一个虚拟表用于数据再计算，比如创建度量表达式，是表查询最重要的应用场景之一。

举个例子，以上第7个案例的代码执行了排名计算，利用该计算结果，我们可以查询各个班级前3名的学员明细：

EVALUATEVAR a =    ADDCOLUMNS (        SUMMARIZE (            '数据源',            '数据源'[班级],            '数据源'[姓名]        ),        "总分",            CALCULATE (                SUM ( '数据源'[成绩] )            ),        "班级排名",            RANKX (                VALUES ( '数据源'[姓名] ),                CALCULATE (                    SUM ( '数据源'[成绩] )                )            )    )RETURN    FILTER (        a,        [班级排名] <= 3    )ORDER BY    '数据源'[班级],    [总分] DESC