《POWERBI数据处理和分析》课件 第7章. Power Pivot之DAX语言基础.pdf

弟7早Power Pivot之DAX语言基础章节内容 7.1 DAX语言基础,7.1.1 PowerPivot 和 DAX 7.1.2 DAX公式形式 7.1.3DAX公式引用对象命名规则 7.L4DAX数据类型 7.1.5 DAX运算符 7.2 DAX计算基础 721度量值 722计算歹（J 723计算表 7.3 DAX库函数概述27.1.1 PowerPivot 和 DAX Power Pivot是EXCEL和Power Bl中实现数据分析的核心组件。在Power Bl中，主要是通过“建模”选项卡使用Power Pivot的功能。Power Pivot进行数据建模分析和计算所使用的主要语言是DAX,建模和 分析计算的结果则需要结合报表和可视化对象展示出来。DAX是Data Analysis eXpressions的意思，即用于数据分析的表达式语言。最早于2010在EXCEL 2010中随Power Pivot一起发布。目前在EXCEL 2010 及之后版本、POWER Bl和Microsoft SQL SERVER中都支持彳吏用DAX语言 进行数据分析，但是不同软件中使用的DAX语言略有差别。37.1.2 DAX公式形式 DAX公式定义和应用案例 2000年后每平米均价=4AI-圆S普13因&始区h et囱 E a炉it曲1 凶mG。酊 金&0回四 由司SI R叩E Q 3 口 JZ -昌7CALCULATE(AVERAGE(houseunit_price),housebuildingyear2000)先从house表按照buildin等ear列属性的值2000作为条件对数据进行筛 选，对得到的数据再计算unijprice的平均值。该DAX公式被用到柱形图中时，Power B根据选中的横轴属性“equipment”,对于每种蓑修情况的属性莅又征了一次数射筛选，然 后对每种装修情况的数据再动态的执行DAX公式的计算，从而得到了 按装修情况分类的2000年以后的二手房每平米价格并展示在柱形图中,达到计算后通过可视化对比进行分析的效果。47.1.2 DAX公式形式 DAX公式按照以下规则构建：以等号“二开始；由标量运算符、标量常量和DAX函数等语法单位构成，如上例中的运算 符，整型常量2000,DAX库函数CALCULATE和AVERAGE等；除了标量常量外，公式中所引用的操作数都是来自于数据模型中表对象 或表的列对象，如上例中的houseunit_price和houesbuildingyear；按照作用不同，DAX公式可能返回一日标量值、表中的一个列或者一个 表对象；DAX公式用于构建度量值、计算列、计算表等计算对象，因此在等号“二 左边可以给这些DAX公式对象命名后保存。57.1.3 DAX公式弓|用对象命名规则在DAX公式中会引用表对象名、属性列名、公式对象名等，这些 被引用的对象名由UNICODE字符构成，但是需要遵循一定的语法 规则，包括：表名和列名中如果不包括空格和“./：/|?&%$!+=()等特殊字符,则 可以直接使用，否则需要使用单引号括起来，例如suppose ratio。当引用列名或计算列、度量值这样的公式对象时，需要使用方括号括起 来，例如2000年后每平米均价;当引用列名或计算列、度量值这样的公式对象时，需要使用限定名称形 式，即“表名对象名的形式，有些函数中支持隐式限定，例如在同一 张表上下文中计算时，可以直接使用列名形式，但是有些函数要求使用 显式的完全限定名，例如houseunit_price。67.1.4 DAX数据类型 DAX数据类型主要包括整数、实数、逻辑、字符串、日期/时间、货币等，此 外还有针对不可处理类型的空白类型，以及并不会针对最终计算结果，而是 作为很多DAX库函数的参数类型的表数据类型。DAX数据类型M语言中对应数据类型解释INTEGER64位整数Number类型表示整数，可用于度量值、计算列、DAX函 数中整数参数的数据类型DOUBLE64位实数Number类型表示实数，可用于度量值、计算列、DAX函 数中实数参数的数据类型BOOLEAN 逻辑类型逻辑类型只有true和false两个取值，可以作为DAX函 数中表达条件的参数的数据类型STRING 字符串类型文本类型表示文本数据，可用于度量值、计算列、DAX函数中文本参数的数据类型CURRENCY 货币类型Number类型表示货币类型数据空白类型NULL类型等不能表示为其他DAX数据类型的情况统一用 空白类型表达表数据类型表格数据类型用于返回表对象的DAX函数，以及DAX函数 中表对象参数的数据类型7.1.5 DAX运算符 DAX中的运算符只用于构造度量值、计算列等DAX公式时表示所需的计算功能，包 含算术运算符、关系运算符、文本连接运算符和逻辑运算符等四类运算符。类别运算符解释优先级示例圆括号0提升圆括号里运算符 的优先级1(1+2)*3舁术运舁 符A乘塞运算22八3-单目负号3-1*、/双目乘除43*5单目逻辑 运算符!逻辑非5!false舁术运舁 符+、-双目加减61+2文本连接 运算符&文本连接运算7“hi&”world!关系运算 符二、二二、二、=关系运算853双目逻辑 运算符&逻辑与运算953&31II逻辑或运异1053|317.2 DAX计算基础1度量值计算列计算表M X rooms 臼halls 臼toilets 三area?!二direction22179.1南北明22188.4南北22197.3南北22191.3南北22188.8南北22187.6南北21178.2南北22184.3南北22185.3南北221 83.6南北22193.4南北22188.5南北22189南北househeight 二buildingheight 二equipment 臼buildingyear g blockjd 日metro$tation_id 曰 sale_date 三unit_price|27精装2010BKO23M004 2020年3 月 10 日2290077简装2007BK230M004 2020年4 月，3 日1780047简装2006BK230M004 2019 年9 月 11 日1910037精装2006BK060M004 2019%月 19 日2210047精装2009BK230M004 2020钠月28 日1960077中装2008BK230M004 2020年4 月21 日2010037简装2011BK023M004 2020弗月 14 日2300047中装2009BK060M004 202。用月9日1970027精装2010BK715M004 202。年明3日2040027简装2010BK004M004 2020筑月4日2190047中装2008BK230M004 2019 年11 月 14 日2080067楮装2006BK230M004 2019 年10 月 19 日1970057精装2006BKOO7M004 2019 年 11 月26 日1900097.2.1度量值 度量值是指从数据分析角度出发，在数据集上根据业务逻辑进行计算 得到分析指标值的动态DAX计算公式，最终的计算结果是一个标量值。在标量值公式中通常通过引用数据模型中的表属性表示分析计算所针 对的数据集，而所构建的DAX公式表示分析计算的方法。度量值是数据分析中所需要的基本计算单元，需要结合报表中的视觉 对象才能达到数据分析的目标。度量值的动态性体现在用DAX计算公式只定义了计算的方法，而公式 中的属性和与视觉对象相关的筛选器决定了计算真正基于的数据集。度量值可以起名，一个已命名的度量值又可以作为参数应用到更复杂 的度量值计算中。107.2.1度量值在Power Bl的“数据视图”的“表工具”或“报表视图”的“建 模”选项卡中，点击“新建度量值”按钮可以创建度量值；也可以在“数据视图”中选择某个数据表单击右键，在右键菜单 中选择“新建度量值”实现度量值的创建。文件 主页帮助外部工具表工具0名称house|如 始标记为日期表管理关系V结构 日历 关系I 1 HS 新建度量值快度量值新建列新建表_ 计算117.2.1度量值.度量值定义案例选中house表，构造一个计算二手房销售每平米均价的度量值，可以在单 击“新建度量值”按钮后在新建度量值的DAX公式编辑框中输入如下DAX 公式并按回车键：每平米均价=AVERAGE(，house，unit_price)0/twim|1.别隔爽 口 国 福而 -.$.%V3，2小”，心X/1 电单*VTRA6(houunit_prlc)room-j hrfh-loilata a2 1 dwionequamnlboSdinvrra,nnUtion_iric.I*I I 1m aut2，2010MOOImo初月io日2 2 1884 SJtr，武装ZOQJMOOIto”心月 13s2 2 197,3*lt4，商整2006MOOI20190911 a2 2 191.33播装2006M0 a南甘AMOOS2Q2OS4ff8B字段 200闺价Z n2biock.idZ buOdlnQheightX buildingywequpmentZ hou，ha9htZ no由3.&E toilM*uMpnte口好咖卿令麦houw 0 圜 sie.date O Z toilets Z unt_pnc B每平米均价 S metrostatton.gbS 1 E H新建度质值快度重值 标津列新拉表在以下0不会怎么第1页供1 55）饵理角色通过以下身饰台:mA向答没!iSS iS言架构V箍选器a搜索此视觉对fUL的稀选赛200际后短平米均价可视化工ft提示在此棘 block S distarKe.metrobkxk7 8 house*2000年后每平米.嚼V&2 T领 s zz z ZZ137.2.2计算列计算列是指出于数据分析的目标需要，在数据模型的当前表中,添加根据已有属性列的数据通过DAX公式计算得到的新属性列。计算列和度量值的区别主要有以下两点：度量值是基于数据表中某个属性列的所有行数据进行计算，最终得到一 个标量值；计算列是对表中的每一行数据分别计算并得到一个值，最终 所有行的计算结果构成一个新的属性列。度量值存储时不存储计算结果，只存储DAX公式，当与视觉对象结合进 行数据分析时才进行计算；计算列不但存储DAX公式，还会计算并将计 算结果保留在内存中，并且可以根据需要通过刷新重新计算。147.2.2计算歹U在Power Bl的“数据视图”的“表工具”或“报表视图”的“建 模”选项卡中点击“新建列”按钮可以创建计算列；也可以在“数据视图”中选择某个数据表并单击右键，在右键菜 单中选择“新建列”实现计算列的创建。文件 主页帮助外部工具表工具 度量工具标记为日期表 管理关系 新建度量值快度量值新建列新建表结构 日历 关系 计算157.2.2计算歹U计算列定义案例为house表创建一个表示房型的文本类型计算列，可以在单击“新建列”按钮后在 新建列的DAX公式编辑框中输入以下DAX公式并按回车键：房型=houserooms&房&househalls&厅&housetoilets&“卫metrostation_id文件名称主页 帮助house构外部工具 表工具1 B 嚏标记为日期表 管理关系历 关系列工具ffl ffl HH新建度最值快度至值新建列新建表幽 X/1房型=house-rooms&房&househalls&,厅”&housetoilets&“卫字段 toilets 二 area2direction 二|househeight 二buildingheight 匚Jequipment 二buildingyear 口 block_id 已metrostation_id 二 sale_dateunit_prke|习 用 sale.date 19121 97.3南北 47筒装2006BK23OM004 2019911321 91.3南北 37靖装2006BK060M004 20199190221.Z tUets21 88.8南北 47精装2009BK23OM004 2020 取月28 日196 X unit.price21 87.6南北 77中装2008BK23OM004 2020年4 月21 日201 孱房型11 78.2南北 37简装2011BKO23M004 2020 浜月 14 日23。g每平米均价21 84.3南北 47中装2009BK060M004 2020寺月9日297 盘 metrostation gb21 85.3南北 27精装2010BK715M004 2020豕月3日20421 83.6南北 27简装2010BK004M004 20203 月4 日21”167.2.3计算表源、处据 馥”象 W对 PO表 过化 通构 而的，析 刖分 之据，这称为POWER Bl中DAO在霰需计算表对象的来源和用途可能有以下几种场景:褊房复制某个表生成。例如某张数据表的源表用于数据计算，复制表用于数据.打现曜嬲磊瞿髓毂整父足口槎罂史现有表对象的数据，完全用DAX公式生成一张新表。例如用17723计算表添加计算表的操作可以通过Power Bl数据视图”的“表工具”或“报表视图”的“建模”选项卡中点击“新表”按钮实现。文件 主页帮助0名称house外部工具表工具 列工具 S 唱 H H 0标记为日期表管理关系新建度量值快度量值新建歹!新建表结构日历 关系 计算18723计算表计算表定义案例为房价可能的涨跌比例用DAX公式新建一个假设的涨跌率表，则可以单击 Power Bl建模”选项卡中“新表”按钮后在新表的DAX公式编辑框中输入以 下DAX公式并按回车键：suppose_ratio=DataTable(ratio,DOUBLE/-0.1,-0.05,0.05,0.10,0.20)目夕 Ql house-Power Bl Desktop H录 X文件 主页 帮助 外部工具 表工具 名称(suppose.rabo fit OH7记为日期表 管理关系 新建度显值快度星值新建列新建表结为 日历 关浜 计算帆 X 5】suppose_ratio-OataTableCratio,DOUBLE,-0.1,(-0.05),伊.05,0.10,0.2。字段|q搜索 剧 block S di$tonc_mtroblock 髭 house 网 rretro$tation_gb随 suppose.ratioZ ratio表:supposejobo（5 行）197.3 DAX库函数概述Power Pivot中内置了超过200个DAX库函数。准备好数据分析所需要的数据，这是通过筛选函数来实现的;完成分析指标的计算任务 支持函数完成错误处理等任务。207.3 DAX库函数概述 1.聚合函数聚合函数通常对表中的所有行聚合统计得到一个标量值函数解释可能的应用场景AVERAGE()统计列column中包含的数值的算术平均值。计算平均工资，计算房屋每 平米均价。A VE R AG EX(,)对表table的每一行使用expression计算得到一个值,再计算每行计算结果的平均值。计算由工资和奖金列构成的 每个人总收入的平均值COUNT()统计列column中包含的数值型、日期型和字符串 薮据的个数统计销售订单数COUNTBLANK()统计列column中包含的空白单元格的个数统计缺失的数据数PRODUCT(Tablecolumn)计算表格中某列数值的乘积计算按年度的累积投资收益PRODUCTX(,)先用expression对表table中的每一行数据做计算得 到一个值，再计算这些值的乘积作为返回值。计算按年度的累积投资收益SUM()计算列column中包含的数值的和计算累积销售量SUMX(,)先用expression对表table中的每一行数据做计算得 到一个值，再计算这些值的和作为返回值。计算累积销售额，每行由数 量乘以单价得到。7.3 DAX库函数概述2.数学函数数学函数主要用于对表格中数值型的数据进行转换和计算。函数解释可能的应用场景DIVIDE(,)实现除法运算，但是当分母为0时可以返回 BLANKO用于除法运算符可能出错的场合QUOTIENT(,)执行除法运算，并仅返回除法运算结果的整 数部分。做整除运算时应用RAN D()返回大于或等于0并且小于1的随机实数。模拟计算时TRUNCT(,)通过舍弃小数部分将实数截断为整数。获取实数的整数部分。227.3 DAX库函数概述 3.统计函数函数解释可能的应用场景RAN K(,expression,)根据expression计算排名每个分公司的年度销售业绩排名RANK.EQ(,)计算参数值value在数据集columnName中 的排名Qrder表示数据集columnName中数 据是升序排列还是降序排列计算本部门每个人的销售成绩在全 公司所有人的销售业绩中的排名237.3 DAX库函数概述 4.文本函数函数解释可能的应用场景FORMAT(,)根据所指定的格式将值value转换为文 本，具体的格式需要参考DAX语言的相 关说明。将日期数据转换成符合某些习惯的文本CREPLACE(,)根据指定的起始位置和长度进行文本的 子串替换。术语的替换REPT(,)重复参数字符串text若干次构造新的字 符串特定数字字符串的构造FIND(,)根据子串内容在母串中从指定位置开始 查找子串，返回子串的起始位置。字母 区分大小写。在名字列中查找给定的“姓”或者名的 人。SEARCH(,)根据子串内容在母串中从指定位置开始 查找子串，返回子串的起始位置。字母 不区分大小写，可以使用通配符。在公司名称列中查找所有公司名称中包 含“科技”的公司。SUBSTITUTE(,)根据子串内容进行子串替换进行产品、装备等名称中的代码替换247.3 DAX库函数概述5.日期和时间函数日期和时间函数用于生成日期时间数据、提取日期时间数据的分量和常 规的日期时间计算。函数解释可能的应用场景CALENDAR(,)返回包含指定的开始日期到指定 的结束日期之间的所有日期构成 的数据表用于构建日历表，辅助基于日期 和时间的智能分析DATEDIFF(,)返回两个日期之间以Interval为单 位的间隔数统计两个日期之间相隔的天数、月数、小时数等DATEVALUE(date_text)将文本格式的日期转换为日期类 型数据将身份证号码中的出生日期部分 转换为日期类型NOW()返回当前的系统日期和时间计算距今的年龄、流逝的时间等 数据TIMEVALUE(time_text)将文本格式的时间转换为时间类 型数据。转换数据表中文本形式的时间数 据为时间类型数据以利于进行日 期时间运算。EDATE(,)返回据开始日期经过指定月份后 的日期计算DEADLINE,例如考试日期、发货日期等。7.3 DAX库函数概述6,时间智能函数时间智能函数的功能是生成指定范围的日期时间数据表，从而可 以进行基于指定时间段的商业智能分析。函数解释可能的应用场景DATEADD(,)根据日期属性列dates计算得到偏移某个时间间隔 的时间表生成和当前表中数据相关 的前后一年的日期表DATESBETWEEN(,)返回日期属性列dates中指定开始和结束日期范围 内的日期数据构建日期筛选条件表267.3 DAX库函数概述 7.筛选器函数筛选器函数用于构造分析计算的上下文环境，并在筛选得到的数据上执行指定的 计算，是理解DAX语言和Power Pivot工作原理的关键。函数解释可能的应用场景ALL(|,)清除所有的筛选条件，返回指定数据表table 和属性列co 1 u m n的所有数据在筛选环境中进行总体的统 计计算ALLSELECTEDCALCULATE(,-)除了指定的筛选器函数，清除其他所有的筛选 条件，返回指定数据表和属性列的相关数据在多重筛选环境中确定保留 的筛选条件CALCULATETABLE(,)第一个参数必须是返回表的函数，返回对表筛 选以后的结果。从指定的数据表中筛选提取 数据，作为后续计算的基础CFILTER(,)使用filter作为每行筛选条件对表table进行筛选从指定的数据表中筛选提取 数据，作为后续计算的基础CLOOKUPVALUE(,-,)从表中搜索满足指定属性名和属性值的数据行在人员信息表中查找某个人 的信息。VALUES()提取指定属性列中不重复的数据组成作为返回 值的一列列数据去重277.3 DAX库函数概述8.逻辑函数逻辑函数用于构造复杂的筛选器函数的条件，也可以用于进行条 件分支计算。函数解释可能的应用场景IF(,)如果1 og i ca l_test条件为真，返 回value_if_true的值，否则返回 value_if_false 的值。生成计算列，例如销售业绩决 定提成。IFERROR(value,value_if_error)保护性表达式求值，如果value 求值出错，州返回 value_if_error,否贝lj返回 value 求值的结果。在可能包含错误数据的情况下 进行计算。SWITCH(,result)多分支表达式求值，不过这里 对于条件value要求是常量，而 不能是逻辑表达式。将月份的整数形式转换为英文 名称形式。287.3 DAX库函数概述9.信息函数信息函数用于表格中的数据进行类型或者内容的检查。函数解释可能的应用场景HASONEVALUE()检查作为参数的列是否被筛选器使用 并且筛选后只有一个值在筛选器上下文中进行 DAX计算时，根据筛选是 否只剩下个值来采取不 同的计算方法。ISERROR()检查要进行的表达式求值是否包含错、口厌可以作为IF函数的第一个 参数使用297.3 DAX库函数概述 10.表操作函数表操作函数是用于表对象计算的一些函数。函数解释可能的应用场景ADDCOLUMNS(,-)为第一个参数指定的表table添加计算列name,计算 列的构造由expression参数指定。增加用于辅助分析的新属性列，甚至完全通过增加 属性列构建计算表。CONVERT(，)强制数据类型转换将日期数据转换为整型数据DATATABLE(ColumnNamel,DataTypel,ColumnName2,DataType2.,Valuel,Value2.,ValueN,ValueN+1.通过指定列名、列数据类型和具体的内联形式的每 行数据构建数据表对象创建数据固定的临时表,(,),(,),这称为表构造函数，由一对花括号括起来数据表的 各行数据。列名由系统自动给定，列数据类型由系 统根据行数据隐式推导创建数据固定的临时表EXCEPT(,实现两个结构相同的表的差运算，返回在第一张表 中出现但不在第二张表中出现的数据判断哪些商品老年人喜欢买但是年轻人不会买。INTERSECT(,)实现两个结构相同的表的交运算，返回同时在两张 表中出现的数据判断同时买两种不同商品的顾客有哪些UNION(,-)实现两个结构相同的表的并运算，将两张表合并为 一张表。将两张不同地区的销售表合并为一张表ROW(,expression,)返回一个具有单行数据的表，该行每个值的属性名 和计算方法由后续的参数name和expression决定。为数据表生成针对所有行的统计结果，例如总额、均额、数量、最大值、最小值、中位数等。SUMMARIZE(,-,-)非常重要的分组统计函数。类似于SQL中的分组统计查询或EXCEL中的数据透视 表作用。30