Power Pivot 数据分析表达式（DAX）
官方参考：DAX 函数参考 - DAX | Microsoft Learn
数据分析表达式 （DAX） 是在 Excel 中的 Analysis Services、Power BI 和 Power Pivot 中使用的公式表达式语言。 DAX 公式包括函数、运算符和值，用于对表格数据模型中相关表和列中的数据执行高级计算和查询。
练习题
一、DAX表达式
1. DAX表达式基础
- 定义：Power Pivot特有的函数集（250+函数），中文称数据分析表达式
- 类比：书写与使用方法类似Excel基本函数，比Power Query的M函数简单
- 应用场景：在Power Pivot编辑器内为数据透视表创建规则和新增字段
1）核心特点
- 结果载体：运算结果直接应用于数据透视表
- 作用范围：计算结果作用于整列或表中所有行（不同于Excel的单元格计算）
2）使用规范
- 引用规则：
- 表名用单引号：'表名'
- 字段名用中括号：[字段名]
- 数据类型：需区分表结构数据（表/字段）与常规数据（文本/数值）
- 输入方式：
- 直接公式栏输入
- 通过"fx"图标调用函数对话框
- 大小写规则：函数名不区分大小写（如ALL可写作all），与Excel一致但不同于区分大小写的M函数
3）DAX表达式的介绍与用途
- 函数分类：
- 行上下文计算函数：针对单行数据计算
- 筛选上下文计算函数：针对整个字段汇总
-
- 特殊类别：筛选器函数（如FILTER、ALL等）可实现灵活的数据筛选规则
2. 实战案例应用
1）题目要求
商机金额百分比计算
计算销售人员高赢单率(赢单率=0.75\text{赢单率}=0.75赢单率=0.75)且低风险
- (拖欠还款="无"\text{拖欠还款}="无"拖欠还款="无")的商机金额占比
-
解题步骤
- 表关联：连接商机记录表与企业信息表
- 数据转换：使用SWITCH函数将数值赢单率转为文本描述：
- 字段合并：用RELATED函数跨表获取拖欠还款情况字段
- 条件计算：嵌套使用CALCULATE与FILTER：
- 百分比计算：条件金额总和 / 总金额
- 透视呈现：创建以销售人员为行标签的透视表
关键函数解析
- SWITCH：多条件替换（优于Excel的IF嵌套）
- RELATED：跨表字段引用（效率远高于VLOOKUP）
- FILTER：按条件筛选表数据
- CALCULATE：动态计算规则制定
2）注意事项
- 性能优势：处理数万行数据时，DAX运算效率显著高于Excel原生函数
- 逻辑重点：理解"先筛选后计算"的DAX计算流程
- 学习建议：通过官方文档系统学习250+函数的应用场景
3）操作步骤
1、使用的数据：DAX表达式练习数据
- 数据来源：Power Pivot DAX表达式文件夹下的Excel文件
- 文件信息：
- 主数据文件：DAX表达式练习数据.xlsx（15KB）
- 答案文件：DAX表达式练习数据答案.xlsx（333KB）
1、商机记录表介绍
- 表内容：记录每条商机的详细信息
- 关键字段：
- 商机性质：指尚未完成的商业机会，金额为预测预估金额
- 赢单率：表示商机成功可能性，数值越高赢单可能性越大
- 客户ID：用于连接商机相关企业信息表
- 销售人员：包含三个销售（Sales A、Sales B、Sales C）
- 分析目标：计算高赢单率低风险商机金额占总商机金额的百分比
2、商机相关企业信息表介绍
- 表内容：记录商机对应客户的企业属性信息
- 关键字段：
- 连接字段：客户ID（与商机记录表关联）
- 客户属性：包含客户名称、所在城市、大区、企业规模、所属行业等
- 风险判断指标：
- 有无交易：记录是否与公司有过交易历史
- 有无拖欠还款：判断客户风险等级的关键字段（"无"为低风险，"有"为高风险）
- 风险定义：高风险客户指有拖欠还款记录的客户，可能导致现金流问题
2、Power Pivot文件操作
数据导入操作
- 新建空白文件：首先需要创建一个空的Excel工作簿作为数据导入的基础
- 导入位置：所有数据导入操作都在Power Pivot功能模块中进行管理
数据导入步骤
- 文件选择：
- 连接类型：选择"连接到Microsoft Excel文件"
- 文件路径：通过浏览按钮选择需要导入的Excel文件
- 导入设置：
- 标题处理：勾选"使用第一行作为列标题"选项
- 表选择：在导入向导中勾选需要导入的具体数据表
- 注意事项：备选数据表可以忽略，只需导入主数据表
导入结果确认
- 导入状态：
- 显示导入操作是否成功完成
- 显示已传输的行数统计信息
- 操作按钮：
- 可随时点击"停止导入"中断操作
- 导入完成后点击"关闭"退出向导
数据表关联
- 关联创建：
- 在"关系图视图"中建立表间关联
- 使用共同字段(如客户ID)作为关联键
- 主从关系：
- 明确主表(如商机记录)和从表(如商机相关企业信息)
- 关联方向应从主表指向从表
数据视图操作
- 视图切换：完成关联后需切换回"数据视图"
- 函数编写：在数据视图中可以开始编写所需的计算函数
- 表格结构：数据视图显示完整的表格结构和字段内容
3、Switch函数的运用
（1）Power Pivot数据视图结构
界面分区: Power Pivot数据视图分为三部分：左侧数据预览区、右侧表达式编辑区和下方表达式编辑区
区域功能区别:
- 右侧区域: 针对左侧数据预览区内某个字段的每一行值进行计算
- 下方区域: 针对上方某个字段创建汇总规则时使用
（2）Switch函数语法与应用
- 基本语法: =SWITCH(表达式, 值1, 结果1, 值2, 结果2, ..., [其他情况结果])
- 参数说明:
- 表达式: 需要进行条件判断的字段
- 值-结果对: 当表达式等于某个值时返回对应的结果
- 其他情况: 可选的默认返回值
- 应用场景: 用于将数值描述替换为文字描述
（3）赢单率文字替换实例
具体实现:
- 表达式: [赢单率]字段（自动生成格式为'表名'[字段名]）
- 替换规则:
- 0.15 → "低"
- 0.25 → "低"
- 0.5 → "中"
- 0.75 → "高"
- 其他情况 → "-"
注意事项:
- 文本结果需要用双引号括起来
- 数值参数直接书写，不加引号
- 默认返回值建议设置，避免出现空值
=SWITCH('商机记录'[赢单率],0.15,"低",0.25,"低",0.5,"中",0.75,"高","-")操作步骤详解
- 编写位置: 应选择数据视图右侧的表达式编辑区
- 字段引用: 通过点击字段自动生成'商机记录'[赢单率]格式
- 结果验证: 回车后立即显示每行数值对应的替换结果
- 列重命名: 双击计算列标题可修改为"赢单率文字替换"
相关知识点
字段引用格式
- 表名用单引号括起
- 字段名用中括号括起
数据类型处理
- 数值型直接书写
- 文本型需加双引号
错误预防
- 建议为所有可能情况设置返回值
- 使用"-"等占位符避免空值
4、Related函数的运用
=RELATED('商机相关企业信息'[有无拖欠还款情况])函数基本用法
- 语法结构: RELATED(列名)，仅需一个参数，即要合并的字段名
- 前提条件: 必须在关系图视图中预先创建好两个表的连接关系
- 功能说明: 将关联表中的指定字段内容匹配到当前表中
实际操作步骤
- 在商机记录表中新建列
- 输入公式=RELATED('商机相关企业信息'[有无拖欠还款情况])
- 将字段重命名为"有无拖欠"
注意事项
- 若无法输入中文，可通过外部复制粘贴或改用拼音/英文替代
- 必须确保两表已建立正确的关系连接
5、Calculate与Filter函数的嵌套使用
- 核心结构: CALCULATE函数嵌套SUM和FILTER，形成CALCULATE(SUM(字段), FILTER(表,条件1), FILTER(表,条件2))的三层结构
- 条件设置要点:
- 高赢单率条件：[赢单率文字替换]="高"
- 低风险条件：[有无拖欠]="无"
- 书写规范: 汇总规则应写在要汇总字段的正下方单元格，保持公式结构的可视化对齐
高赢单率低风险商机金额加总值:=CALCULATE(SUM('商机记录'[商机金额(M)]),FILTER('商机记录','商机记录'[赢单率2]="高"),FILTER('商机记录','商机记录'[欠款情况]="无"))
度量值创建:
- 系统自动生成"度量值1"字段，需手动修改为有意义的名称如"高赢单率低风险商机金额加总值"
- 修改时保留冒号及后续内容，仅修改冒号前的字段名部分
常用汇总函数:
- 加总：SUM(字段)
- 平均：AVERAGE(字段)
- 计数：COUNT(字段)
- 极值：MAX(字段)/MIN(字段)
6、计算总商机金额与百分比
- 分母计算: 直接使用SUM([商机金额])计算所有商机金额总和
- 百分比公式: = [高赢单率低风险商机金额加总值] / [总商机金额]
- 命名规范: 百分比度量值建议命名为"高赢单率低风险商机金额百分比"
- 应用说明: Power Pivot编辑器中的计算规则需应用到数据透视表才能显示实际效果，编辑器仅用于定义计算逻辑
7、创建数据透视表
字段布局：将销售人员字段拖拽到行标签区域，将高赢单率低风险百分比字段放入值区域
显示问题：默认显示为小数形式而非百分比，需要后续调整格式
返回Power Pivot界面，在"主页"选项卡下找到"格式"设置，将数据类型从"常规"改为"百分比"
验证方法：可将加总值和总商机值同时拖入透视表进行交叉验证
感叹：普通数据透视表难以实现此类复杂汇总规则
二、知识小结
| 知识点 | 核心内容 | 考试重点/易混淆点 | 难度系数 |
| DAX表达式概述 | Power Pivot特有函数集，含250+函数，语法类似Excel函数 | 区分表名(单引号)和字段名(中括号)的引用方式 | ⭐⭐ |
| 函数特性 | 1. 结果应用于数据透视表; 2. 作用于整列/所有行; 3. 函数名不区分大小写 | 与Power Query的M函数区分（M函数区分大小写） | ⭐⭐ |
| RELATED函数 | 跨表关联字段（需预先建立表关系） | 比VLOOKUP效率更高，适合大数据量 | ⭐⭐⭐ |
| SWITCH函数 | 多条件值替换：SWITCH(表达式,值1,结果1,值2,结果2,...,其他结果) | 替代复杂的IF嵌套，参数成对出现 | ⭐⭐⭐ |
| CALCULATE函数 | 核心筛选函数：CALCULATE(计算规则,筛选条件1,筛选条件2...) | 必须配合SUM等聚合函数使用 | ⭐⭐⭐⭐ |
| FILTER函数 | 条件筛选：FILTER(表,筛选条件) | 常作为CALCULATE的筛选参数 | ⭐⭐⭐⭐ |
| 实战案例 | 计算高赢单率(≥0.75)+低风险(无拖欠)商机占比： 1. 用SWITCH转换赢单率为文本; 2. 用RELATED关联风险字段; 3. 嵌套CALCULATE+FILTER计算条件求和; 4. 创建百分比度量值 | 关键公式： CALCULATE(SUM([金额]), FILTER(表,[赢单率]="高"), FILTER(表,[风险]="无")) | ⭐⭐⭐⭐ |
| 函数分类 | 1. 行级计算函数; 2. 聚合计算函数; 3. 特殊筛选器函数（FILTER/ALL等） | 筛选器类函数是DAX特有功能 | ⭐⭐ |