Dax Functions 简明教程
DAX Functions - Introduction
DAX 的全称是 *D*ata *A*nalysis E*x*pressions(数据分析表达式)。DAX 是一种公式语言,是一个用于公式或表达式中的函数、运算符和常量的集合,可以计算并返回一个或多个值。DAX 是与 Microsoft Excel Power Pivot 的数据模型以及 Microsoft Power BI 相关联的公式语言。
DAX stands for *D*ata *A*nalysis E*x*pressions. DAX is a formula language and is a collection of functions, operators, and constants that can be used in a formula or expression to calculate and return one or more values. DAX is the formula language associated with the Data Model of Microsoft Excel Power Pivot and with Microsoft Power BI.
DAX 不是一种编程语言,而是一种公式语言,它允许用户在计算列和计算字段(也称为度量)中定义自定义计算。借助 DAX,你可以使用数据模型中现有数据创建新的信息。DAX 公式使你可以执行数据建模、数据分析,并使用结果进行报告和决策。
DAX is not a programming language, however it is a formula language that allows the users to define custom calculations in calculated columns and calculated fields (also known as measures). DAX helps you create new information from the existing data in your Data Model. DAX formulas enable you to perform data modeling, data analysis, and use the results for reporting and decision making.
有关 DAX 的深入理解,请参阅本教程库中有关 DAX 的教程。
For an in-depth understanding of DAX, refer to the tutorial – DAX in this tutorials library.
What is a DAX Function?
DAX 函数是一种内置函数,它在 DAX 语言中提供,使你可以对数据模型中表格中的数据执行各个操作。
A DAX function is an inbuilt function provided in the DAX language to enable you to perform various actions on the data in the tables in your Data Model.
DAX 函数使你可以对数据模型执行常用的数据计算。某些 DAX 函数的名称及其功能与 Excel 函数的相同,但已修改为使用 DAX 数据类型并与表格和列一起使用,如下节中所突出显示的那样。DAX 附加了一些旨在与关系数据一起使用并执行动态聚合的函数。
DAX functions enable you to perform commonly used data calculations on the Data Model. Some of the DAX functions have same names and functionality as that of Excel functions but have been modified to use DAX data types and to work with tables and columns, as highlighted in the next section. DAX has additional functions that are designed to work with relational data and perform dynamic aggregation.
DAX 函数在使用 DAX 进行数据建模和报告中发挥着重要作用。
DAX functions play an important role in the usage of DAX for data modeling and reporting.
Excel Functions vs. DAX Functions
Excel 函数与 DAX 函数之间存在某些相似之处,但也存在某些差异。以下是 Excel 函数和 DAX 函数之间的相似之处和差异 -
There are certain similarities between the Excel functions and the DAX functions and there are certain differences too. Following are the similarities and differences between Excel functions and DAX functions −
Similarities Between Excel Functions and DAX Functions
-
Certain DAX functions have the same name and the same general behavior as Excel functions.
-
DAX has lookup functions that are similar to the array and vector lookup functions in Excel.
Differences Between Excel Functions and DAX Functions
-
DAX functions have been modified to take different types of inputs and some of the DAX functions might return a different data type. Hence, you need to understand the usage of these functions separately though they have the same name.
-
You cannot use DAX functions in an Excel formula or use Excel functions in DAX formula, without the required modifications.
-
Excel functions take a cell reference or a range of cells as a reference. DAX functions never take a cell reference or a range of cells as a reference, but instead take a column or table as a reference.
-
Excel date and time functions return an integer that represents a date as a serial number. DAX date and time functions return a datetime data type that is in DAX but not in Excel.
-
Excel has no functions that return a table, but some functions can work with arrays. Many of the DAX functions can easily reference complete tables and columns to perform calculations and return a table or a column of values. This ability of DAX adds power to the Power Pivot, Power View and Power BI, where DAX is used.
-
DAX lookup functions require that a relationship is established between the respective tables.
DAX Parameter Naming Conventions
DAX 拥有标准的参数名称来帮助使用和理解 DAX 函数。此外,您可以在参数名前使用特定前缀。如果前缀清晰易懂,您可以使用前缀本身作为参数名。
DAX has standard parameter names to facilitate the usage and understanding of the DAX functions. Further, you can use certain prefixes to the parameter names. If the prefix is clear enough, you can use the prefix itself as the parameter name.
您需要了解 DAX 参数的命名约定,以便了解 DAX 函数的语法并正确使用所需参数的值。
You need to understand DAX parameter naming conventions so as to understand the syntax of the DAX functions and use the values for the required parameters correctly.
有关详细信息,请参阅第 {} 章。
Refer to the chapter − DAX Parameter Naming Conventions for details.
Types of DAX Functions
DAX 支持以下类型的函数。
DAX supports the following types of functions.
-
DAX Table-Valued Functions DAX Filter FunctionsDAX Aggregation FunctionsDAX Time Intelligence Functions
-
DAX Date and Time Functions
-
DAX Information Functions
-
DAX Logical Functions
-
DAX Math and Trig Functions
-
DAX Other Functions
-
DAX Parent and Child Functions
-
DAX Statistical Functions
-
DAX Text Functions
-
DAX Description Structure Functions
DAX Table-Valued Functions
许多 DAX 函数将表格作为输入或输出表格,或者同时进行。这些 DAX 函数称为 DAX 表值函数。由于表格可以只有一列,因此 DAX 表值函数也接受单列作为输入。您有以下类型的 DAX 表值函数 -
Many DAX functions take tables as input or output tables or do both. These DAX functions are called DAX table-valued functions. Because a table can have a single column, DAX table-valued functions also take single columns as inputs. You have the following types of DAX table-valued functions −
-
DAX Aggregation functions
-
DAX Filter functions
-
DAX Time intelligence functions
DAX Aggregation Functions
DAX 聚合函数汇总表各行中的任何表达式,并且在计算中很有用。
DAX Aggregation functions aggregate any expression over the rows of a table and are useful in calculations.
有关详细信息,请参阅第 {} 章。
Refer to the chapter − DAX Aggregation functions for details.
DAX Filter Functions
DAX 筛选器函数返回列或表格或与当前行相关的值。您可以使用 DAX 筛选器函数返回特定的数据类型,查找相关表格中的值,并按相关值进行筛选。DAX 查找函数通过使用表格和它们之间的关系来工作。DAX 筛选器函数使您能够操作数据上下文以创建动态计算。
DAX Filter functions return a column or a table or values related to the current row. You can use DAX Filter functions to return specific data types, look up values in related tables and filter by related values. DAX Lookup functions work by using tables and relationships between them. DAX Filter functions enable you to manipulate the data context to create dynamic calculations.
有关详细信息,请参阅第 {} 章。
Refer to the chapter − DAX Filter functions for details.
DAX Time Intelligence Functions
DAX 时间智能函数返回一个包含日期的表格,或使用一个日期表格计算聚合结果。这些 DAX 函数可帮助您创建计算结果,满足商业智能分析需求,让您使用时间段(包括天、月、季度和年)操作数据。
DAX Time Intelligence functions return a table of dates or the use a table of dates to calculate an aggregation. These DAX functions help you create calculations that support the needs of Business Intelligence analysis by enabling you to manipulate data using time periods, including days, months, quarters, and years.
有关详细信息,请参阅章节 DAX Time Intelligence functions 。
Refer to the chapter − DAX Time Intelligence functions for details.
DAX Date and Time Functions
DAX 日期和时间函数与 Excel 日期和时间函数类似。但是,DAX 日期和时间函数基于 DAX 的 datetime 数据类型。
DAX Date and Time functions are similar to the Excel date and time functions. However, DAX Date and Time functions are based on the datetime data type of DAX.
有关详细信息,请参阅章节 DAX Date and Time functions 。
Refer to the chapter − DAX Date and Time functions for details.
DAX Information Functions
DAX 信息函数查看以参数形式提供的单元格或行,并告知您该值是否与预期类型匹配。
DAX Information functions look at the cell or row that is provided as an argument and tell you whether the value matches the expected type.
有关详细信息,请参阅章节 DAX Information functions 。
Refer to the chapter − DAX Information functions for details.
DAX Logical Functions
DAX 逻辑函数返回表达式中值的信息。例如,DAX TRUE 函数让您知道正在计算的表达式是否返回 TRUE 值。
DAX Logical Functions return information about values in an expression. For example, DAX TRUE function lets you know whether an expression that you are evaluating returns a TRUE value.
有关详细信息,请参阅章节 DAX Logical functions 。
Refer to the chapter − DAX Logical functions for details.
DAX Math and Trig Functions
DAX 数学和三角函数与 Excel 的数学和三角函数非常相似。
DAX Mathematical and Trigonometric functions are very similar to the Excel mathematical and trigonometric functions.
有关详细信息,请参阅章节 DAX Math and Trig functions 。
Refer to the chapter − DAX Math and Trig functions for details.
DAX Parent and Child Functions
DAX 父子函数用于管理以数据模型中父子层次结构呈现的数据。
DAX Parent and Child functions are useful in managing data that is presented as a parent/child hierarchy in the Data Model.
有关详细信息,请参阅章节 DAX Parent and Child functions 。
Refer to the chapter − DAX Parent and Child functions for details.
DAX Statistical Functions
DAX 统计函数与 Excel 统计函数非常相似。
DAX Statistical functions are very similar to the Excel Statistical functions.
有关详细信息,请参阅章节 DAX Statistical functions 。
Refer to the chapter − DAX Statistical functions for details.
DAX Text Functions
DAX 文本函数处理表格和列。使用 DAX 文本函数,您可以返回字符串的一部分,在字符串中搜索文本,或连接字符串值。您还可以控制日期、时间和数字的格式。
DAX Text functions work with tables and columns. With DAX Text functions, you can return part of a string, search for text within a string or concatenate string values. You can also control the formats for dates, times, and numbers.
有关详细信息,请参阅章节 DAX Text functions 。
Refer to the chapter − DAX Text functions for details.
DAX Other Functions
这些 DAX 函数执行的独特操作无法由其他大多数函数所属的任何类别定义。
These DAX functions perform unique actions that cannot be defined by any of the categories most other functions belong to.
有关详细信息,请参阅章节 DAX Other functions 。
Refer to the chapter − DAX Other functions for details.
DAX Function Description Structure
如果您需要在 DAX 公式中使用 DAX 函数,那么您需要详细地了解该函数。您应当了解该函数的语法、参数类型、该函数返回的内容,等等。
If you have to use a DAX function in a DAX formula, you need to understand the function in detail. You should know the syntax of the function, the parameter types, what the function returns, etc.
本教程为所有 DAX 函数采用统一的函数描述结构,以便您能有效阅读和理解 DAX 函数。
In this tutorial, a common function description structure is used for all the DAX functions so that you can read and interpret the DAX functions effectively.
请参阅章节 − DAX Function Description Structure 了解详细信息。
Refer to the chapter − DAX Function Description Structure for details.
DAX Parameter Naming Conventions
DAX 拥有标准的参数名称来帮助使用和理解 DAX 函数。此外,您可以在参数名前使用特定前缀。如果前缀清晰易懂,您可以使用前缀本身作为参数名。
DAX has standard parameter names to facilitate the usage and understanding of the DAX functions. Further, you can use certain prefixes to the parameter names. If the prefix is clear enough, you can use the prefix itself as the parameter name.
在理解 DAX 函数的语法并正确使用数据值来获取相关的 DAX 函数参数之前,您需要理解 DAX 参数的命名规则。
To understand the syntax of the DAX functions and to use data values appropriately for the relevant DAX function parameters, you need to understand DAX parameter naming conventions.
Parameter Names
以下为 DAX 的标准参数名称:
Following are the DAX standard parameter names −
Sr.No. |
Parameter Name & Description |
1 |
expression Any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context). |
2 |
value Any DAX expression that returns a single scalar value where the expression is to be evaluated exactly once before all other operations. |
3 |
table Any DAX expression that returns a table of data. |
4 |
tableName The name of an existing table using standard DAX syntax. It cannot be an expression. |
5 |
columnName The name of an existing column using standard DAX syntax, usually fully qualified. It cannot be an expression. |
6 |
name A string constant that will be used to provide the name of a new object. |
7 |
order An enumeration used to determine the sort order. |
8 |
ties An enumeration used to determine the handling of tie values. |
9 |
type An enumeration used to determine the data type for PathItem and PathItemReverse. |
Prefixing Parameter Names or Using the Prefix Only
您可以用前缀来限定参数名称:
You can qualify a parameter name with a prefix −
-
The prefix should be descriptive of how the argument is used.
-
The prefix should be in such a way that ambiguous reading of the parameter is avoided.
例如,
For example,
-
Result_ColumnName − Refers to an existing column used to get the result values in the DAX LOOKUPVALUE () function.
-
Search_ColumnName − Refers to an existing column used to search for a value in the DAX LOOKUPVALUE () function.
如果前缀足够明确地描述参数,你就可以省略参数名称而仅使用前缀。有时,省略参数名称且仅使用前缀有助于避免在阅读期间产生杂乱。
You can omit the parameter name and use only the prefix, if the prefix is clear enough to describe the parameter. Omitting the parameter name and using only prefix can sometimes help in avoiding the clutter during reading.
例如,考虑 DATE (Year_value, Month_value, Day_value) 。你可以省略参数名称 - value(重复三次),并将其写为 DATE (Year, Month, Day)。正如所见,仅通过使用前缀,函数就更具可读性。但是,有时为了更清楚地理解,参数名称和前缀必须同时存在。
For example, Consider DATE (Year_value, Month_value, Day_value). You can omit the parameter name – value, that is repeated thrice and write it as DATE (Year, Month, Day). As seen, by using only the prefixes, the function is more readable. However, sometimes the parameter name and the prefix have to be present for clarity.
例如,考虑 Year_columnName 。参数名称是 ColumnName,前缀是 Year。两者同时存在是为了让用户理解此参数需要现有年份列的引用。
For example, Consider Year_columnName. The parameter name is ColumnName and the prefix is Year. Both are required to make the user understand that the parameter requires a reference to an existing column of years.
DAX Functions - Description Structure
如果您需要在 DAX 公式中使用 DAX 函数,那么您需要详细地了解该函数。您应当了解该函数的语法、参数类型、该函数返回的内容,等等。
If you have to use a DAX function in a DAX formula, you need to understand the function in detail. You should know the syntax of the function, the parameter types, what the function returns, etc.
为方便您理解如何阅读和解释 DAX 函数,本教程采用了统一的函数描述结构。
To enable you to understand how to read and interpret the DAX functions, a uniform function description structure is used in this tutorial.
-
The different types of DAX functions are grouped by the type name of the DAX functions as chapters.
-
Each of these chapters provides a brief description of the utility of the respective type of DAX functions.
-
The brief description will be followed by the list of DAX functions corresponding to that chapter (Type/Category of DAX functions).
-
Each DAX function name is hyperlinked to DAX function details that have the following DAX function description structure − DescriptionSyntaxParametersReturn ValueRemarksExample
以下部分说明每个 DAX 函数说明中出现的各个标题。
The following sections explain each of these headings that appear in each DAX function explanation.
Description
在说明部分,您将学习 DAX 函数是什么以及它可以在哪里使用。
In the Description section, you will learn what the DAX function is about and where it can be used.
如果 DAX 函数是在 Excel 2016 中引入的,这里也会提到。(其他 DAX 函数存在于 Excel 2013 中。)
If the DAX function is introduced in Excel 2016, the same will be mentioned here. (Rest of the DAX functions exist in Excel 2013.)
Syntax
在语法部分,你将了解确切的功能名称和相关参数。
In the Syntax section, you will learn the exact function name and the respective parameters.
-
DAX function name is given in UPPERCASE letters.
-
DAX function name is followed by opening parenthesis.
-
Each parameter follows standard DAX parameter naming convention and is enclosed in angle brackets.
-
If a parameter is optional, it is further enclosed in square brackets.
-
The parameters are separated by commas.
-
Ellipses … are used to show an optional number of repetitions of parameters.
-
The function syntax ends with closing parenthesis.
Parameters
在“参数”段中,特定 DAX 函数的每个参数在具有其说明的表中列出。例如,上述示例 DAX 函数 SELECTCOLUMNS 的参数列在下面的表中。
In the Parameters section, each of the parameters of the specific DAX function is listed in a table with its description. For example, the parameters of the above example DAX function SELECTCOLUMNS is listed in the following table.
Sr.No. |
Parameter & Description |
1 |
Table Table or a DAX expression that returns a table. |
2 |
Name The name given to the column, enclosed in double quotes. |
3 |
scalar_expression DAX expression that returns a scalar value like a column reference, integer, or string value. |
Return Value
在“返回值”部分,您将了解 DAX 函数将返回什么值及其数据类型。
In the Return Value section, you will learn about what value the DAX function will return and its data type.
Remarks
在“备注”部分,您将了解有关 DAX 函数用法的任何额外信息。您还将了解潜在错误及原因。
In the Remarks section, you will learn about any extra information that you need to know about the usage of the DAX function. You will also understand the potential errors and the reasons.
Example
本部分提供了 DAX 函数用法示例。
An example of the usage of the DAX function is given in this section.
Note − 当您编写具有参数数据值的 DAX 函数时,您将按照以下给出的命名约定 −
Note − When you write DAX functions with the data values for the parameters, you will follow the naming conventions as given below −
-
A Table name is specified as it appears in the Data Model. E.g. Sales.
-
A Column name is specified as it appears in the Data Model with square brackets enclosing it. For example, [Sales Amount] It is recommended to use fully qualified names for columns, i.e. a column name is prefixed with the table name that contains it. For example, Sales[Sales Amount] If the table name contains spaces, it should be enclosed in single quotes. For example, ‘East Sales’[Sales Amount]
-
A DAX function can return a column or table of values, in which case, it needs to be used as a parameter of another DAX function that requires a column or table.
DAX Functions - Aggregation
DAX 聚合函数汇总表各行中的任何表达式,并且在计算中很有用。
DAX Aggregation functions aggregate any expression over the rows of a table and are useful in calculations.
以下是 DAX 聚合函数 −
Following are the DAX Aggregation functions −
DAX Functions - Filter
你可以使用 DAX 筛选器函数来返回特定的数据类型、查找相关表格中的值以及根据相关值进行筛选。查找函数可以通过使用表格及其之间的关系来进行工作。筛选器函数使你可以处理数据上下文以创建动态计算。
You can use DAX Filter functions to return specific data types, look up values in related tables and filter by related values. Lookup functions work by using tables and relationships between them. Filter functions enable you to manipulate data context to create dynamic calculations.
以下是 DAX 筛选器函数
Following are the DAX Filter functions
DAX Functions - Time Intelligence
DAX 计时智能函数可帮助您创建计算,这些计算通过使用时间段(包括天、月、季度和年)操纵数据来满足商业智能分析需求。
DAX Time Intelligence functions help you create calculations that support the needs of Business Intelligence analysis by enabling you to manipulate data using time periods, including days, months, quarters and years.
以下是 DAX 计时智能函数
Following are the DAX Time Intelligence functions
DAX Functions - Date and Time
DAX 日期和时间函数类似于 Excel 的日期和时间函数。但是,DAX 的日期和时间函数基于 DAX datetime 的数据类型。
DAX Date and Time functions are similar to the Excel date and time functions. However, DAX date and time functions are based on the DAX datetime data type.
以下为 DAX 的日期和时间函数:
Following are the DAX Date and Time functions −
DAX Functions - Information
DAX 信息函数查看作为参数提供的值或列并告诉您该值是否与预期类型匹配。
DAX Information functions look at the value or column that is provided as an argument and tell you whether the value matches the expected type.
以下是 DAX 信息函数 −
Following are the DAX Information functions −
DAX Functions - Logical
DAX 逻辑函数根据对相关参数执行的逻辑运算返回逻辑值 (TRUE/FALSE)。
DAX Logical functions return logical values (TRUE/FALSE) based on the logical operations performed on the relevant parameters.
以下是 DAX 逻辑函数 -
Following are the DAX Logical functions −
Mathematical & Trigonometric Functions
DAX 数学和三角函数与 Excel 的数学和三角函数非常相似。
DAX Mathematical and Trigonometric functions are very similar to the Excel mathematical and trigonometric functions.
以下是 DAX 数学和三角函数 −
Following are the DAX Math and Trig functions −
DAX Functions - Parent and Child
DAX 父子函数用于管理以数据模型中父子层次结构呈现的数据。
DAX Parent and Child functions are useful in managing data that is presented as a parent/child hierarchy in the Data Model.
以下是 DAX 父项和子项函数 −
Following are the DAX Parent and Child functions −
DAX Functions - Statistical
DAX 统计函数与 Excel 统计函数非常相似。
DAX Statistical functions are very similar to Excel Statistical functions.
以下是 DAX 统计函数 -
Following are the DAX Statistical functions −
DAX Functions - Text
DAX 文本函数与表和列一起使用。使用 DAX 文本函数,你可以返回部分字符串,搜索字符串中的文本或连接字符串值。还可以控制日期、时间和数字的格式。
DAX Text functions work with tables and columns. With DAX Text functions, you can return a part of a string, search for text within a string, or concatenate string values. You can also control the formats for dates, times, and numbers.
以下是 DAX 文本函数 -
Following are the DAX Text functions −