创建计算列Create calculated columns

在本课中,将通过添加计算列在模型中创建数据。In this lesson, you create data in your model by adding calculated columns. 可以在使用“获取数据”时(通过使用查询编辑器)或稍后在模型设计器中(按照本教程中介绍的步骤)创建计算列(作为自定义列)。You can create calculated columns (as custom columns) when using Get Data, by using the Query Editor, or later in the model designer like you do here. 若要了解详细信息,请参阅计算列To learn more, see Calculated columns.

将在三个不同的表中创建五个新的计算列。You create five new calculated columns in three different tables. 每个任务的执行步骤稍有不同,说明可通过多种方法创建列、对其进行重命名,以及将其放置在表中的各种位置。The steps are slightly different for each task showing there are several ways to create columns, rename them, and place them in various locations in a table.

还将在本课中首次使用数据分析表达式 (DAX)。This lesson is also where you first use Data Analysis Expressions (DAX). DAX 是一种特殊语言,用于为表格模型创建可高度自定义的公式表达式。DAX is a special language for creating highly customizable formula expressions for tabular models. 在本教程中,将使用 DAX 创建计算列、度量值和角色筛选器。In this tutorial, you use DAX to create calculated columns, measures, and role filters. 若要了解详细信息,请参阅表格模型中的 DAXTo learn more, see DAX in tabular models.

本课预计完成时间:15 分钟Estimated time to complete this lesson: 15 minutes

先决条件Prerequisites

本主题是表格建模教程的一部分,应当按顺序完成。This topic is part of a tabular modeling tutorial, which should be completed in order. 在执行本课中的任务之前,应当已完成上一课:第 4 课:创建关系Before performing the tasks in this lesson, you should have completed the previous lesson: Lesson 4: Create relationships.

创建计算列Create calculated columns

在 DimDate 表中创建 MonthCalendar 计算列Create a MonthCalendar calculated column in the DimDate table

  1. 单击“模型”菜单 >“模型视图” > “视图”。Click the Model menu > Model View > Data View.

    只能使用模型设计器在“数据视图”中创建计算列。Calculated columns can only be created by using the model designer in Data View.

  2. 在模型设计器中,单击“DimDate”表(选项卡)。In the model designer, click the DimDate table (tab).

  3. 右键单击“CalendarQuarter”列标题,并单击“插入列”。Right-click the CalendarQuarter column header, and then click Insert Column.

    一个名为“Calculated Column 1”的新列将插入到“Calendar Quarter”列的左侧。A new column named Calculated Column 1 is inserted to the left of the Calendar Quarter column.

  4. 在表上方的公式栏中,键入以下 DAX 公式:“自动完成”可帮助你键入列和表的完全限定名称,并将列出可用的函数。In the formula bar above the table, type the following DAX formula: AutoComplete helps you type the fully qualified names of columns and tables, and lists the functions that are available.

    =RIGHT(" " & FORMAT([MonthNumberOfYear],"#0"), 2) & " - " & [EnglishMonthName]  
    

    然后,会在计算列中为所有行填充值。Values are then populated for all the rows in the calculated column. 如果在表中向下滚动,可看到,根据每行中的数据,各行中的此列可能具有不同的值。If you scroll down through the table, you see rows can have different values for this column, based on the data in each row.

  5. 将此列重命名为 MonthCalendarRename this column to MonthCalendar.

    aas-lesson5-newcolumn

MonthCalendar 计算列提供月份的可排序名称。The MonthCalendar calculated column provides a sortable name for Month.

在 DimDate 表中创建 DayOfWeek 计算列Create a DayOfWeek calculated column in the DimDate table

  1. DimDate 表仍然处于活动状态的情况下,单击“列”菜单,并单击“添加列”。With the DimDate table still active, click the Column menu, and then click Add Column.

  2. 在公式栏中,键入以下公式:In the formula bar, type the following formula:

    =RIGHT(" " & FORMAT([DayNumberOfWeek],"#0"), 2) & " - " & [EnglishDayNameOfWeek]  
    

    完成公式构建后,按 ENTER。When you've finished building the formula, press ENTER. 新列将添加到表的最右侧。The new column is added to the far right of the table.

  3. 将此列重命名为 DayOfWeekRename the column to DayOfWeek.

  4. 单击列标题,并将该列拖动到 EnglishDayNameOfWeek 列和 DayNumberOfMonth 列之间。Click the column heading, and then drag the column between the EnglishDayNameOfWeek column and the DayNumberOfMonth column.

    Tip

    移动表中的列可使其更容易导航。Moving columns in your table makes it easier to navigate.

DayOfWeek 计算列提供周几的可排序名称。The DayOfWeek calculated column provides a sortable name for the day of week.

在 DimProduct 表中创建 ProductSubcategoryName 计算列Create a ProductSubcategoryName calculated column in the DimProduct table

  1. 在“DimProduct”表中,滚动到表的最右侧。In the DimProduct table, scroll to the far right of the table. 请注意,最右侧的列名为“添加列”(斜体),单击列标题。Notice the right-most column is named Add Column (italicized), click the column heading.

  2. 在公式栏中,键入以下公式:In the formula bar, type the following formula:

    =RELATED('DimProductSubcategory'[EnglishProductSubcategoryName])  
    
  3. 将该列重命名为“ProductSubcategoryName”。Rename the column to ProductSubcategoryName.

ProductSubcategoryName 计算列用于在 DimProduct 表中创建层次结构,该层次结构将包括 DimProductSubcategory 表的 EnglishProductSubcategoryName 列中的数据。The ProductSubcategoryName calculated column is used to create a hierarchy in the DimProduct table, which includes data from the EnglishProductSubcategoryName column in the DimProductSubcategory table. 层次结构不能跨多个表。Hierarchies cannot span more than one table. 稍后将在第 9 课中创建层次结构。You create hierarchies later in Lesson 9.

在 DimProduct 表中创建 ProductCategoryName 计算列Create a ProductCategoryName calculated column in the DimProduct table

  1. DimProduct 表仍然处于活动状态的情况下,单击“列”菜单,并单击“添加列”。With the DimProduct table still active, click the Column menu, and then click Add Column.

  2. 在公式栏中,键入以下公式:In the formula bar, type the following formula:

    =RELATED('DimProductCategory'[EnglishProductCategoryName]) 
    
  3. 将该列重命名为“ProductCategoryName”。Rename the column to ProductCategoryName.

ProductCategoryName 计算列用于在 DimProduct 表中创建层次结构,该层次结构包括 DimProductCategory 表的 EnglishProductCategoryName 列中的数据。The ProductCategoryName calculated column is used to create a hierarchy in the DimProduct table, which includes data from the EnglishProductCategoryName column in the DimProductCategory table. 层次结构不能跨多个表。Hierarchies cannot span more than one table.

在 FactInternetSales 表中创建 Margin 计算列Create a Margin calculated column in the FactInternetSales table

  1. 在模型设计器中,选择 FactInternetSales 表。In the model designer, select the FactInternetSales table.

  2. SalesAmount 列与 TaxAmt 列之间创建一个新的计算列。Create a new calculated column between the SalesAmount column and the TaxAmt column.

  3. 在公式栏中,键入以下公式:In the formula bar, type the following formula:

    =[SalesAmount]-[TotalProductCost]
    
  4. 将此列重命名为 MarginRename the column to Margin.

    aas-lesson5-newmargin

    Margin 计算列用来分析每次销售的利润率。The Margin calculated column is used to analyze profit margins for each sale.

后续步骤What's next?

第 6 课:创建度量值Lesson 6: Create measures.