Power Pivot 系列 (4) - DAX 查询

2024-02-05 13:32
文章标签 查询 系列 power pivot dax

本文主要是介绍Power Pivot 系列 (4) - DAX 查询,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

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

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

查询表的所有数据

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

-- 查询 Sales 表所有数据
EVALUATE Sales


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

字段排序

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

-- 按 Order Date字段排序
EVALUATE
Sales ORDER BY [Order Date] ASC

DAX 支持按多个字段排序:

EVALUATE
Sales
ORDER BY[Order Date] ASC,[CustomerKey] ASC

选择指定字段

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

EVALUATE
SUMMARIZE ( 'Sales', [ProductKey], [OrderDateKey], [Quantity], [Unit Price] )

DAX 查询结果的界面:

数据筛选

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

EVALUATE
FILTER ( 'Product', Product[Class] = "Economy")

分组计算

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

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

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

EVALUATE
SUMMARIZE ( Sales, [CustomerKey], "Total Sales", SUM ( Sales[Quantity] ) )

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

EVALUATE
SUMMARIZE (Sales,[CustomerKey],[ProductKey],"Total Quantity", SUM ( Sales[Quantity] )
)
ORDER BY [CustomerKey]

基于多表的操作

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

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

EVALUATE
SUMMARIZE (Sales,Customer[Company Name],'Product'[Product Name],Sales[Quantity]
)

查询结果截图如下:

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

EVALUATE
SUMMARIZE (Sales,Customer[Company Name],'Product'[Product Name],"Total Sales", SUM ( Sales[Quantity] )
)

查询的截图如下:


数据筛选也能自由地使用其他表的字段。比如下面的示例,查询 Sales 表,但筛选条件是 Product 的 Brand 为 Litware。需要用到 RELATED 函数:

EVALUATE
FILTER ( Sales, RELATED ( Product[Brand] ) = "Litware" )

基于多表筛选且选择指定字段

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

EVALUATE
FILTER (SUMMARIZE (sales,Customer[Country],Product[Brand],"Total Quantity", SUM ( Sales[Quantity] )),[Brand] = "Contoso"
)

添加列

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

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

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

EVALUATE
ADDCOLUMNS ( Sales, "Line Prcie", Sales[Quantity] * Sales[Unit Price] )

定义变量

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

DEFINEVAR groupedSales =SUMMARIZE (Sales,Customer[Country],'Product'[Brand],"Total Quantity", SUM ( Sales[Quantity] ))
EVALUATE
FILTER ( groupedSales, [Brand] = "Contoso" )

在 DAX 查询中定义度量值

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

DEFINEMEASURE Sales[salesamt] =SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )
EVALUATE
ADDCOLUMNS ( VALUES ( 'Product'[Brand] ), "Total Sales", 'Sales'[salesamt] )

示例数据

github - sample data

参考

  • 理解EVALUATE语法
  • Using DAX to retrieve tabular data

这篇关于Power Pivot 系列 (4) - DAX 查询的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/681065

相关文章

浅谈mysql的sql_mode可能会限制你的查询

《浅谈mysql的sql_mode可能会限制你的查询》本文主要介绍了浅谈mysql的sql_mode可能会限制你的查询,这个问题主要说明的是,我们写的sql查询语句违背了聚合函数groupby的规则... 目录场景:问题描述原因分析:解决方案:第一种:修改后,只有当前生效,若是mysql服务重启,就会失效;

MySQL多列IN查询的实现

《MySQL多列IN查询的实现》多列IN查询是一种强大的筛选工具,它允许通过多字段组合快速过滤数据,本文主要介绍了MySQL多列IN查询的实现,具有一定的参考价值,感兴趣的可以了解一下... 目录一、基础语法:多列 IN 的两种写法1. 直接值列表2. 子查询二、对比传统 OR 的写法三、性能分析与优化1.

mybatis-plus 实现查询表名动态修改的示例代码

《mybatis-plus实现查询表名动态修改的示例代码》通过MyBatis-Plus实现表名的动态替换,根据配置或入参选择不同的表,本文主要介绍了mybatis-plus实现查询表名动态修改的示... 目录实现数据库初始化依赖包配置读取类设置 myBATis-plus 插件测试通过 mybatis-plu

MySQL中实现多表查询的操作方法(配sql+实操图+案例巩固 通俗易懂版)

《MySQL中实现多表查询的操作方法(配sql+实操图+案例巩固通俗易懂版)》本文主要讲解了MySQL中的多表查询,包括子查询、笛卡尔积、自连接、多表查询的实现方法以及多列子查询等,通过实际例子和操... 目录复合查询1. 回顾查询基本操作group by 分组having1. 显示部门号为10的部门名,员

mysql关联查询速度慢的问题及解决

《mysql关联查询速度慢的问题及解决》:本文主要介绍mysql关联查询速度慢的问题及解决方案,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql关联查询速度慢1. 记录原因1.1 在一次线上的服务中1.2 最终发现2. 解决方案3. 具体操作总结mysql

mysql线上查询之前要性能调优的技巧及示例

《mysql线上查询之前要性能调优的技巧及示例》文章介绍了查询优化的几种方法,包括使用索引、避免不必要的列和行、有效的JOIN策略、子查询和派生表的优化、查询提示和优化器提示等,这些方法可以帮助提高数... 目录避免不必要的列和行使用有效的JOIN策略使用子查询和派生表时要小心使用查询提示和优化器提示其他常

SQL 中多表查询的常见连接方式详解

《SQL中多表查询的常见连接方式详解》本文介绍SQL中多表查询的常见连接方式,包括内连接(INNERJOIN)、左连接(LEFTJOIN)、右连接(RIGHTJOIN)、全外连接(FULLOUTER... 目录一、连接类型图表(ASCII 形式)二、前置代码(创建示例表)三、连接方式代码示例1. 内连接(I

轻松上手MYSQL之JSON函数实现高效数据查询与操作

《轻松上手MYSQL之JSON函数实现高效数据查询与操作》:本文主要介绍轻松上手MYSQL之JSON函数实现高效数据查询与操作的相关资料,MySQL提供了多个JSON函数,用于处理和查询JSON数... 目录一、jsON_EXTRACT 提取指定数据二、JSON_UNQUOTE 取消双引号三、JSON_KE

查询SQL Server数据库服务器IP地址的多种有效方法

《查询SQLServer数据库服务器IP地址的多种有效方法》作为数据库管理员或开发人员,了解如何查询SQLServer数据库服务器的IP地址是一项重要技能,本文将介绍几种简单而有效的方法,帮助你轻松... 目录使用T-SQL查询方法1:使用系统函数方法2:使用系统视图使用SQL Server Configu

MYSQL关联关系查询方式

《MYSQL关联关系查询方式》文章详细介绍了MySQL中如何使用内连接和左外连接进行表的关联查询,并展示了如何选择列和使用别名,文章还提供了一些关于查询优化的建议,并鼓励读者参考和支持脚本之家... 目录mysql关联关系查询关联关系查询这个查询做了以下几件事MySQL自关联查询总结MYSQL关联关系查询