DAX 简介Introduction to DAX

向知名专家学习如何创建和使用 DAX。Learn how to create and work with DAX, from an established expert.

你将了解操作方式...

DAX 简介Introduction to DAX

欢迎使用 Power BI 指导学习部分,它旨在为你介绍 DAXWelcome to the Power BI Guided Learning section designed to introduce you 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 以及此指导学习视频系列DAX and this Guided Learning video series

指导学习部分的目标是教授 DAX 基础知识和基本原理 - 如何看待 DAX,它的工作原理,以及如一位知名的 DAX 专家 Alberto Ferrari 所解释(且凭借大量经验而学得)的那些最有用的功能。The goal of this Guided Learning section is to teach you DAX basics and fundamentals - how to think about DAX, how it works, and the most useful features as explained (and learned with lots of experience) by a renowned DAX expert, Alberto Ferrari.

Alberto Ferrari 的画像

在此指导学习部分中的关于 DAX 的视频将从 DAX 公式语言如何工作的视角来讲解 DAX 基础知识。The videos in this Guided Learning section on DAX teaches you DAX basics from the perspective of how the DAX formula language works. 如果是从零开始创建 DAX 公式时,这就很有用,而且这对于理解当你在查询编辑器中创建查询时 Power BI 如何创建这些 DAX 公式也非常有用。This is useful when creating DAX formulas from scratch, but it's also very useful for understanding how Power BI creates those DAX formulas as you create queries in the Query Editor.

在本视频中 - 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 中,函数可以包含其他内容,例如嵌套函数、条件语句和值引用。In DAX, functions can contain other, nested functions, conditional statements, and value references. DAX 中的执行从最内部函数或参数开始,逐步向外计算。Execution in DAX starts from the innermost function or parameter, and works outward. 在 Power BI 中,DAX 公式在单个行中编写,因此函数的正确格式设置对于可读性很重要。In Power BI, DAX formulas are written in a single line, so formatting your functions correctly is important for readability.

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.

视频内容由 Alberto Ferrari, SQLBI 提供Video content courtesy of Alberto Ferrari, SQLBI

DAX 计算类型DAX calculation types

你可以使用 DAX 创建的两个主要的计算:There are two primary calculations you can create using DAX:

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

在深入探讨创建上述二者之一前,最好牢固掌握用于表和列的 DAX 语法,你将在创建计算列计算度量值时使用它。Before digging into creating either of those, it's good to have a firm grasp on DAX syntax for tables and columns, which you will use when creating either calculated columns or calculated measures.

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

无论你要创建新的列还是度量值,都要务必了解 DAX 中的表名的一般格式:Whether you're creating a new column or measure, it's important to know the general format of table names in DAX:

'Table Name'[ColumnName]

如果表名(如上所示)中有空格,那么表名周围的单引号则是必需的。If there are spaces in the table name (as shown above), the single quotes around the table name are mandatory. 如果表名没有空格,则可以省略单引号,因此语法如下所示:If the table name has no spaces, the single quotes can be omitted, so the syntax looks like the following:

TableName[ColumnName]

下图显示了在 Power BI 中创建的一个 DAX 公式:The following image shows a DAX formula being created in Power BI:

你也可以完全省略表名而只使用列名,但这对于写入清晰的函数(从而,对于清晰的 DAX 代码)来说不是一个好的做法。You can also omit the table name completely and just use the column name, but this is poor practice for writing clear functions (and thus, for clear DAX code). 列名称必须始终包含方括号。Column names must always include the square brackets.

最佳做法是始终执行以下操作:It's best practice to always do the following:

  • 表名中无空格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

当你要划分或筛选值,或者要对表中的每一行进行计算时,计算列非常有用。Calculated columns are useful when you want to slice or filter on the value, or if you want a calculation for every row in your table.

通过从“建模”选项卡选择“新建列”,你可以在 Power BI Desktop 中创建计算列。最好采用“数据”视图(而不是“报表”或“关系”视图),因为你可以看到创建的新列以及“编辑栏”填充并准备好用于 DAX 公式。You can create calculated columns in Power BI Desktop by selecting New Column from the Modeling tab. It's best to be in Data view (rather than Report or Relationships view), since you can see the new column created and the Formula Bar is populated and ready for your DAX formula.

一旦你选择“新建列”按钮,“编辑栏”将填充基本列名(当然,你可以更改以使其适合你的公式)和 = 运算符,新列将显示在数据网格中,如下图所示。Once you select the New Column button, the Formula Bar is populated with a basic column name (which you change to suit your formula, of course) and the = operator, and the new column appears in the data grid, as shown in the following image.

计算列所需的元素如下:The required elements for a calculated column are the following:

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

如果在计算列公式中引用一个表或列,则无需在表中指定行 -- Power BI 会为每个计算的当前行计算列。If you reference a table or column in your calculated column formula, you do not need to specify a row in the table - Power BI calculates the column for the current row for each calculation.

创建计算度量值Creating calculated measures

当你计算百分比或比率,或者需要复杂的聚合时,使用计算度量值Use a calculated measure when you are calculating percentages or ratios, or you need complex aggregations. 若要创建使用 DAX 公式的度量值,请从“建模”选项卡选择“新建度量值”按钮。同样,最好采用 Power BI Desktop 的“数据”视图,因为它显示“编辑栏”并使写入 DAX 公式更加容易。To create a measure using a DAX formula, select the New Measure button from the Modeling tab. Again, it's best to be in the Data view of Power BI Desktop since it shows the Formula Bar and makes it easy to write your DAX formula.

对于度量值,你会看到一个有度量值名称的新的度量值图标出现在“字段”窗格中。With measures, you see a new measure icon appear in the Fields pane with the name of the measure. “编辑栏”再次被 DAX 公式名称(这次,带有度量值)填充。The Formula Bar is again populated with the name of your DAX formula (this time, with your measure).

计算度量值的必需元素与计算列的必需元素是相同的:The required elements for a calculated measure are the same as they are for a calculated column:

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

视频内容由 Alberto Ferrari, SQLBI 提供Video content courtesy of Alberto Ferrari, SQLBI

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

与 Excel 类似,当开始向 Power BI Desktop 公式栏键入公式时,会显示可用函数列表,以帮助你确定要选择的可用函数。Similar to Excel, when you start typing your formula into the Power BI Desktop Formula Bar, a list of available functions appears to help you determine which available function you want to select. 通过使用键盘上的向上向下箭头键,可以突出显示任何可用函数,有关该函数的简要说明将会显示出来。And by using the up and down arrow keys on your keyboard, you can highlight any of the available functions, and a brief description is displayed.

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).

通过这种方式很容易试用 DAX 以及查找 Power BI 中可用的各种 DAX 函数。It's easy to experiment with DAX in this way, and to find each of the various DAX functions that are available in Power BI. 你所要做的就是开始键入,Power BI 会全程帮助你完成。All you have to do is start typing, and Power BI helps you along.

知道了如何开始使用 DAX 公式后,让我们来依次看看各个函数类别。Now that we know how to get that DAX formula started, let's take a look at each of these function categories in turn.

聚合函数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)

这些函数仅适用于数字列,并通常一次只能聚合一列。These functions work only on numeric columns, and generally can aggregate only one column at a time.

但是以 X 结尾的特殊聚合函数(例如 SUMX 则可同时处理多列。However, special aggregation functions that end in X, such as SUMX, can work on multiple columns. 这些函数循环访问表,并为每一行计算表达式。These functions iterate through the table, and evaluate the expression for each row.

计数函数Counting functions

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

  • COUNTCOUNT
  • COUNTACOUNTA
  • COUNTBLANKCOUNTBLANK
  • COUNTROWSCOUNTROWS
  • DISTINCTCOUNTDISTINCTCOUNT

这些函数用来计数不同的元素,如非重复值、非空值和表行。These functions count different elements, such as distinct values, non-empty values, and table rows.

逻辑函数Logical functions

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

  • ANDAND
  • OROR
  • NOTNOT
  • IFIF
  • IFERRORIFERROR

这些特殊函数还可以用运算符表达。These special functions can also be expressed with operators. 例如,在 DAX 公式中 AND 可以输入为(替换为)&&For example, AND can be typed as (replaced with) && in your DAX formula.

如果公式中存在两个以上条件,则可以使用运算符(如 &&),但在其他情况最好使用函数名本身(如 AND),以增强 DAX 代码可读性。You can use operators (such as &&) when you need more than two conditions in your formula, but otherwise, it's best practice use the function name itself (such as AND) for readability of your DAX code.

信息函数Information functions

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

  • ISBLANKISBLANK
  • ISNUMBERISNUMBER
  • ISTEXTISTEXT
  • ISNONTEXTISNONTEXT
  • ISERRORISERROR

尽管这些函数在具体情况下有用,但提前知道列的数据类型,而不依赖这些函数来提供数据类型仍很有价值。While these functions can be situationally useful, there is value in knowing the data type of your columns ahead of time, rather than depending on these functions to provide the data type.

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

这些文本函数与同名的 Excel 函数工作方式类似,因此,如果熟悉 Excel 如何处理文本函数,你就已经领先一步了。These text work very similarly to the Excel functions that have the same name, so if you're familiar with how Excel handles text functions, you're already a step ahead. 如果不熟悉,则可以一直在 Power BI 上试用这些函数,以了解它们的详细行为方式。If not, you can always experiment with these functions in Power BI, and learn more about how they behave.

日期函数Date functions

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

  • DATEDATE
  • HOURHOUR
  • NOWNOW
  • EOMONTHEOMONTH
  • WEEKDAYWEEKDAY

尽管这些函数对于从日期值中计算和提取信息很有用,但它们并不适用于使用日期表的时间智能。While these functions are useful to calculate and extract information from date values, they do not apply to time intelligence, which uses a date table.

视频内容由 Alberto Ferrari, SQLBI 提供Video content courtesy of Alberto Ferrari, SQLBI

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

使用变量是 DAX 表达式的功能非常强大的组成部分。Using variables are an extremely powerful part of a DAX expression.

你可以使用以下语法在 DAX 表达式的任意位置定义一个变量:You can define a variable anywhere in a DAX expression, using the following syntax:

VARNAME = RETURNEDVALUE

变量可以是任何数据类型,包括整个表。Variables can be any data type, including entire tables.

请记住,每次在 DAX 表达式中引用变量时,Power BI 必须根据定义重新计算它的值。Keep in mind that each time you reference a variable in your DAX expression, Power BI must recalculate its value according to your definition. 因此,在函数中避免使用重复变量是一个好做法。For this reason, it's good practice to avoid repeating variables in your function.

视频内容由 Alberto Ferrari, SQLBI 提供Video content courtesy of Alberto Ferrari, SQLBI

表关系和 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.

你可以返回列值,或者使用 DAX 函数返回某一关系中的所有行。You can return the value of a column, or you can return all rows in a relationship using DAX functions.

例如,TABLE 函数依照关系返回列值,而 RELATEDTABLE 依照关系返回经过筛选的只包含相关行的整个表。For example, the TABLE function follows relationships and returns the value of a column, while RELATEDTABLE follows relationships, and returns an entire table that is filtered to include only related rows.

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

你可以使用关系函数生成包含跨多个表的值的表达式。You can use relational functions to build expressions that include values across multiple tables. DAX 将返回这些函数的一个结果,而不会考虑关系链的长度。DAX will return a result with these functions, regardless of the length of the chain of the relationship.

视频内容由 Alberto Ferrari, SQLBI 提供Video content courtesy of Alberto Ferrari, SQLBI

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,你可以创建全新的计算表,然后像处理其他表格一样处理它们 - 包括在这些表和存在于你的数据模型中的其他表之间建立关系。With DAX, you can create entirely new calculated tables and then treat them like any other table - including creating relationships between them and other tables in your data model.

DAX 表函数DAX table functions

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

  • FILTERFILTER
  • ALLALL
  • VALUESVALUES
  • DISTINCTDISTINCT
  • RELATEDTABLERELATEDTABLE

这些函数返回一个完整的表,而不是一个值。These functions return a full table rather, rather than a value. 通常,你会在进一步的分析中将函数的结果(而不是返回的一个最终值)作为更大的表达式的一部分使用。Typically you'll use the results of a table function in further analysis as part of a greater expression, rather than using that returned table a final value. 值得注意的是,使用表函数时,其结果将继承其列的关系。It's important to note that When you use a table function, the results inherit the relationships of their columns.

可以在表达式中混合使用表函数,前提是每一个表达式只使用一个表并返回一个表。You can mix table functions in your expression, as long as each function uses a table and returns a table. 例如下面的 DAX 表达式:For example, consider the following DAX expression:

FILTER (ALL (Table), Condition)

该表达式将筛选整个 ,而忽略当前筛选的任何内容。That expression would put a filter over the entirety of Table, ignoring any current filter content.

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?

如果你面前出现一个表,那么这是很好回答的简单问题,但是 DAX 会采用其他方式来计算该值,尤其是在表之间存在关系时。That may be a simple question to answer with a table displayed in front of you, but DAX approaches in a different way in a different way, particularly when there's a relationship between tables.

例如,Power BI 和 DAX 包含未正确建立交叉索引的值。For example, Power BI and DAX includes values that are not properly cross-indexed. 如果传入关系已损坏,DAX 会将新行添加到每个字段都有空值的相关表中,并将其链接到未建立索引的行,以确保引用的完整性。If the incoming relationship is broken, DAX adds a new row to the related table that has blanks in every field, and links that new row to the unindexed row to guarantee referential integrity. 如果函数包含空白行(这在使用 ALL 时经常出现),那么列的返回值数目中将包含这些空白行。If your function includes blank rows, such as is often the case when using ALL, those blank rows will then be included in the number of values returned for that column.

你还可以使用 DAX 函数创建整个计算表。You can also create entire calculated tables using DAX functions. 使用 DAX 创建的计算表要求有一个名称和一个函数。Calculated tables created using DAX require a NAME and a TABLE function. 计算的表的使用,包括建立关系,和任何其他表一样。Calculated tables can be used like any other table, including establishing relationships.

视频内容由 Alberto Ferrari, SQLBI 提供Video content courtesy of Alberto Ferrari, SQLBI

恭喜!

现已完成 Power BI 引导式学习教程的“DAX 简介”部分。You've completed the Introduction to DAX section of Power BI Guided Learning. 非常精彩的学习之旅!What a journey! 如果你已按顺序学习了这些教程,则已完成引导式学习的所有部分。If you've done these tutorials in sequence, then you've finished all the sections of Guided Learning. 我们继续!Way to go! 现在,可以使用 Power BI 来创建精致的报表和仪表板来讲述自己的经历。Now go use Power BI to create compelling reports and dashboards that tell your story.

你已了解操作方式...

作者

  • Davidiseminger
  • olprod
  • Alisha-Acharya