创建 Power BI Desktop 中的计算列Create 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 that are created as part of a query by using Add Custom Column in Query Editor, calculated columns that are 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, do addition, or extract substrings.

像任何其他字段一样,你创建的计算列将显示在“字段”列表中,但它们将带有特殊图标,显示其值为公式的结果。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.

Power BI Desktop 的屏幕截图,其中显示了“字段”视图中的已计算的列。

计算列使用 DAX 计算结果,该表达式是一个旨在处理关系数据(如 Power BI Desktop 中的)的公式语言。Calculated columns calculate results by using 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. 它为创建公式提供了巨大的灵活性,几乎可以计算任何数据分析所需的结果。It provides immense flexibility in creating formulas to calculate results for just about any data analysis need. 若要了解有关 DAX 的详细信息,请参阅 Power BI Desktop 中的 DAX 基本概念To learn more about DAX, see DAX basics in Power BI Desktop.

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 中,可以为表中的每行使用不同的公式。In Excel, you can have a different formula for each row in a table. 在 Power BI 中,为新列创建 DAX 公式时,它会为表中的每行计算结果。In Power BI, 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, and wants to create a report showing the number of shipments to different cities. 他有包含城市和州分隔字段的“地理”表。Jeff has a Geography table with separate fields for city and state. 但是,Jeff 希望他的报表能够将城市和州值作为单个值显示在同一行。But, Jeff wants their reports to show the city and state values as a single value on the same row. 现在,Jeff 的“Geography”表中没有所需字段。Right now, Jeff's Geography table doesn't have the wanted field.

Power BI Desktop 的屏幕截图,其中显示了“字段”视图中的“地理”筛选器。

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

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

Power BI Desktop 的屏幕截图,其中显示了 DAX 公式输入。

此公式只创建名为 CityState 的新列。This formula simply creates a new column named CityState. 对于“地理”表中的每行,取“城市”列的值,添加逗号和空格,然后连接“州”列的值。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 wanted field.

Power BI Desktop 的屏幕截图,其中显示了“字段”视图的“地理”筛选器中已勾选的“CityState”。

现在 Jeff 可以将它与货运数量一起添加到报表画布中。Jeff can now add it to the report canvas along with the number of shipments. 仅需一点努力,Jeff 现在有了“CityState”字段,可以将其添加到几乎任何类型的可视化效果中。With minimal effort, Jeff now has a CityState field that can be added to just about any type of visualization. 在 Jeff 创建新地图时,Power BI Desktop 已经知道如何读取新建列中的城市和州值。When Jeff creates a new map, Power BI Desktop already knows how to read the city and state values in the new column.

Power BI Desktop 的屏幕截图,其中显示了“地图”视觉对象中显示的数据。

后续步骤Next steps

我们在此仅提供了关于计算列的简要介绍。We've only provided a quick introduction to calculated columns here. 有关详细信息,请参阅下列资源:For more information, check out the following resources: