从Excel到Power BI:数据建模核心DAX公式全攻略
- 2026-04-23 02:15:17
还在为大宽表查询慢、数据冗余头疼？还在用Excel公式做复杂计算？本文带你系统掌握Power BI数据建模的核心DAX公式，从ALL系列到RANKX排名，一份指南搞定！
一、为什么必须学数据建模？
在Power BI的数据分析世界里，数据建模是连接"原始数据"与"业务洞察"的关键桥梁。它的核心功能可以归纳为四大价值：
| 整合数据源 | |
| 优化数据查询 | |
| 数据建模可视化 | |
| 更低学习成本 |
💡 一句话总结：数据建模让你的数据从"散"到"聚"、从"慢"到"快"、从"看不懂"到"一目了然"。
二、数据建模的KPI指标计算
2.1 关系建模
通过建立表与表之间的关系（一对多、多对一等），实现数据的关联查询，避免大宽表的冗余和低效。
2.2 DAX公式
通过DAX（Data Analysis Expressions）公式创建度量值、新建表以及新建列，在不同上下文环境中完成数据查询与运算。
从业务需求出发，逐步完成DAX公式，同时完成业务经营逻辑的诠释。
2.3 低代码创建字段
通过DAX公式创建度量值、新建表以及新建列，以类似Excel公式的结构完成数据查询与运算。
三、核心DAX公式详解
🔹 3.1 ALL系列 — 取消筛选的利器
ALL系列函数是DAX中最常用的筛选控制函数，它们能灵活地取消外部筛选上下文，是度量值计算中的"万能钥匙"。
函数一览
| ALL | ALL('表')ALL('表'[列]) | |
| ALLEXCEPT | ALLEXCEPT('表', '事实表'[列], '维度表'[列], ...) | |
| ALLSELECTED | ALLSELECTED('表') |
典型用法
-- 取消所有外部筛选，计算整体占比CALCULATE([基础度量值], ALL('SA_销售记录'))-- 仅保留指定列筛选ALLEXCEPT('表', '事实表'[列], '维度表'[列])-- 保留切片器筛选，取消其他ALLSELECTED('表')⚡ 实战场景：当你需要计算"占总计的百分比"时，ALL函数是不可或缺的搭档——用ALL取消产品筛选，即可算出每个产品占全品类的比重。
🔹 3.2 SELECTEDVALUE — 单值筛选的优雅解法
定义
在指定列只有一个值时返回该值，否则返回替代结果。
语法
SELECTEDVALUE('表'[列], 替代值)核心逻辑
SELECTEDVALUE 是 IF + HASONEVALUE + VALUES 的等价公式，写法更简洁。
-- 等价写法对比SELECTEDVALUE = SELECTEDVALUE('SA_销售记录'[产品名称])-- 传统写法IF(HASONEVALUE('SA_销售记录'[产品名称]), VALUES('SA_销售记录'[产品名称]), BLANK())进阶应用：动态筛选度量值
SELECTEDVALUE 动态筛选度量值 = VAR SHAIXUAN = SELECTEDVALUE('3.3 SELECTEDVALUE 匿名表筛选度量值'[业务指标])RETURN SWITCH( TRUE(), SHAIXUAN = "销售额", CALCULATE(SUM('更新清单'[销售金额])), SHAIXUAN = "成本", CALCULATE(SUM('更新清单'[产品成本])), SHAIXUAN = "成本率", '更新清单'[产品成本] / '更新清单'[销售金额] )💡 技巧：SELECTEDVALUE拥有VALUES的筛选上下文特性，可以用A列做SELECTEDVALUE，去判断B列值的筛选上下文结果，实现跨列联动！
🔹 3.3 CONCATENATEX — 字符串拼接神器
定义
计算表上每个行的表达式，然后用单个字符串结果返回这些值的串接，值之间用指定的分隔符分隔。
语法
CONCATENATEX('表', 串接的表列, 分隔符, 排序表达式, 升序ASC/降序DESC)三种经典用法
-- 用法1：基础拼接，用顿号分隔CONCATENATEX 销售大区 = CONCATENATEX(VALUES('SA_销售记录'[销售大区]), 'SA_销售记录'[销售大区], "、")-- 用法2：TOP N 拼接，只取前3名CONCATENATEX 销售大区 TOP3 = VAR TOP3 = CALCULATETABLE( VALUES('SA_销售记录'[销售大区]), TOPN(3, ALL('SA_销售记录'[销售大区]), [基础度量值], DESC))RETURN CONCATENATEX(TOP3, 'SA_销售记录'[销售大区], "、")🎯 应用场景：在报表中展示"销售额前三的大区"，不用做表，一行文字搞定！
🔹 3.4 FORMAT — 数值格式化专家
定义
将值转换为采用指定数值格式的文本。
语法
FORMAT(表达式, 格式类型, 区域选择)常用格式速查表
FORMAT(12345.67, "Currency") | ||
FORMAT(12345.67, "Fixed") | ||
FORMAT(12345.67, "Standard") | ||
FORMAT(12345.67, "Percent") | ||
FORMAT(12345.67, "Scientific") |
自定义格式写法
-- 货币简写FORMAT(12345.67, "¥#,##0.00")-- 定点小数简写FORMAT(12345.67, "0.0")-- 标准简写FORMAT(12345.67, "#,##0.00")📌 注意：第三个参数"区域选择"一般省略即可。
🔹 3.5 DATATABLE — 内联建表术
定义
返回具有以内联方式定义的数据的表。
语法
DATATABLE( "列名1", 列1格式, "列名2", 列2格式, { {"列1的第一行", "列2的第一行"}, {"列1的第二行", "列2的第二行"} })支持的列格式代码
STRING | |
DATETIME | |
INTEGER | |
BOOLEAN | |
CURRENCY | |
DOUBLE |
实战示例：新建产品信息表
DATATABLE 新建匿名表 = DATATABLE( "产品名称", STRING, "上架时间", DATETIME, "批次", STRING, "数量", INTEGER, "布尔值", BOOLEAN, "货币", CURRENCY, "小数", DOUBLE, { {"产品A", "2024-01-15", "B001", 100, TRUE(), 5000.00, 3.14} })💡 里层每组
{}对应一行的数据，每一行数据需要对应列的数量与格式，外层的{}为整个列表的汇总符号。
🔹 3.6 HASONEVALUE — 单值判断守门员
定义
判断指定列在当前筛选上下文中是否仅有一个值。
语法
HASONEVALUE('表'[列])典型用法：控制排名显示
HASONEVALUE 判断当前行排名 = SWITCH( TRUE(), HASONEVALUE('RS_日期表'[年度]), RANKX(ALL('RS_日期表'[年度]), [1.0 基础度量值]), HASONEVALUE('SA_销售记录'[销售大区]), RANKX(ALL('SA_销售记录'[销售大区]), [1.0 基础度量值]), HASONEVALUE('SA_销售记录'[产品名称]), RANKX(ALL('SA_销售记录'[产品名称]), [1.0 基础度量值]), 1)HASONEVALUE 与 SELECTEDVALUE 对比
-- HASONEVALUE 写法IF(HASONEVALUE('SA_销售记录'[产品名称]), [1.0 基础度量值], BLANK())-- SELECTEDVALUE 等价写法（更简洁）SELECTEDVALUE('SA_销售记录'[产品名称])🔹 3.7 RANKX — 排名之王
定义
为表中的每一行计算排名值。
语法
RANKX(表, 表达式, [值], [排序方式], [排名方式])参数说明
DESC0/FALSE 为降序，ASC/1/TRUE 为升序 | ||
SKIPDENSE 为连续排名（1,2,2,3） |
⚠️ 第3、4、5参数均可忽略，若仅忽略部分参数，中间需要用
,占位。
六大排名公式模板
-- 1️⃣ 常规排名RANKX 常规排名方法 = RANKX(ALL('SA_销售记录'[产品名称]), [基础度量值])-- 2️⃣ A度量值在B度量值中排名RANKX A度量值在B度量值中进行排名 = RANKX(ALL('SA_销售记录'[产品名称]), [基础度量值], [基础度量值] * 3)-- 3️⃣ 相对排名（仅对选中项排名）RANKX 相对排名 = RANKX(ALLSELECTED('SA_销售记录'[产品名称]), [基础度量值])-- 4️⃣ 完整排名公式（升序+连续排名）RANKX 完整排名公式 升序 连续排名 = RANKX(ALL('SA_销售记录'[产品名称]), [基础度量值], [基础度量值], ASC, Dense)-- 5️⃣ 公式内基础度量值RANKX 公式内基础度量值 = RANKX(ALL('SA_销售记录'[产品名称]), CALCULATE(SUM('SA_销售记录'[销售金额])))-- 6️⃣ 去除总计行的排名RANKX 去除总计排名 = IF(HASONEVALUE('SA_销售记录'[产品名称]), [RANKX 常规排名方法], BLANK())🔹 3.8 ISFILTERED / ISCROSSFILTERED — 筛选状态侦探
定义
| ISFILTERED | |
| ISCROSSFILTERED |
语法
ISFILTERED('表'[列])ISCROSSFILTERED('表'[列])实战示例
-- 判断产品名称是否被直接筛选ISFILTERED 判断产品名称是否筛选 = ISFILTERED('SA_销售记录'[产品名称])-- 判断销售大区是否被直接筛选ISFILTERD 判断销售大区是否筛选 = ISFILTERED('SA_销售记录'[销售大区])-- 判断产品名称是否被交叉筛选ISCROSSFILTERD 产品名称 = ISCROSSFILTERED('SA_销售记录'[产品名称])-- 判断销售大区是否被交叉筛选ISCROSSFILTERD 销售大区 = ISCROSSFILTERED('SA_销售记录'[销售大区])💡 何时使用：当你需要判断某个字段是"被直接筛选"还是"被交叉筛选影响"时，这两个函数能帮你精确识别筛选上下文的来源。
🔹 3.9 TREATAS — 虚拟关系建立者
定义
将一个表表达式中的列作为另一个表中的列的筛选条件，建立虚拟关系。
语法
TREATAS(表表达式, '目标表'[目标列1], '目标表'[目标列2], ...)实战：跨表筛选
-- 用新建的匿名表筛选销售记录TREATAS 产品名称 新建表 = TREATAS( VALUES('3.11 TREATAS 产品名称 新建表'[产品名称]), 'SA_销售记录'[产品名称])🔹 3.10 UNION — 表的合并
定义
将多个表纵向合并为一个表。
核心特性
🔑 UNION可完成类增量刷新的功能！原理在于DAX新建表是在需要调用表时进行代码刷新，即只要物理表刷新进去后，代码会根据DAX调用情况实时刷新。
语法
UNION(表1, 表2, ...)实战：历史+更新数据合并
UNION 合并表 = VAR GENGXIN = SELECTCOLUMNS( '更新清单', '更新清单'[产品名称], '更新清单'[销售大区], '更新清单'[销售金额], '更新清单'[产品成本])VAR LISHI = SELECTCOLUMNS( '历史清单', '历史清单'[产品名称], '历史清单'[销售大区], '历史清单'[销售金额], '历史清单'[产品成本])RETURN UNION(GENGXIN, LISHI)🔹 3.11 CROSSJOIN — 笛卡尔积生成器
定义
返回两个表所有行的笛卡尔积（全排列组合）。
语法
CROSSJOIN(表1, 表2, ...)实战：生成所有组合
-- 产品名称 × 销售大区CROSSJOIN 产品名称+销售大区 = CROSSJOIN('3.11 TREATAS 产品名称 新建表', '3.11 TREATAS 销售大区 新建表')-- 产品名称 × 销售大区 × 年度（带筛选）CROSSJOIN 产品名称+销售大区+2015/2016年度 = CROSSJOIN( '3.11 TREATAS 产品名称 新建表', '3.11 TREATAS 销售大区 新建表', FILTER(VALUES('RS_日期表'[年度]), [年度] = 2015 || [年度] = 2016))写在最后
数据建模不是"会写公式"就够了，而是要理解每一个公式背后的业务逻辑。从ALL系列控制筛选上下文，到RANKX实现多维排名，再到TREATAS/UNION/CROSSJOIN实现灵活的数据组合——这些工具的真正力量，在于帮助你从"数据搬运工"进化为"业务分析师"。
