PowerBI数据分析与数据可视化第4章数据分析表达式.pptx

第4章数据分析表达式,数据分析表达式（Data Analysis Expressions，DAX）是一个函数和运算符库，这些函数和运算符可用便在Microsoft SQL Server Analysis Services、Excel中的Power Pivot以及Power BI中创建公式和表达式。本章主要内容：DAX基础DAX函数,4.1 DAX基础,DAX也称公式语言，它与Java、Python、C+等计算机程序设计语言不同，它通过公式来完成计算。DAX与Excel的公式非常相似，而且大部分函数都是通用的。本节主要内容：语法规则运算符数据类型上下文,4.1.1语法规则,语法规则是DAX公式的编写规则。一个DAX公式通常包含度量值、函数、运算符、列引用等组成，如图所示。度量值度量值类似于程序设计语言中的全局变量，是一个标量，通常用于表示单个的值。例如，求和、求平均值、求最大值等结果为单个值，可定义为度量值。度量值可在报表任意位置使用。在Power BI Desktop中，可用公式来创建度量值、列和表，所以等号左侧可以是新建的度量值、列或表的名称。用公式创建的列和表可分别称为计算列和计算表。等号等号表示公式的开始，其后是完成各种计算的表达式。,函数Power BI Desktop提供了大量的内置函数，这些函数通常用于在数据表中返回单个值，或者返回包含单列或多列的表。运算符运算符完成相应计算。列引用在公式中除了列引用，还涉及度量值和表的引用。列和度量值在引用时，名称必须放在方括号中。在引用表时，表名称包含空格或其他特殊符号时，必须将名称放在单引号中，否则可以直接使用名称。引用列时，如果列不属于当前数据表，则必须用数据表名称限定列，例如，“销售数据销量”或者“销售数据销量”。使用数据表名称限定列名也称为完全限定，建议在公式中都使用完全限定，避免产生误解。,DAX公式与Excel公式类似，两者主要区别如下。Excel公式可以直接引用单个的单元格或某个范围的多个单元格。Power BI公式只能直接引用完整的数据表或数据列。通过筛选器和函数，可获得列的一部分、列中的唯一值或者表的一部分的引用。DAX公式与Excel支持的数据类型并非完全相同。通常，DAX 提供的数据类型比Excel 多，在导入数据时DAX会对某些数据执行隐式类型转换。DAX公式还具有下列特点。DAX公式不能修改表中原有的数据，只能通过新建列操作为表添加数据。可通过DAX公式创建计算列、度量值和表，但不能创建计算行。在DAX公式中，不限制函数的嵌套调用。DAX提供了返回表的函数。,4.1.2运算符,DAX支持4种运算符：算术运算符、比较运算符、文本串联运算符和逻辑运算符。1.算术运算符算术运算符用于执行算术运算，运算结果为数值。,2.比较运算符比较运算符用于比较操作数关系，运算结果为逻辑值True或False。,3.文本串联运算符文本串联运算符只有一个：&，用于将两个字符串连接成一个字符串。例如，abc&123，结果为abc1234.逻辑运算符逻辑运算符用于执行逻辑运算，运算结果为逻辑值True或False。,4.1.3数据类型,1.数字类型Power BI Desktop支持三种数字类型：小数、定点小数和整数。小数：表示 64 位（8字节）浮点数。可以处理从-1.79E+308到-2.23E-308的负数、0，以及从2.23E-308到1.79E+308的正数。定点小数：小数点位置固定，小数点后固定有四位有效数字数，最多19位有效数字。它可以表示的值范围为-922,337,203,685,477.5807到+922,337,203,685,477.5807。整数：表示64位（8字节）整数值。最多允许19位有效数字，取值范围从-,223,372,036,854,775,808到9,223,372,036,854,775,807。,2.日期时间类型Power BI Desktop 支持查询视图中的五种日期时间数据类型，以及报表视图和模型中的三种日期时间数据类型。日期/时间：表示日期和时间值。日期/时间值以小数类型进行存储，可以在这两种类型之间进行转换。日期的时间部分存储为1/300 秒（3.33 ms）的整数倍的分数。支持1900年和9999年之间的日期。日期：仅表示日期，没有时间部分。时间：仅表示时间，没有日期部分。日期/时间/时区：表示UTC日期/时间。数据加载后，会被转换为日期/时间类型。持续时间：表示时间的长度。数据加载后，会被转换为十进制数类型。可将其与日期/时间字段执行加法和减法运算。,3.文本类型文本类型为Unicode字符串，其最大字符串长度为268,435,456个Unicode字符或536,870,912字节。4.True/False类型True/False类型表示逻辑值的True或False。5.空值/Null类型空值/Null类型可在DAX中表示和替代SQL中的Null。可用BLANK函数创建空值，也可用ISBLANK逻辑函数测试空值。,4.1.4上下文,上下文（Context）在众多高级程序设计语言中使用，它代表了变量、函数、程序的运行环境。上下文也是DAX的一个重要概念。在DAX中，上下文是公式的计算环境。DAX公式中有两种上下文：行上下文和筛选上下文。1.行上下文行上下文可以理解为当前记录（当前行）。从数据源获取的各种数据后，Power BI Desktop将其以关系表（二维表）的形式存储。在计算函数时，通常都会应用某一行中某个列的数据，此时的行就是当前计算的行上下文。2.筛选上下文筛选上下文可以理解为作用于表的筛选条件（筛选器），函数应用筛选出的数据（单个或多个值）完成计算。,4.2DAX函数,函数是通过使用特定值、调用参数，并按特定顺序或结构来执行计算的预定义公式。函数参数可以是其他函数、另一个公式、表达式、列引用、数字、文本、逻辑值（如True或False）或者常量。本节主要内容：DAX函数概述聚合函数逻辑函数数学函数文本函数信息函数日期和时间函数时间智能函数筛选器函数,4.2.1DAX函数概述,DAX中的函数按类型可分为：日期和时间函数、时间智能函数、信息函数、逻辑函数、数学函数、统计函数、文本函数等。DAX函数具有下列特点。DAX函数始终引用整列或整个表。如果仅想使用表或列中的某个特定值，则需为公式添加筛选器。在需要逐行自定义计算时，DAX允许将当前行的值或关联值作为参数。DAX函数可返回计算表，计算表可作为其他函数的参数。DAX提供了各种时间智能函数。这些函数可用于定义或选择日期范围，以便执行动态计算。,DAX对内置函数的参数名称进行了规范化,4.2.2聚合函数,AVERAGE()计算列中所有数字的平均值。如果列中包含文本，则不执行计算，函数返回空值。列中包含空单元或逻辑值时，则忽略这些值，不对行进行计数。值为0纳入计算，对行计数。例如：=AVERAGE(成绩数据语文)AVERAGEA()计算列中所有值的平均值。列中的非数字值处理规则为：计算结果为True的值作为1计数，计算结果为False的值、包含非数字文本的值、空文本()和空单元均作为0计数。例如：=AVERAGEA(成绩数据语文)AVERAGEX(,)计算表中表达式计算结果的平均值。例如：=AVERAGEX(成绩数据,语文+数学+外语),COUNT()对列中的数字和日期进行计数。如果单元包含不能转换成数字的文本，则不对该行进行计数。如果列中没有可计数的单元，函数返回空值。例如：=COUNT(成绩数据语文)COUNTA()对列中非空单元进行计数。例如：=COUNTA(成绩数据语文)COUNTAX(,)对表中的每一行计算表达式，返回表达式计算结果不为空的数目。例如：=COUNTAX(成绩数据,专业代码)COUNTROWS()计算指定表的行数。例如：=COUNTROWS(成绩数据),MAX()返回数值列中的最大值。例如：=MAX(语文)MIN()返回数值列中的最小值。例如：=MIN(语文)RANK.EQ(,)计算value在列columnName中的排名。order指定排名方式，可省略。order为0（默认）时，列中最大值排名为1；order为1时，列中最小值排名为1。例如，创建语文成绩的排名列。=RANK.EQ(成绩数据语文,成绩数据语文),RANKX(,)计算表table中表达式expression计算结果在value中的排名。参数order与RANK.EQ()函数中一致。参数ties为skip（默认）时，相同排名要计数，例如，有5个值排名第10，则下一个排名为15（10+5）。参数ties为Dense时，相同排名只计数1次，例如，有5个值排名第10，则下一个排名为11。例如：=RANKX(成绩数据,成绩数据外语+成绩数据数学+成绩数据语文)SUM()对列中的数值进行求和。例如：=SUM(销售数据销量),SUMMARIZE(,)对表table中的数据按分组列groupBy_columnName计算表达式expression，计算结果作为列name的值，返回的表包含分组列和计算结果列。可以有多个分组列。计算表达式expression也可有多个，每个表达式一个名称name。,4.2.3 逻辑函数,AND(,)对两个逻辑值计算逻辑与。例如：=AND(AVERAGE(成绩数据语文)60,AVERAGE(成绩数据数学)60)NOT()对逻辑值取反。例如：=NOT(AVERAGE(成绩数据语文)60)OR(,)对两个逻辑值计算逻辑或。例如：=OR(AVERAGE(成绩数据语文)60,AVERAGE(成绩数据数学)60),TRUE()返回逻辑值True。例如：=TRUE()FALSE()返回逻辑值False。例如：=FALSE()IF(logical_test,value_if_false)如果条件logical_test为True，则返回值value_if_true；否则返回值value_if_false。例如：=IF(成绩 55,合格,不合格),IFERROR(value,value_if_error)在计算value发生错误时，函数返回value_if_error的值，否则返回value的值。例如：=IFERROR(成绩 55,出错)SWITCH(,)计算表达式expression，计算结果与某个value匹配时，对应的result作为函数返回值。如果没有值与计算结果匹配，则else作为函数返回值。例如：=SWITCH(weekday,1,周一,2,周二,3,周三,4,周四,5,周五,6,周六,7,周日,非法数),4.2.4 数学函数,ABS()求number的绝对值。例如：=ABS(销售量-100)CEILING(,)将数字number向上舍入到最接近的整数或基数significance的最接近倍数。例如，下面的表达式将单价舍入为整数。=CEILING(单价,1)FLOOR(,)将数字number向下舍入到最接近的整数或基数significance的最接近倍数。例如：=CEILING(单价,0.5)。,INT()将数字number向下舍入到最接近的整数。例如，下面的表达式返回-5。=INT(-4.3)TRUNC()返回数字的整数部分。例如，下面的表达式返回-4。=TRUNC(-4.3)RAND()返回大于或等于0且小于1的随机数字。例如：=RAND(),RANDBETWEEN(,)返回指定范围内的随机数字。例如，返回1和10之间的随机数字。=RANDBETWEEN(1,10)ROUND(,)将数字舍入到指定的位数。如果num_digits大于0，则将数字舍入到指定的小数位数。如果num_digits为0，则将数字舍入到最接近的整数。如果 num_digits小于0，则将数字向小数点左侧舍入。例如，下面的表达式返回3.3。=ROUND(3.25,1)例如，下面的表达式返回30。=ROUND(32.5,-1),4.2.5 文本函数,BLANK()返回一个空值。例如：=BLANK()EXACT(,)比较两个文本字符串；如果它们完全相同则返回True，否则返回False。EXACT区分大小写但忽略格式上的差异。例如：=EXACT(ab,xABC)FIND(,)在字符串within_text中start_num指定位置开始查找find_text最先出现的位置。start_num省略时，从第1个字符开始查找。NotFoundValue指定未找到时的返回值，默认为空值。例如：=FIND(a,blankabc),LEFT(,)从文本字符串的开头返回指定数目的字符。例如：=LEFT(abcd,3)RIGHT(,)从文本字符串的末尾返回指定数目的字符。=RIGHT(abcd,3)MID(,)根据给出的开始位置start_num和长度num_chars，从文本字符串text的中间返回字符串。例如：=MID(abcdef,2,3),4.2.6 信息函数,CONTAINS(,)如果在表table的列columnName中包含value，则函数返回True，否则返回False。例如：=CONTAINS(成绩数据,成绩数据专业代号,306003)ISBLANK()如果值value为空白，则返回True，否则返回False。=ISBLANK(度量值2)ISNUMBER()如果值value为数字，则返回True，否则返回False。=ISNUMBER(度量值2),ISTEXT()如果值value为文本，则返回True，否则返回False。=ISTEXT(度量值2)LOOKUPVALUE(,)在search_column中查找search_value，如果找到匹配值，则返回该行中result_column列的值；没有找到匹配值，则返回空值。例如，返回总成绩排名第3的专业代码：=LOOKUPVALUE(成绩数据专业代号,成绩数据总成绩排名,3),4.2.7 日期和时间函数,DATE(,)用给定的整数表示的年、月、日创建日期，返回datetime格式的值。year值在0到99之间时，会加上1900作为年份值，例如DATE（90,1,1）返回日期为“1990年1月1日”。month超出月份有效数字1到12范围时，会以12为基数取模，并加减年份，例如，下面的表达式返回日期为“2017年11月1日。=DATE（2018,-1,1）例如，下面的表达式返回日期为“2019年2月1日”。=DATE（2018,14,1）类似地，如果参数day超过了指定月份日的有效范围，会加减月份来获得正确日期。例如，下面的表达式返回日期为“2019年1月30日”。=DATE(2018,14,-1),DATEVALUE(date_text)将文本形式的日期转换为日期时间格式的日期。例如，=DATEVALUE(08/2/17)NOW()返回当前日期时间。例如：=NOW()TODAY()返回当前日期。例如：=TODAY(),YEAR()返回日期中的以4位整数表示的年份。例如：=YEAR(NOW()MONTH()返回日期中的月份，1到12内的数字。例如：=MONTH(NOW()DAY()返回日期中的日，1到31内的数字。例如：=DAY(NOW(),HOUR()返回时间中的小时，0到23内的数字。例如：=HOUR(NOW()MINUTE()返回时间中的分钟，0到59内的数字。例如：=MINUTE(NOW()SECOND()返回时间中的秒，0到59内的数字。例如：=SECOND(NOW(),TIME(hour,minute,second)将作为数字提供的小时、分钟和秒钟转换为datetime格式的时间，默认日期为“1899年12月30日”。例如，下面的表达式返回的日期时间为“1899年12月30日 13:04:50”。=TIME(13,4,50)TIMEVALUE(time_text)将文本格式的时间转换为datetime格式的时间，默认日期为“1899年12月30日”。例如，下面的表达式返回的日期时间为“1899年12月30日 13:04:50”。=TIMEVALUE(13:04:50)WEEKDAY(,)返回日期是星期几。return_type为1（默认）时，星期日为1，星期六为7；return_type为2时，星期一为1，星期日为7；return_type为3时，星期日为0，星期六为6。例如：=WEEKDAY(NOW(),2),WEEKNUM(,)返回日期是一年中的第几周。return_type为1（默认）时，一周从星期日开始；return_type为2时，一周从星期一开始。例如：=WEEKNUM(NOW(),2)EDATE(,)返回指定日期start_date加上months个月份的日期。参数start_date可以是datetime或文本格式的日期。months为整数，不是整数时只取整数部分（截断取整）。例如，下面的表达式返回日期为“2017年7月5日”。=EDATE(2017-4-5,3)YEARFRAC(,)计算两个日期之间的天数在一年中占的比例，返回小数。例如，下面的表达式返回0.18。=YEARFRAC(2017/3/11,2017/5/15),4.2.8 时间智能函数,CLOSINGBALANCEMONTH(,)对dates指定的日期列中每月最后一个日期计算表达式expression。例如，计算月末销售金额。=CLOSINGBALANCEMONTH(SUMX(销售数据,销售数据销量*销售数据单价),销售数据日期)CLOSINGBALANCEQUARTER(,)对dates指定的日期列中每季度最后一个日期计算表达式expression。例如：=CLOSINGBALANCEQUARTER(SUMX(销售数据,销售数据销量*销售数据单价),销售数据日期)CLOSINGBALANCEYEAR(,)对dates指定的日期列中每年最后一个日期计算表达式expression。例如：=CLOSINGBALANCEYEAR(SUMX(销售数据,销售数据销量*销售数据单价),销售数据日期),DATEADD(,)Dates为包含日期的列，number为增加的值。interval为增加类型，可以是year（年）、quarter（季度）、month（月）、day（日）。函数对指定列中的每一个日期按interval指定的类型加上number，获得新日期。新日期在dates列包含的日期范围内的，则出现在返回的表中。例如，按月份加3生成新表。=DATEADD(日期表日期,3,MONTH)DATESBETWEEN(,)返回一个表，从指定日期列dates中返回在start_date和end_date范围内的日期。例如：=DATESBETWEEN(日期表日期,2018/1/1,2018/3/31)DATESMTD()返回一个表，该表包含当前上下文中本月截止到现在的日期列。例如：=DATESMTD(销售数据日期)。,DATESQTD()返回一个表，该表包含当前上下文中本季度截止到现在的日期列。例如：=DATESQTD(销售数据日期)DATESYTD()返回一个表，该表包含当前上下文中本年截止到现在的日期列。例如：=DATESYTD(销售数据日期)ENDOFMONTH()从当前上下文中日期列dates中返回相应月份的最后一个日期。例如：=ENDOFMONTH(销售数据日期),ENDOFQUARTER()从当前上下文中日期列dates中返回相应季度最后一个日期。例如：=ENDOFMONTH(销售数据日期)ENDOFYEAR()从当前上下文中日期列dates中返回相应年度最后一个日期。例如：=ENDOFYEAR(销售数据日期)对应的STARTOFMONTH()、STARTOFQUARTER()和STARTOFYEAR()函数分别返回相应每月、季度和年都中的第1个日期。,FIRSTDATE()从指定日期列dates中返回第一个日期。例如：=FIRSTDATE(销售数据日期)LASTDATE()从指定日期列dates中返回最后一个日期。例如：=LASTDATE(销售数据日期)NEXTDAY()返回一个表，包含从当前上下文中dates列中第一个日期的下一天的日期。例如：=CALCULATE(SUMX(销售数据,销售数据销量),NEXTDAY(销售数据日期)NEXTDAY()返回的只是下一天的日期，所以SUMX()获得的就是下一天的“销量”。类似的PREVIOUSDAY()返回上下文中dates列中第一个日期的前一天的日期。,NEXTMONTH()返回一个表，包含从当前上下文中dates列中第一个日期的下一个月包含的所有日期。例如：=CALCULATE(SUMX(销售数据,销售数据销量),NEXTMONTH(销售数据日期)SUMX()获得的从当前日期开始的一个月时间段之内“销量”之和。对应的NEXTQUARTER()和NEXTYEAR()函数分别返回下一个季度和下一年中的所有日期。PREVIOUSMONTH()、PREVIOUSQUARTER()和PREVIOUSYEAR()函数分别返回前一个月，前一个季度和前一年中的所有日期。TOTALMTD(,)计算当前上下文中当月至今的expression的值。例如：=TOTALMTD(SUMX(销售数据,销售数据销量),销售数据日期)类似的TOTALQTD()、TOTALYTD()分别计算当前上下文中当前季度、当年至今的expression的值,4.2.9 筛选器函数,ALL(|,)返回一个表，包含表中的所有行和列，或者返回列中的所有行。例如，下面的表达式返回“销售数据”表中所有数据。=ALL(销售数据)例如，下面的表达式返回“销售数据”表中“产品”和“销量”量列的所有数据。=ALL(销售数据产品,销售数据销量)CALCULATE(,)返回一个表，应用指定筛选条件（filter1、filter2等）完成表达式expression的计算。例如，下面的表达式计算2017年的总销量。=CALCULATE(sum(销售数据销量),year(销售数据日期)=2017)CALCULATETABLE(,)应用指定筛选条件对表进行筛选。表达式expression可以是表名称，也可以是计算结果为表的其他表达式。例如：=CALCULATETABLE(销售数据,销售数据销量100,销售数据产品=Montana),DISTINCT()返回由一个列构成的表，列中不包含重复值。例如：=DISTINCT(成绩数据专业代码)FILTER(,)返回一个表，包含表table中符合筛选条件的所有行。例如：=FILTER(销售数据,销售数据销量100&销售数据产品=Montana)RELATED()从另一个表返回关联的列。例如：=RELATED(招生专业专业名称)对于“成绩数据”表中的当前行，RELATED(招生专业专业名称)返回一个值。RELATEDTABLE()从关联表返回关联的行。例如：=COUNTROWS(RELATEDTABLE(成绩数据),