使用 Power BI Desktop 中的计算列Using calculated columns in Power BI Desktop

凭借计算列,你可以将新数据添加到模型中已存在的表。With calculated columns, you can add new data to a table already in your model. 但请勿从数据源查询并将值加载到新列中,而是创建用于定义列值的数据分析表达式 (DAX) 公式。But instead of querying and loading values into your new column from a data source, you create a Data Analysis Expressions (DAX) formula that defines the column’s values. 在 Power BI Desktop 中,使用“报表”视图中的“新建列”功能创建计算列。In Power BI Desktop, calculated columns are created by using the New Column feature in Report View.

与使用查询编辑器中的“添加自定义列”创建为查询的一部分的自定义列不同,在报表视图或数据视图中创建的计算列以你已加载到模型中的数据为基础。Unlike custom columns created as part of a query by using Add Custom Column in Query Editor, calculated columns created in Report View or Data View are based on data you’ve already loaded into the model. 例如,你可以选择联接两个不同但相关的表中的值、执行添加或提取子字符串。For example, you might choose to concatenate values from two different columns in two different but related tables , perform addition, or extract sub-strings.

像任何其他字段一样,你创建的计算列将显示在“字段”列表中,但它们将带有特殊图标,显示其值为公式的结果。Calculated columns you create appear in the Fields list just like any other field, but they’ll have a special icon showing its values are the result of a formula. 你可以随意对列进行命名,并将其像其他字段一样添加到报表可视化效果。You can name your columns whatever you want, and add them to a report visualization just like other fields.

计算列使用数据分析表达式 (DAX) 计算结果,该表达式是一个旨在处理关系数据(如 Power BI Desktop 中的)的公式语言。Calculated columns calculate results by using Data Analysis Expressions (DAX), a formula language meant to work with relational data like in Power BI Desktop. DAX 包括一个超过 200 个函数、运算符和构造的库,在创建公式时提供巨大的灵活性,可以计算几乎任何数据分析需求的结果。DAX includes a library of over 200 functions, operators, and constructs, providing immense flexibility in creating formulas to calculate results for just about any data analysis need. 若要了解有关 DAX 的详细信息,请参阅本文末尾处的“了解详细信息”部分。To learn more about DAX, see the Learn more section at the end of this article.

DAX 公式类似于 Excel 公式。DAX formulas are similar to Excel formulas. 事实上,DAX 有着许多与 Excel 相同的功能。In fact, DAX has many of the same functions as Excel. 但是,DAX 函数旨在处理交互式地切片或筛选的报表中的数据,例如 Power BI Desktop 中的数据。DAX functions, however, are meant to work over data interactively sliced or filtered in a report, like in Power BI Desktop. 与 Excel 不同的是,在 Excel 中,你可以在表中每行使用不同公式;而为新列创建 DAX 公式时,它将为表中每一行计算结果。Unlike Excel, where you can have a different formula for each row in a table, when you create a DAX formula for a new column, it will calculate a result for every row in the table. 将在必要时(例如刷新基础数据或更改值时)重新计算列值。Column values are recalculated as necessary, like when the underlying data is refreshed and values have changed.

我们来看一个示例Let’s look at an example

Jeff 是 Contoso 的一名货运经理。Jeff is a shipping manager at Contoso. 他想创建一个报表,显示去往不同城市的货运数量。He wants to create a report showing the number of shipments to different cities. 他有包含城市和州分隔字段的“地理”表。He has a Geography table with separate fields for city and state. 但是,Jeff 希望他的报表能够将“城市,州”作为单个值显示在同一行。But, Jeff wants his reports to show City, State as a single value on the same row. 现在,Jeff 的“地理”表中没有他想要的字段。Right now, Jeff’s Geography table doesn’t have the field he wants.

但凭借计算列,Jeff 可以简单地将来自“城市”列的城市与来自“州”列的州组合或连接起来。But with a calculated column, Jeff can simply put together, or concatenate, the cities from the City column with the states from the State column.

Jeff 右键单击“地理”表,然后单击“新建列”。Jeff right clicks on the Geography table and then clicks New Column. 然后他在公式栏中输入以下 DAX 公式:He then enters the following DAX formula into the formula bar:

此公式只需创建名为 CityState 的新列,对于地理表中的每一行,取“城市”列的值,添加逗号和空格,然后连接“州”列的值。This formula simply creates a new column named CityState, and for each row in the Geography table, it takes values from the City column, adds a comma and a space, and then concatenates values from the State column.

现在,Jeff 就有了他想要的字段。Now Jeff has the field he wants.

他可以将它与货运数量一起添加到他的报表画布。He can add it to his report canvas along with the number of shipments. 非常快而且不费吹灰之力,Jeff 此刻便拥有了“城市,州”字段。Very quickly and with minimal effort, Jeff now has a City, State field. 他可以添加几乎任何类型的可视化效果。He can add to just about any type of visualization. Jeff 甚至能够看到,当他创建地图可视化时,Power BI Desktop 甚至知道如何读取新建列中的“州,城市”值。Jeff even sees that when he creates a map visualization, Power BI Desktop even knows how to read the City, State values in his new column.

了解详细信息Learn more

我们在此仅提供了关于计算列的快速介绍。We’ve only provided a quick introduction to calculated columns here. 请务必参阅在 Power BI Desktop 中创建计算列教程,在其中可以下载示例文件并获取有关如何创建更多列的分步课程。Be sure to see the Create calculated columns in Power BI Desktop tutorial, where you can download a sample file and get step-by-step lessons on how to create more columns.

若要了解有关 DAX 的详细信息,请参阅 Power BI Desktop 中的 DAX 基本概念To learn more about DAX, see DAX basics in Power BI Desktop.

若要了解有关作为查询的一部分创建的列的详细信息,请参阅 Power BI Desktop 中的常见查询任务中的“创建自定义列”部分。To learn more about columns you create as part of a query, see the Create custom columns section in Common query tasks in Power BI Desktop.