DAX语言.docx

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