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

相关文章

Pandas透视表(Pivot Table)的具体使用

《Pandas透视表(PivotTable)的具体使用》透视表用于在数据分析和处理过程中进行数据重塑和汇总,本文就来介绍一下Pandas透视表(PivotTable)的具体使用,感兴趣的可以了解一下... 目录前言什么是透视表?使用步骤1. 引入必要的库2. 读取数据3. 创建透视表4. 查看透视表总结前言

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》:本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J

Go语言开发实现查询IP信息的MCP服务器

《Go语言开发实现查询IP信息的MCP服务器》随着MCP的快速普及和广泛应用,MCP服务器也层出不穷,本文将详细介绍如何在Go语言中使用go-mcp库来开发一个查询IP信息的MCP... 目录前言mcp-ip-geo 服务器目录结构说明查询 IP 信息功能实现工具实现工具管理查询单个 IP 信息工具的实现服

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

SQL表间关联查询实例详解

《SQL表间关联查询实例详解》本文主要讲解SQL语句中常用的表间关联查询方式,包括:左连接(leftjoin)、右连接(rightjoin)、全连接(fulljoin)、内连接(innerjoin)、... 目录简介样例准备左外连接右外连接全外连接内连接交叉连接自然连接简介本文主要讲解SQL语句中常用的表

MySQL高级查询之JOIN、子查询、窗口函数实际案例

《MySQL高级查询之JOIN、子查询、窗口函数实际案例》:本文主要介绍MySQL高级查询之JOIN、子查询、窗口函数实际案例的相关资料,JOIN用于多表关联查询,子查询用于数据筛选和过滤,窗口函... 目录前言1. JOIN(连接查询)1.1 内连接(INNER JOIN)1.2 左连接(LEFT JOI

MySQL 中查询 VARCHAR 类型 JSON 数据的问题记录

《MySQL中查询VARCHAR类型JSON数据的问题记录》在数据库设计中,有时我们会将JSON数据存储在VARCHAR或TEXT类型字段中,本文将详细介绍如何在MySQL中有效查询存储为V... 目录一、问题背景二、mysql jsON 函数2.1 常用 JSON 函数三、查询示例3.1 基本查询3.2

MySQL中的交叉连接、自然连接和内连接查询详解

《MySQL中的交叉连接、自然连接和内连接查询详解》:本文主要介绍MySQL中的交叉连接、自然连接和内连接查询,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、引入二、交php叉连接(cross join)三、自然连接(naturalandroid join)四

mysql的基础语句和外键查询及其语句详解(推荐)

《mysql的基础语句和外键查询及其语句详解(推荐)》:本文主要介绍mysql的基础语句和外键查询及其语句详解(推荐),本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋... 目录一、mysql 基础语句1. 数据库操作 创建数据库2. 表操作 创建表3. CRUD 操作二、外键

Mybatis 传参与排序模糊查询功能实现

《Mybatis传参与排序模糊查询功能实现》:本文主要介绍Mybatis传参与排序模糊查询功能实现,本文通过实例代码给大家介绍的非常详细,感兴趣的朋友跟随小编一起看看吧... 目录一、#{ }和${ }传参的区别二、排序三、like查询四、数据库连接池五、mysql 开发企业规范一、#{ }和${ }传参的