# Power BI Desktop 中的 DAX 基本概念DAX basics in Power BI Desktop

## DAX 是什么？What is DAX?

DAX 是公式或表达式中可用于计算并返回一个或多个值的函数、运算符或常量的集合。DAX 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 可帮助你通过模型中已有的数据来创建新信息。Stated more simply, DAX helps you create new information from data already in your model.

## 现在就开始吧！Let's begin!

### 语法Syntax

A.A. 度量值名称 Total SalesThe measure name Total Sales.

B.B. 等号运算符 (=) 表示公式的开头。The equals sign operator (=) indicates the beginning of the formula. 完成计算后将会返回结果。When calculated, it will return a result.

C.C. DAX 函数 SUM 会将 Sales[SalesAmount] 列中的所有数字相加。The DAX function SUM adds up all of the numbers in the Sales[SalesAmount] column. 稍后你将了解有关函数的详细信息。You’ll learn more about functions later.

D.D. 括号 () 会括住包含一个或多个参数的表达式。Parenthesis () surround an expression containing one or more arguments. 所有函数都至少需要一个参数。All functions require at least one argument. 一个参数会传递一个值给函数。An argument passes a value to a function.

E.E. 引用的表 SalesThe referenced table Sales.

F.F. Sales 表中的引用列 [SalesAmount]The referenced column [SalesAmount] in the Sales table. 使用此参数，SUM 函数就知道在哪一列上进行聚合求和。With this argument, the SUM function knows on which column to aggregate a SUM.

### 任务：创建度量值公式Task: Create a measure formula

1. 在“报表”视图的字段列表中，右键单击 Sales 表，然后单击新度量值In Report view, in the field list, right-click on the Sales table, and then click New Measure.

2. 在编辑栏中，通过键入新的度量值名称 Previous Quarter Sales 来替换度量值In the formula bar, replace Measure by typing a new measure name, Previous Quarter Sales.

3. 在等号后键入 SUM，其后紧跟左括号。After the equals sign, type SUM followed by an opening parenthesis.

我们将输入另一个函数来筛选想要求和的数据，而不是立即键入列名来求和。Rather than type a column name to sum up right away, we’re going to enter another function, to filter the data we want to sum up.

4. 在括号中，键入 CALCULATE，其后紧跟左括号。Between the parentheses, type CALCULATE, followed by an opening parenthesis.

你将通过我们传递给 CALCULATE 函数的参数，使用 CALCULATE 函数来筛选要求和的金额。You’ll use the CALCULATE function to filter the amounts we want to sum by an argument we pass to the CALCULATE function. 这就是所谓的嵌套函数。This is what’s referred to as nesting functions. CALCULATE 函数至少有两个参数。The CALCULATE function has at least two arguments. 第一个参数是要计算的表达式，第二个参数是筛选器。The first is the expression to be evaluated, and second, a filter.

5. CALCULATE 函数的括号 () 中，键入 Sales[SalesAmount]Between the parenthesis () for the CALCULATE function, type Sales[SalesAmount]. 这是 CALCULATE 函数的第一个表达式参数。This is the first expression argument for our CALCULATE function.

6. 键入逗号 (“,”) 来指定第一个筛选器，然后键入“PREVIOUSQUARTER”，其后紧跟左括号。Type a comma (,) to specify the first filter, then type PREVIOUSQUARTER followed by an opening parenthesis..

你将使用 PREVIOUSQUARTER 时间智能函数按上一季度来筛选求和结果。You’ll use the PREVIOUSQUARTER time intelligence function to filter our SUM results by the previous quarter.

7. 在 PREVIOUSQUARTER 函数的括号 () 中，键入“Calendar[DateKey]”。Between the parenthesis () for the PREVIOUSQUARTER function, type Calendar[DateKey].

PREVIOUSQUARTER 函数有一个参数，即包含连续日期范围的列。The PREVIOUSQUARTER function has one argument, a column containing a contiguous range of dates. >

8. 请确保传递给 PREVIOUSQUARTER 和 CALCULATE 函数的两个自变量都后跟两个右括号 ))Make sure both the arguments being passed to the PREVIOUSQUARTER function and the CALCULATE function are closed by two closing parentheses )).

该公式现在应如下所示：Your formula should now look like this:

Previous Quarter Sales = CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSQUARTER(Calendar[DateKey]))Previous Quarter Sales = CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSQUARTER(Calendar[DateKey]))

9. 单击公式栏中的复选标记 或按 Enter 键，验证公式并将其添加到模型中。Click the checkmark in the formula bar or press Enter to validate the formula and add it to the model.

### 语法快速测验Syntax QuickQuiz

1. 编辑栏上这个按钮的功能是什么？What does this button on the formula bar do?

2. 一律会用什么括住 DAX 公式中的列名？What always surrounds a column name in a DAX formula?

### 函数Functions

DAX 包括以下函数类别：日期和时间函数、时间智能函数、信息函数、逻辑函数、数学函数、统计函数、文本函数、父/子函数和其他函数。DAX includes the following categories of functions: Date and Time, Time Intelligence,Information, Logical,Mathematical, Statistical, Text, Parent/Child and Other functions. 如果你熟悉 Excel 公式中的函数，那么 DAX 中的很多函数都会让你觉得相似；但是，DAX 函数在以下方面是独一无二的：If you’re familiar with functions in Excel formulas, many of the functions in DAX will appear similar to you; however, DAX functions are unique in the following ways:

• DAX 函数始终引用完整列或表。A DAX function always references a complete column or a table. 如果你仅想使用某个表或列中的特定值，则可以向公式添加筛选器。If you want to use only particular values from a table or column, you can add filters to the formula.
• 如果需要逐行自定义计算，DAX 提供可让你将当前行值或相关值用作一种参数的函数，以便执行因上下文而变的计算。If you need to customize calculations on a row-by-row basis, DAX provides functions that let you use the current row value or a related value as a kind of argument, to perform calculations that vary by context. 稍后你将了解有关上下文的详细信息。You will learn more about context later.
• DAX 包括许多会返回表而非值的函数。DAX includes many functions that return a table rather than a value. 表不会显示出来，但可以将其用于提供其他函数的输入。The table is not displayed, but is used to provide input to other functions. 例如，你可以检索表，然后计算其中的非重复值，或者计算所筛选的表或列的动态总和。For example, you can retrieve a table and then count the distinct values in it, or calculate dynamic sums across filtered tables or columns.
• DAX 包括各种时间智能函数。DAX includes a variety of time intelligence functions. 这些函数可让你定义或选择日期范围，并基于此范围执行动态计算。These functions let you define or select date ranges, and perform dynamic calculations based on them. 例如，你可以比较并行时间段内的总和。For example, you can compare sums across parallel periods.
• Excel 有一个非常热门的函数 VLOOKUP。Excel has a very popular function, VLOOKUP. 不同于 Excel 中的 VLOOKUP，DAX 函数不会采用单元格或单元格区域作为引用。DAX functions don’t take a cell or cell range as a reference like VLOOKUP does in Excel. DAX 函数采用某一列或表作为引用。DAX functions take a column or a table as a reference. 请记住，在 Power BI Desktop 中，你会使用关系数据模型。Keep in-mind, in Power BI Desktop, you’re working with a relational data model. 查找另一个表中的值其实非常简单，而且在大多数情况下，完全不需要创建任何公式。Looking up values in another table is really quite easy, and in most cases you don’t need to create any formula at all.

如你所见，DAX 中的函数可帮助你创建功能非常强大的公式。As you can see, functions in DAX can help you create very powerful formulas. 我们实际上只接触到了函数的基本概念。We really only touched on the basics of functions. 随着你对 DAX 技能的熟悉，你将使用许多不同的函数来创建公式。As your DAX skills grow, you'll create formulas using many different functions. 若要了解有关每个 DAX 函数的详细信息，最好的办法之一就是参阅DAX 函数参考One of the best places to learn details about each of DAX functions is in the DAX Function Reference.

### 函数快速测验Functions QuickQuiz

1. 函数会始终引用何项？What does a function always reference?
2. 一个公式是否可以包含多个函数？Can a formula contain more than one function?
3. 可以使用哪种函数类别来将两个文本字符串连接成一个字符串？What category of functions would you use to concatenate two text strings into one string?

### 上下文Context

A.A. 度量值名称 Store SalesThe measure name Store Sales.

B.B. 等号运算符 (=) 表示公式的开头。The equals sign operator (=) indicates the beginning of the formula.

C.C. CALCULATE 函数会在根据指定筛选器所修改的上下文中，作为参数来计算表达式。The CALCULATE function evaluates an expression, as an argument, in a context that is modified by the specified filters.

D.D. 括号 () 会括住包含一个或多个参数的表达式。Parenthesis () surround an expression containing one or more arguments.

E.E. 同一表中作为表达式的 [Total Sales] 度量值。A measure [Total Sales] in the same table as an expression. Total Sales 度量值的公式为：=SUM(Sales[SalesAmount])。The Total Sales measure has the formula: =SUM(Sales[SalesAmount]).

F.F. 逗号 (,) 会分隔第一个表达式参数和筛选参数。A comma (,) separates the first expression argument from the filter argument.

G.G. 完全限定的引用列为 Channel[ChannelName]The fully qualified referenced column, Channel[ChannelName]. 这是我们的行上下文。This is our row context. 此列中的每行各指定一个通道：Store、Online 等。Each row in this column specifies a channel: Store, Online, etc.

H.H. 将特定值 Store 作为筛选器。The particular value, Store as a filter. 这是我们的筛选上下文。This is our filter context.

### 上下文快速测验Context QuickQuiz

1. 上下文有哪两种类型？What are the two types of context?
2. 什么是筛选上下文？What is filter context?
3. 什么是行上下文？What is row context?

## 摘要Summary

DAX 在 Power Pivot 和 Analysis Services 表格模型等其他 Microsoft BI 工具中已存在数年，因此有许多有用信息。DAX has been around for several years in other Microsoft BI tools such as Power Pivot and Analysis Services Tabular models, so there’s a lot of great information out there. 你可以从 Microsoft 和顶级 BI 专业人员所提供的书籍、白皮书和博客中找到详细信息。You can find more information in books, whitepapers, and blogs from both Microsoft and leading BI professionals. TechNet 上的 DAX 资源中心 Wiki 也是一个不错的起点。The DAX Resource Center Wiki on TechNet is also a great place to start.

1. 验证度量值并将其输入模型中。Validates and enters the measure into the model.
2. 方括号 []。Brackets [].

1. 表和列。A table and a column.
2. 是的。Yes. 公式可以包含多达 64 个嵌套函数。A formula can contain up to 64 nested functions.
3. 文本函数

1. 行上下文和筛选上下文。Row context and filter context.
2. 计算中用于确定单个值的一个或多个筛选器。One or more filters in a calculation that determines a single value.
3. 当前行。The current row.