教程:创建 Power BI Desktop 中的计算列Tutorial: Create calculated columns in Power BI Desktop

有时,当前分析的数据不包含获取所期望结果时所需的特定字段。Sometimes the data you’re analyzing just doesn’t contain a particular field you need to get the results you’re after. 这就是计算列的有用之处。This is where calculated columns come in. 计算列使用数据分析表达式 (DAX) 公式来定义列的值。Calculated columns use Data Analysis Expressions (DAX) formulas to define a column’s values. 这些值可以是几乎任何内容,无论它是将来自其他位置的几个不同列的组合中的文本值都放到模型中,或是计算来自其他值的数字值。Those values can be just about anything, whether it be putting together text values from a couple of different columns elsewhere in the model, or calculating a numeric value from other values. 例如,假设你的数据中含有“城市”和“州”两个列(作为字段列表中的字段),但你想要使同时含有这两个列的单一“位置”字段成为一个单值,如“迈阿密,佛罗里达州”。For example, let’s say your data has a City and State columns (as fields in the Fields list), but you want a single Location field that has both as a single value, like Miami, FL. 这正是计算列的作用。This is precisely what calculated columns are for.

计算列类似于度量值,因为二者都基于 DAX 公式,但它们的区别在于如何使用。Calculated columns are similar to measures in that both are based on a DAX formula, but they differ in how they are used. 度量值最常用于可视化的“值”区域,用来计算基于你在表中有行的其他字段的结果,或用于可视化的“轴”、“图例”或“组”区域中。Measures are most often used in the Values area of a visualization, to calculate results based on other fields you have on a row in a table, or in an Axis, Legend, or Group area of a visualization. 另一方面,计算列用于当你希望列的结果在此表的某一行中,或在“轴”、“图例”或“组”区域中。Calculated columns on the other hand are used when you want the column’s results on that row in the table, or in the Axis, Legend, or Group area.

本教程将指导你了解并在 Power BI Desktop 中创建一些你自己的计算列。This tutorial will guide you through understanding and creating some of your own calculated columns in Power BI Desktop. 教程面向已熟悉使用 Power BI Desktop 创建更高级的模型的 Power BI 用户。It’s intended for Power BI users already familiar with using Power BI Desktop to create more advanced models. 你应该已经熟悉使用“查询”来导出数据、使用多个相关表和向报表画布添加字段。You should already be familiar with using Query to import data, working with multiple related tables, and adding fields to the Report Canvas. 如果刚开始使用 Power BI Desktop,请务必查看 Power BI Desktop 入门If you’re new to Power BI Desktop, be sure to check out Getting Started with Power BI Desktop.

若要完成本教程中的步骤,你需要下载 Power BI Desktop 的 Contoso 销售示例文件。To complete the steps in this tutorial, you’ll need to download the Contoso Sales Sample for Power BI Desktop file. 这是在《在 Power BI Desktop 中创建自己的度量值》教程中所用的相同示例文件。This is the same sample file used for the Create your own measures in Power BI Desktop tutorial. 它已包含来自虚构公司 Contoso,inc. 的销售数据。因为文件中的数据已从数据库导入,因此你将无法连接到数据源或在查询编辑器中查看。It already includes sales data from the fictitious company, Contoso, Inc. Because data in the file was imported from a database, you won’t be able to connect to the datasource or view it in Query Editor. 当你自己的计算机上有此文件时,请直接在 Power BI Desktop 中将它打开。When you have the file on your own computer, go ahead and open it in Power BI Desktop.

让我们来创建一个计算列Let’s create a calculated column

假设我们想要在行中的一个单值内同时展示产品类别和产品子类别,如手机 – 附件,手机 – 智能手机和 PDA,等等。Let’s say we want to display product categories together with product subcategories in a single value on rows, like Cell phones – Accessories, Cell phones – Smart phones & PDAs, and so on. 在报表视图或数据视图中(此处我们使用报表视图),如果我们看一下字段列表中的产品表,就会发现给出的结果中没有我们想要的任何字段。In Report View or Data View (we're using Report View here), If we look at our product tables in the Fields list, we see there’s no field that gives is what we want. 然而,我们有 ProductCategory 字段和 ProductSubcategory 字段,二者分别在其各自的表中。We do, however, have a ProductCategory field and a ProductSubcategory field, each in their own tables.

我们将创建新的计算列,用来将这两个列中的值合并到新列中的新值内。We’ll create a new calculated column to combine values from these two column into new values for our new column. 有趣的是,我们需要将两个不同表中的数据组合到一个单列内。Interestingly enough, we need to combine data from two different tables into a single column. 由于要使用 DAX 来创建新列,因而我们可以利用已有模型的完整功能,包括不同的表之间已存在的关系。Because we’re going to use DAX to create our new column, we can leverage the full power of the model we already have, including the relationships between different tables that already exist.

创建 ProductFullCategory 列To create a ProductFullCategory column

  1. 右键单击或单击字段列表中的 ProductSubcategory 表上的向下箭头,然后单击新建列Right click, or click the down arrow on the ProductSubcategory table in the Fields list, and then click New Column. 这会确保新列被添加到 ProductSubcategory 表中。This will make sure our new column is added to the ProductSubcategory table.

    公式栏将在报表画布或数据网格的顶部出现。The formula bar appears along the top of the Report canvas or Data grid. 我们可以在此处重命名列并输入 DAX 公式。This is where we can rename our column and enter a DAX formula.

    默认情况下新计算列简单地命名为列。By default a new calculated column is simply named Column. 如果我们不进行重命名,当我们创建其他度量值时,便会命名为列 2、列 3,依此类推。If we don’t rename it, when we create another, it will be named Column 2, Column 3, and so on. 我们希望列更易于识别,因此我们为这个新列提供新名称。We want our columns to be more identifiable, so we’ll give our new column a new name.

  2. 由于名已在公式栏中突出显示,因此只需键入“ProductFullCategory”即可。Since the Column name is already highlighted in the formula bar, just type ProductFullCategory.

    现在我们可以开始输入公式。Now we can begin entering our formula. 我们希望新列中的值以 ProductCategory 表中的 ProductCategory 名称开始。We want the values in our new column to start with the ProductCategory name from the ProductCategory table. 因为此列在不同但相关的表中,我们将使用 RELATED 函数来帮助获得它。Because this column is in a different, but related table, we’re going to use the RELATED function to help us get it.

  3. 在等号后键入 R。你将看到显示出来一个下拉建议列表,其中有以字母 R 开头的所有的 DAX 函数。我们键入的字母越多,建议列表就更能调整至接近我们所需的函数。After the equals sign, type R. You’ll see a dropdown suggestion list appear with all of the DAX functions beginning with the letter R. The more we type, the more the suggestion list is scaled closer to the function we need. 在函数周围,你将看到该函数的说明。Next to the function you’ll see a description of the function. 通过向下滚动选择 RELATED,然后按 Enter。Select RELATED by scrolling down, and then pressing Enter.

    出现一个左括号和另一个建议清单,其中包括我们可以传递到 RELATED 函数的所有可用的列。An opening parenthesis appears along with another suggestion list of all of the available columns we can pass to the RELATED function. 此外还会显示参数所预期的说明和详细信息。A description and details on what parameters are expected is also shown.

    表达式将始终出现在左括号和右括号之间。An expression always appears between an opening and closing parenthesis. 在这种情况下,我们的表达式将包含已传递到 RELATED 函数的单个参数;也就是要作为返回值来源的相关列。In this case, our expression is going to contain a single argument passed to the RELATED function; a related column to return values from. 列的列表会自动缩小以仅显示相关的列。The list of columns is automatically narrowed down to show only the columns that are related. 在这种情况下,我们想要 ProductCategory 表中的 ProductCategory 列。In this case, we want the ProductCategory column in the ProductCategory table.

    选择 ProductCategory [ProductCategory],然后键入右括号。Select ProductCategory[ProductCategory], and then type a closing parenthesis.

    提示

    语法错误通常由缺少或错放右括号导致。Syntax errors are most often caused by a missing or misplaced closing parenthesis. 但如果你忘记了,Power BI Desktop 通常会添加上。But often Power BI Desktop will add it if you forget.

  4. 我们想要添加短划线符号来分隔各个值,因此在第一个表达式的右括号后面,键入空格、& 号、左引号、空格、短划线 (-)、又一空格、右引号和又一 & 号。We want to add a dash symbol to separate each value, so after the closing parenthesis of the first expression, type a space, ampersand (&), quote, space, dash (-), another space, a closing quote, and then another ampersand. 该公式现在应如下所示:Your formula should now look like this:

    ProductFullCategory = RELATED(ProductCategory[ProductCategory]) & " - " &ProductFullCategory = RELATED(ProductCategory[ProductCategory]) & " - " &

    提示

    单击公式栏右侧的向下 V 形图标,展开公式编辑器。Click the down chevron on the right side of the formula bar to expand the formula editor. 按 Alt 和 Enter 键,向下移动一行,并按 Tab 键来移动内容。Click Alt & Enter to move down a line, and Tab to move things over.

  5. 最后,输入又一左括号,然后选择“[ProductSubcategory]”列,从而完成公式。Finally, enter another opening bracket and then select the [ProductSubcategory] column to finish the formula. 该公式应如下所示:Your formula should look like this:

    你会注意到我们没有使用调用 ProductSubcategory 列的第二个表达式中的另一个 RELATED 函数。You’ll notice we didn’t use another RELATED function in the second expression calling the ProductSubcategory column. 这是因为此列已在创建新列所在的同一个表中。This is because this column is already in the same table we’re creating our new column in. 我们可以输入带表名(完全限定的)或不带表名(非限定的)的 [ProductCategory]。We can enter [ProductCategory] with the table name (fully qualified) or without (non-qualified).

  6. 按 Enter 键或单击在公式栏中的选中标记以完成该公式。Complete the formula by pressing Enter or clicking on the checkmark in the formula bar. 该公式将经过验证并添加到 ProductSubcategory 表中的字段列表。The formula is validated and added to the field list in the ProductSubcategory table.

    你将注意到,计算列在字段列表中有一个特殊图标。You’ll notice calculated columns get a special icon in the field list. 这显示它们包含一个公式。This shows they contain a formula. 且仅在 Power BI Desktop 中才会如此显示。They’ll only appear like this in Power BI Desktop. 在 PowerBI 服务(Power BI 网站)中,绝对无法更改公式,所以计算列字段不会附带图标。In the PowerBI service (your Power BI site), there’s no way to change a formula, so a calculated column field doesn’t have an icon.

让我们将新列添加到报表中Let’s add our new column to a report

现可将新的 ProductFullCategory 列添加到报表画布。Now we can add our new ProductFullCategory column to the report canvas. 我们来看一下按 ProductFullCategory 排列的 SalesAmount。Let’s look at SalesAmount by ProductFullCategory.

ProductSubcategory 表中 ProductFullCategory 列拖到报表画布上,然后将销售表的 SalesAmount 字段拖到图表中。Drag the ProductFullCategory column from the ProductSubcategory table onto the Report canvas, and then drag the SalesAmount field from the Sales table into the chart.

再来创建一个列Let’s create another

现在了解了如何创建计算列,我们再来创建一列。Now that you know how to create a calculated column, let’s create another.

Power BI Desktop 模型的 Contoso 销售示例包括针对活跃商店和非活跃商店的销售数据。The Contoso Sales Sample for Power BI Desktop model contains sales data for both active and inactive stores. 我们希望针对非活跃商店显示的数据清楚标识为此状态。We want to make it really clear that data shown for inactive stores is identified as such. 事实上,我们想有一个名为 Active StoreName 的字段。In-effect, we want a field named Active StoreName. 为此,将再创建一个列。To do this, we’ll create another column. 在本例中,当商店不活跃时,我们希望 Active StoreName 列(作为字段)将此商店的名称显示“非活动”,而在商店活跃时显示此商店的实际名称。In this case, when a store is inactive, we want our new Active StoreName column (as a field) to show the store’s name as “Inactive”, but show the store’s real name when it’s an active store.

幸运的是,销售表中有一个名为“状态”的列,其针对活跃商店具有值“开”且针对非活跃商店具有值“关”。Fortunately, our Stores table has a column named Status, with a value of On for active stores, and Off for inactive stores. 我们可在“状态”列中测试每行的值,以在新列中创建新值。We can test values for each row in the Status column to create new values in our new column.

若要创建 Active StoreNam 列To create an Active StoreName column

  1. 销售表中新建名为 Active StoreName 的计算列。Create a new calculated column named Active StoreName in the Stores table.

    对于此列,DAX 公式将检查每个商店的状态。For this column, our DAX formula is going to check each stores status. 如果商店状态为“开”,则公式将返回商店名称。If a stores status is On, our formula will return the stores name. 如果其为“关”,则将具有“非活动”这一名称。If it’s Off, it will have the name, “Inactive”. 为此,将使用逻辑 IF 函数来测试商店状态,并在结果为 true 或 false 时返回特定值。To do this, we’ll use the logical IF function to test the stores status and return a particular value if the result is true or false.

  2. 开始键入 IFBegin typing IF. 建议列表将显示我们可以添加的内容。The suggestion list will show what we can add. 选择 IFSelect IF.

    IF 的第一个参数是逻辑测试。The first argument for IF is a logical test. 我们想要测试商店是否具有状态“开”。We want to test whether or not a store has a status of “On”.

  3. 键入一个左方括号 [,它可用于从商店表中选择列。Type an opening bracket [ , which allows us to select columns from the Stores table. 选择 [Status]Select [Status].

  4. 紧跟 [Status] 键入 ="On",然后输入逗号 (,) 以便输入第二个参数。Right after [Status], type ="On", then enter a comma (,) to enter the second argument. 工具提示建议我们在结果为 true 时添加一个值。The tooltip suggests we need to add the value for when the result is true.

  5. 如果商店处于“开”的状态,则需要显示商店的名称。If the store is On, we want to show the store’s name. 键入一个左方括号 [,选择 [StoreName] 列,然后再键入一个逗号,以便可输入第 3 个参数。Type an opening bracket [ and select the [StoreName] column, and then type another comma so we can enter our third argument.

  6. 当结果为 false 时,需要添加一个值,在本例中我们希望值为“非活动”We need to add a value for when the result is false, in this case we want the value to be “Inactive”.

  7. 按 Enter 键或单击在公式栏中的选中标记以完成该公式。Complete the formula by pressing Enter or clicking on the checkmark in the formula bar. 该公式将经过验证并添加到销售表中的字段列表。The formula is validated and added to the field list in the Stores table.

    如其他任何字段一样,能够在可视化对象中使用新的 Active StoreName 列。Just like any other field, we can use our new Active StoreName column in visualizations. 在此图中,状态为“开”的商店按名称单独显示,但状态为“关”的商店将组合在一起并显示为“非活动”。In this chart, stores with a status of On are shown individually by name, but stores with a status of Off are grouped together and shown as Inactive.

所学内容What we’ve learned

计算列可丰富数据,提供更方便的见解。Calculated columns can enrich our data, providing easier insights. 我们学习了如何使用公式栏来创建计算列、如何使用建议列表,以及如何以最佳方式设置新列的名称。We've learned how to create calculated columns by using the formula bar, how to use the suggestions list, and how to best name our new columns.

后续步骤Next steps

如果想要更深入了解 DAX 公式,并使用更高级的 DAX 公式来创建计算列,请参阅 Power BI Desktop 中的 DAX 基本概念If you want to take a deeper dive into DAX formulas, and create calculated columns with more advanced DAX formulas, see DAX Basics in Power BI Desktop. 本文重点在于介绍 DAX 中的基本概念,如语法、函数和对上下文的透彻理解。This article focuses on fundamental concepts in DAX, such as syntax, functions, and a more thorough understanding of context.

请务必将数据分析表达式 (DAX) 参考添加到收藏夹。Be sure to add the Data Analysis Expressions (DAX) Reference to your favorites. 你可以在这里找到有关 DAX 语法、运算符和 200 多个 DAX 函数的详细信息。This is where you'll find detailed info on DAX syntax, operators, and the over 200 DAX functions.