# DAX 简介Introduction to DAX

## DAX 简介Introduction to DAX

DAX 代表数据分析表达式，它是在整个 Power BI 中使用（它也由 Power BI 在后台使用）的公式语言。DAX stands for Data Analysis Expressions, and it is the formula language used throughout Power BI (it is also used by Power BI behind the scenes). 在 Microsoft 的其他产品也能找到 DAX，如 Power Pivot 和 SSAS 表格，但此指导学习集合的主题重点介绍如何在 Power BI 中使用 DAX - 你将如何使用它。DAX is also found in other offerings from Microsoft, such as Power Pivot and SSAS Tabular, but this collection of Guided Learning topics focuses on how DAX is used - and can be used by you - in Power BI.

## 在本视频中 - DAX 简介In this video - introduction to DAX

DAX 概念简单明了，但其功能强大。DAX concepts are simple and straightforward, but DAX is powerful. DAX 使用一些独特的编程概念和模式，使其难以被充分利用和理解。DAX uses some unique programming concepts and patterns which can make it difficult to fully use and understand. 传统的学习语言的方法对于 DAX 来说可能不是最好的方法，因此本视频旨在向你讲解有关概念和理论，以便在你以后的 Power BI 工作中提供帮助。Traditional ways of learning languages may not be the best approach to DAX, so the goal of this video is to teach you concepts and theory that will help later on in your Power BI work.

DAX 是一种 函数语言 ，这意味着完整的执行代码包含在一个函数中。DAX is a functional language, which means the full executed code is contained inside a function.

DAX 的设计用于处理表格，因此它只有两个主要的数据类型：数字其他DAX is designed to work with tables, so it has just two primary data types: Numeric and Other. 数字可以包括整数、小数和货币。Numeric can include integers, decimals, and currency. 其他可以包括字符串和二进制对象。Other can include strings and binary objects. 这意味着如果构建 DAX 函数来处理一种类型的数字，那么可以确定该函数可以处理任何其他数字数据。This means that if you build your DAX function to work on one type of number, you can be assured that it will work on any other Numeric data.

DAX 使用运算符重载，这表示可以在计算中混合使用各种数据类型，其结果将根据输入中使用的数据类型进行更改。DAX uses operator overloading, which means that you can mix data types in your calculations and the results will change based on the type of data used in the inputs. 数据类型转换将自动发生。Conversion happens automatically. 这意味着你无需知道在 Power BI 中使用的列的数据类型，但它还意味着有时转换是以意想不到的方式进行的。This means you don't have to know the data types of the columns you're working with in Power BI, but it also means that sometimes conversion can happen in unexpected ways. 了解你使用的数据是一个不错的做法，这样可以确保运算符按照预期进行工作。It's good practice to understand the data you are using to ensure that your operators are behaving as anticipated.

Power BI 中可能大量使用的一种数据类型是：DateTimeThere is one data type in particular that you'll likely be working with a lot in Power BI: DateTime. DateTime 存储为浮点值，包括整数和小数部分。DateTime is stored as a floating point value with both integer and decimal parts. DateTime 可以用来精确计算 1900 年 3 月 1 日以后的任意时间段。DateTime can be used accurately for calculations of any time period after March 1, 1900.

## DAX 计算类型DAX calculation types

• 计算列calculated columns
• 计算度量值calculated measures

## DAX 表名和列名语法DAX table and column name syntax

``````'Table Name'[ColumnName]
``````

``````TableName[ColumnName]
``````

• 表名中无空格No spaces in table names
• 始终在公式中包含表名（不要将其省略掉，即使 DAX 允许）Always include the table name in formulas (don't omit it, even though DAX lets you)

## 创建计算列Creating calculated columns

• 新的列名a new column name
• 至少一个函数或表达式at least one function or expression

## 创建计算度量值Creating calculated measures

• 新的度量值名称a new measure name
• 至少一个函数或表达式at least one function or expression

## DAX 函数DAX functions

DAX 拥有许多可用于成形、组织或分析数据的函数。With DAX, there are many functions available to shape, form, or otherwise analyze your data. 这些函数可以分为以下几个类别：These functions can be grouped into a handful of categories:

• 聚合函数Aggregation functions
• 计数函数Counting functions
• 逻辑函数Logical functions
• 信息函数Information functions
• 文本函数Text functions
• 日期函数Date functions

Power BI 会显示出与你当前所键入的字母相匹配的函数，因此，如果仅键入 S，那么列表中就会显示出以 S 开头的函数。Power BI displays the functions that match the letters you've typed so far, so if you type S only functions that begin with S appear in the list. 如果键入 Su，则列表中会显示名称中包含字母序列 Su 的函数（不必以 Su 开头，只需包含该字母序列即可）。If you type Su, only functions that contain the letter sequence Su in their name appear in the list (they don't have to start with Su, they just have to contain that letter sequence).

## 聚合函数Aggregation functions

DAX 提供多种聚合函数，包括以下常用函数：DAX has a number of aggregation functions, including the following commonly used functions:

• SUMSUM
• AVERAGEAVERAGE
• MINMIN
• MAXMAX
• SUMX（以及其他 X 函数）SUMX (and other X functions)

## 计数函数Counting functions

DAX 中经常使用的计数函数包括：Often-used counting functions in DAX include the following:

• COUNTCOUNT
• COUNTACOUNTA
• COUNTBLANKCOUNTBLANK
• COUNTROWSCOUNTROWS
• DISTINCTCOUNTDISTINCTCOUNT

## 逻辑函数Logical functions

DAX 中的逻辑函数包括：The collection of logical functions in DAX include:

• ANDAND
• OROR
• NOTNOT
• IFIF
• IFERRORIFERROR

## 信息函数Information functions

DAX 中的信息函数包括：Information functions in DAX include:

• ISBLANKISBLANK
• ISNUMBERISNUMBER
• ISTEXTISTEXT
• ISNONTEXTISNONTEXT
• ISERRORISERROR

DAX 使用 MAXMIN 函数来聚合和比较值。DAX uses the MAX and MIN functions to both aggregate values, and to compare values.

## 文本函数Text functions

DAX 中的文本函数包括：The text functions in DAX include the following:

• CONCATENTATECONCATENTATE
• REPLACEREPLACE
• SEARCHSEARCH
• UPPERUPPER
• FIXEDFIXED

## 日期函数Date functions

DAX 包含以下日期函数：DAX includes the following Date functions:

• DATEDATE
• HOURHOUR
• NOWNOW
• EOMONTHEOMONTH
• WEEKDAYWEEKDAY

## 在 DAX 表达式中使用变量Using variables in DAX expressions

``````VARNAME = RETURNEDVALUE
``````

## 表关系和 DAXTable relationships and DAX

Power BI 可以让你在多个表（包括来自完全不同数据源的表）之间建立关系。Power BI lets you create relationships among multiple tables, including tables that come from completely different data sources. 你可以在 Power BI Desktop 的“关系”视图中看到任何数据模型的关系。You can see those relationships for any data model in the Relationships view of Power BI Desktop.

## DAX 关系函数DAX relational functions

DAX 具有让你可以与建立了关系的表进行互动的关系函数DAX has relational functions that enable you to interact with tables that have established relationships.

RELATED 函数处理多对一 的关系，而 RELATEDTABLE 函数处理 一对多 的关系。The RELATED function works on many-to-one relationships, while RELATEDTABLE is for one-to-many relationships.

## DAX 表和筛选DAX tables and filtering

DAX 和 Excel 公式语言的一个显著区别是 DAX 允许在表达式之间传递整个表，而不仅限于单个值。One significant difference between DAX and the Excel formula language is that DAX allows you to pass entire tables between expressions, rather than being constrained to a single value. DAX 的一项强大功能是允许你在其表达式中筛选表格，然后使用筛选的值集。One powerful effect is that DAX allows you to filter tables in its expressions, then work with the filtered set of values.

## DAX 表函数DAX table functions

DAX 提供一套丰富的函数，包括：DAX has a rich set of table functions, including the following:

• FILTERFILTER
• ALLALL
• VALUESVALUES
• DISTINCTDISTINCT
• RELATEDTABLERELATEDTABLE

``````FILTER (ALL (Table), Condition)
``````

DISTINCT 函数返回某一列的各个不同值，这些值在当前上下文中也可见。The DISTINCT function returns the distinct values of a column that are also visible in the current context. 因此，以上述 DAX 表达式为例，在表达式中使用 ALL 会忽略筛选，而使用 DISTINCT 替换 ALL 则可查看筛选。So touse the above DAX expression example, using ALL in that expression ignores filters, while replacing ALL with DISTINCT would observe them.

## 使用 DAX 对值进行计数Counting values with DAX

Power BI 报表生成人员想要回答的一个常见问题是：One common question that Power BI report builders want to answer is the following:

• 我可以对该列设置多少个值？How many values do I have for this column?