使用 Power BI Desktop 中的计算表Using calculated tables in Power BI Desktop

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

大多数情况下,你都要从外部数据源将数据导入模型。Most of the time, you import data into your model from an external data source. 但是,计算表具备某些优势。However, calculated tables provide certain advantages. 通常,计算表最适合于你希望将其作为模型的一部分而存储的中间计算和数据,而非在运行中计算的或作为查询的一部分而存储的中间计算和数据。Calculated tables are generally best for intermediate calculations and data you want stored as part of the model rather than calculated on the fly or as part of a query.

与作为查询的一部分而创建的表不同,在报表视图或数据视图中创建的计算表是以你已加载到模型中的数据为基础的。Unlike tables created as part of a query, calculated tables created in Report View or Data View are based on data you’ve already loaded into the model. 例如,你可以选择合并或交叉联接两个表。For example, you might choose to union or cross join two tables.

与普通表一样,计算表也能与其他表建立关系。Just like normal tables, calculated tables can have relationships with other tables. 计算表中的列具有数据类型、格式设置,并能归属于数据类别。The columns in your calculated table have data types, formatting, and can belong to a data category. 你可以随意对列进行命名,并将其像其他字段一样添加到报表可视化效果。You can name your columns whatever you want, and add them to a report visualization just like other fields. 如果计算表从其中提取数据的任何表以任何形式进行了刷新或更新,则将重新计算计算表。Calculated tables are re-calculated if any of the tables it pulls data from are refreshed or updated in any way.

计算表使用数据分析表达式 (DAX) 计算结果,它是一个旨在处理如 Power BI Desktop 中的关系数据的公式语言。Calculated tables 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.

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

Jeff,Contoso 的项目经理,拥有一个西北部员工的表和一个西南部员工的表。Jeff, a project manager at Contoso, has a table with employees on the in Northwest and another table with employees in the Southwest. Jeff 希望将这两个表合并成单个表。Jeff wants to put the two tables together into a single table.

NorthwestEmployeesNorthwestEmployees

SoutwestEmployeesSoutwestEmployees

使用计算表将这两个表合并非常容易。Putting these two tables together with a calculated table is quite easy. 尽管 Jeff 可以在报表视图或数据视图中创建计算表,但是在数据视图中创建会稍微容易一点,因为在此之中,他可以立即查看新的计算表。While Jeff can create a calculated table in either Report View or Data View, it’s a bit easier to do it in Data View because he can immediately see his new calculated table.

数据视图建模选项卡上,Jeff 单击新建表In Data View, on the Modeling tab, Jeff clicks New Table. 出现一个公式栏。A formula bar appears.

然后 Jeff 输入了以下公式:Jeff then enters the following formula:

名为 Western Region Employees 的新表就创建完成了。A new table named Western Region Employees is created.

Jeff 的 Western Region Employees 新表的显示方式与字段列表中的其他任何表相同。Jeff’s new Western Region Employees table appears just like any other table in the Fields list. 他可以创建与其他表之间的关系、添加计算列和度量值,并将其中任何字段添加到报表中,就像任何其他表一样。He can create relationships to other tables, add calculated columns and measures, and add any of its fields to reports just like any other table.

计算表的函数Functions for calculated tables

可以通过任何会返回表(包括对另一个表的简单引用)的 DAX 表达式定义计算表。Calculated tables can be defined by any DAX expression that returns a table, including a simple reference to another table. 例如:For example:

可以协同使用计算表和 DAX 来解决许多分析问题。You can use calculated tables with DAX to solve many analytical problems. 我们在此处只提供了关于计算表的简单介绍。We’ve only provided a quick introduction to calculated tables here. 开始使用计算表时,你可以在此处找到一些有用的更常见 DAX 表函数:As you start working with calculated tables, here are some of the more common DAX table functions you might find useful:

<TABLE> DISTINCT VALUES CROSSJOIN UNION NATURALINNERJOIN NATURALLEFTOUTERJOIN INTERSECT CALENDAR CALENDARAUTO<TABLE> DISTINCT VALUES CROSSJOIN UNION NATURALINNERJOIN NATURALLEFTOUTERJOIN INTERSECT CALENDAR CALENDARAUTO

有关这些函数以及返回 DAX 函数的其他表的信息,请参阅 DAX 函数引用See the DAX Function Reference for these and other table returning DAX functions.