1、DAX语言DAX语言DAX语言简介DAX公式常用的数据类型整数、小数、文本、日期。文本:不能做加减乘除运算,否则公式报错。1 简介是一门函数语言,全称Data Analysis Expressions,即数据分析表达式,以公式的方法来完成计算,也叫公式语言。允许用户在 PowerPivot 表(“计算列”)和 Excel 数据透视表(“度量值”)中定义自定义计算。DAX 包含一些在 Excel 公式中使用的函数,此外还包含其他设计用于处理关系数据和执行动态聚合的函数。2 特点易于使用:DAX函数使用标准Excel公式语法,并且共用一部分函数;处理关系数据:基于表格、列和关系建立的关6 DAX计
2、算类型计算列计算度量值6.1 DAX表名和列名语法创建新的列或度量值,表名的一般格式:Table NameColumnName如果表名中有空格,那么表名周围的单引号则是必需的。如果表名没有空格,则可以省略单引号,语法如下:TableNameColumnName也可以完全省略表名而只使用列名,但这对于清晰的函数来说不是一个好的做法。列名称必须始终包含方括号。最佳做法是执行以下操作:表名中无空格;始终在公式中包含表名(不要将其省略掉,即使DAX允许)。6.2 创建计算列当需要划分或筛选值,或者要对表中的每一行进行计算时,计算列非常有用。计算列所需元素:新的列名至少一个函数或表达式如果在计算列公式中
3、引用一个表或列,则无需再表中指定行Power BI会为每个计算的当前行计算列。6.3 创建计算度量值当计算百分比或比率,或者需要复杂的聚合时,使用计算度量值。计算度量值的必需元素与计算列的必需元素是相同的:新的度量值名称至少一个函数或表达式7 DAX函数DAX拥有许多可用于成形、组织或分析数据的函数。聚合函数计数函数逻辑函数信息函数文本函数日期函数7.1 聚合函数DAX提供多种聚合函数,包括以下常用函数:SUMAVERAGEMINMAXSUMX(以及其他X函数)这些函数仅适用于数字列,并通常一次只能聚合一列。但是以X结尾的特殊聚合函数(例如SUMX则可同时处理多列。这些函数循环访问表,并为每一
4、行计算表达式)。(1)求销售的咖啡杯数:销售量=Sum(销售数量表数量)(2)求数量列的平均值: 平均销售量=Average(销售数据表销售量)(3)将每一行数据视为一位顾客购买的订单,求订单中最大的杯数: 最大杯数=Max(销售数据表数量)7.2 计数函数DAX中经常使用的计数函数包括:COUNTCOUNTA:计算列中单元格不为空的数目COUNTBLANK:计算列中单元格为空白的数量COUNTROWS:求表中行的个数DISTINCTCOUNT:对列中不重复值计数这些函数用来计数不同的元素,如非重复值、非空值和表行。(1) 数据表中的每一行都是一笔订单,求行的个数就是求订单数量 订单数量=Co
5、untrows(销售数据表)(2) 创建一个门店数量的度量值: 门店数量=Distinctcount(销售数据表门店)7.3 逻辑函数DAX的逻辑函数包括:ANDORNOTIFIFERROR这些特殊函数还可以用运算符表达。例如,在DAX公式中AND可以输入为&。如果公式中存在两个以上条件,则可以使用运算符(如&),但在其他情况最好使用函数名本身(如AND),以增强DAX代码可读性。7.4 信息函数DAX中的信息函数包括:ISBLANKISNUMBERISTEXTISNONTEXTISERROR尽管这些函数在具体情况下有用,但提前知道列的数据类型,而不依赖这些函数来提供数据类型仍很有价值。DAX
6、 使用 MAX 和 MIN 函数来聚合和比较值。7.5 文本函数DAX 中的文本函数包括:CONCATENTATEREPLACESEARCHUPPERFIXED这些文本函数与同名的 Excel 函数工作方式类似。7.6 日期函数DAX 包含以下日期函数:DATEHOURNOWEOMONTHWEEKDAY尽管这些函数对于从日期值中计算和提取信息很有用,但它们并不适用于使用日期表的时间智能。7.7 Calculate函数 计算由指定筛选器修改的上下文中的表达式。语法:CALCULATE(,)参数:术语定义expression要进行计算的表达式filter1,filter2,(可选)定义筛选器的布尔
7、表达式或表达式的逗号分隔的列表Eg:Calculate销售量 = calculate(销售量,产品表咖啡种类=”拿铁”,产品表杯型=”中”)7.8 All函数返回表中的所有行或返回列中的所有值,同时忽略可能已应用的任何筛选器。可用于清楚筛选器并对表中的所有行创建计算。语法:ALL( | , , , )All函数可以引用表或者列。参数:术语定义table要清除其筛选器的表column要清除其筛选器的列All函数的参数必须或者是对某一基表的引用,或者是对某一基列的引用,不能将表的表达式或列的表达式与All函数一起使用。All销售量 = calculate(销售量,all(销售数据表) 注意引用表比
8、较简单,即清除该表的所有筛选限定条件,当使用All函数引用列时,需注意:(1)必须保证All函数所清除的筛选列和初始筛选条件中的筛选列一致(同一张表的同一列)。(2)All函数所有引用列参数必须来自同一张表,否则是无效的。7.9 Allexcept和Allselected函数7.9.1 Allexcept函数删除表中除已应用于指定列的筛选器之外的所有上下文筛选器。语法:ALLEXCEPT(,)Allexcept函数的语法组成分为两部分,第一部分是表,第二部分是想要排除的列,与All函数一样,它可以引用多列,并且返回的结果是表,所以在度量值中不能够单独使用,需要配合像Calculate和Coun
9、trows这些可以引用表的函数使用。Eg: Allexcept = calculate(销售量,allexcpt(产品表,产品表杯型)7.9.2 Allselected函数对表中所显示的筛选条件执行清除筛选,而其他筛选条件皆保留。语法:ALLSELECTED( | )Allselected(表或列),它返回的结果也是表。7.10 Filter函数:高级过滤器筛选器函数,返回结果是一张表,无法单独使用,经常与Calculate函数搭配,也可以直接与某些聚合函数搭配使用,比如Countrows(Filter(表,筛选条件)公式来计算表的行数。语法:FILTER(表,筛选条件)例子:求拿铁中杯咖啡的
10、销售量Filter销售量 =calculate(销售量,filter(产品表,产品表咖啡种类=”拿铁”&产品表杯型=”中”)等同于:Calculate销售量 = calculate(销售量,产品表咖啡种类=”拿铁”,产品表杯型=”中”)注意:当筛选条件出现如下类型,Calculate函数中的直接筛选条件不可用,需要用Filter函数:列=度量值列=公式列=列度量值=度量值度量值=公式度量值=固定值如:求每个季度拿铁咖啡的销售量超过200杯的门店的销售量。Filter销售量 = calculate(销售量,filter(门店信息表,销售量200)7.11 Divide函数:安全除法Divide(
11、分子,分母,可选项) 可选项如果不选则默认返回空值,也可以特别设定,比如将此可选项设为1,则当分母为0时返回1。作用:可以在分母为0时,防止出现报错信息。如: Divide(数量,0),得到的结果为空值。 年比年增长率 = (销售量-calculate(销售量,previousyear(日历表日期)/calculate(销售量,previousyear(日历表日期) 年比年增长率divide = divide(销售量-calculate(销售量,previousyear(日历表日期) , calculate(销售量,previousyear(日历表日期)7.12 If/Switch函数 If年
12、龄分层 = if(年龄20&年龄30&年龄40&年龄50,”50以上”) If函数当有特别多条件,会“外套”套“外套”,使用Switch函数能解决这个问题。新建一个度量值,用Switch+True函数的方法来定义不同条件的返回值,明显会使表达式更清晰。 Switch 年龄分组 = switch(true, 年龄20&年龄30&年龄40&年龄50,”50以上”) Switch+True函数适用于逻辑判断,如果逻辑判断是以一个准确值作为依据,那么Switch函数还可以直接引用表达式,如下:Switch 日历 = switch(日历表月, 1,”一月”, 2,”二月”, 3,”三月”, 4,”四月”
13、, 5,”五月”,6,”六月”,7,”七月”,8,”八月”, 9,”九月”,10,”十月”, 11,”十一月”,12,”十二月”)7.13 关系函数:Related、Relatedtable和LookupvalueLookup表与数据表Related函数专为关系管道建立,所以使用时不用考虑上下文在关系模型中转换的问题。数据表引用Loolup表(多对一)产品名称 = related(产品表产品名称)可嵌入其他公式:收入=数量*related(产品表价格)一对多,使用Relatedtable:在产品表中添加一个“订单数量”列,求每种产品对应的订单数量。订单数量 = countrows(relate
14、dtable(销售数据表)多条件查询,LookupvalueLooupvalue(产品表价格, 目标输出结果列产品表咖啡种类,销售数据表咖啡种类, 条件1产品表杯型,销售数据表杯型) 条件2Lookupvalue函数主要包括3各部分:第一、输入目标输出结果的列名称,即产品表中的“价格”列;第二、输入目标表中将要搜索的范围;第三、输入原标中需要搜索的关联字段。7.14 Time Intelligence函数:时间只能函数获取日历表 利用Excel制作 在Power Query中直接建立一张日期表。第一步:新建一个空查询;第二步:在公式中输入公式:=List.Dates。 注意:在编辑查询器中输入
15、的公式用的是M语言,要区分大小写。输入Start(日期起点)、Count(长度)、Step(颗粒度)等。第三步:单击“转换”选项卡中的“到表”命令,将其转换成表格格式。8 书写规则举例:度量值 = CALCULATE(销售量,产品表咖啡种类 = “拿铁”)等号左边是度量值名称,等号右边是度量值公式。在输入公式时,使用单引号引用某张表,使用中括号表示度量值或列。*在写度量值公式时,如果想引用的是哪张表中的某一列,那么需要使用限定列。限定列:明确所引用的是哪张表中的哪一列。9 运算符算数运算符含义示例+ (加号)加3+3-(减号)减或符号3-1-1* (星号)乘3*3/ (正斜杠)除3/3 (插入号)求幂164比较运算符含义示例=等于产品表咖啡种类 = “拿铁”大于日历表日期 “Jan 1 2015”小于销售数量表数量 =大于或等于销售量 = 200=小于或等于销售量= 100文本运算符含义示例&(与号)连接(或串联)两个值以生成一个连续的文本值产品表咖啡种类& 产品表杯形&”杯”&(双与号)And和,同时满足几个条件门店信息表门店=”北京市”&产品表咖啡种类=”拿铁”| (双竖线符号)Or或,满足任意一个条件门店信息表门店=”北京市”&(产品表咖啡种类=”拿铁”|产品表杯型=”中”)