在Excel 2016数据模型中创建计算表 [英] Create calculated tables in Excel 2016 Data Model

问题描述

我在Excel 2016数据模型中有多个表.这些表来自其他excel工作表中维护的数据，并通过Excel Query导入以填充数据模型，以利用可用的高级数据管理功能(例如DAX，日期表，关系联接等)

I have multiple tables in Excel 2016 data model. These tables come from data maintained in other excel worksheets and are imported through Excel Query to populate a data model to take advantage of superior data management features that are available (e.g., DAX, date tables, relational joins, etc.)

但是，我希望能够通过应用过滤器，联合等来定位和转换现有数据元素来创建计算表"(带有DAX表达式).将数据模型中的计算表"用于数据透视表等的目标.在Excel 2016中是否可能?如果不是，那么需要哪些补充工具(除SQL之外)? TIA.

However, I would like to be able to create "calculated tables" (with DAX expressions) by applying filters, unions, etc. to target and transform the existing data elements. The goal to use the "calculated tables" in the Data Model for pivot tables, etc. Is this possible within Excel 2016? If not, what complementary tools (apart from SQL) are necessary? TIA.

推荐答案

我不这么认为.

在这里(尽管我注意到这篇文章是旧的):

Here (Though i note the article is old):

不幸的是，计算表在Excel 2016中不可用.如果 您需要使用Excel 2016的类似解决方案，可以依靠链接 后备表格(即，对在Excel中实现的数据模型的查询 表格，然后再加载回模型中).唯一的限制是 链接表的大小不能超过1M的物理限制 Excel行，而DAX计算表的大小和大小没有限制 可以在更多场景下工作，从而产生非常优雅和整洁的效果 模型.

Unfortunately, calculated tables are not available in Excel 2016. If you need a similar solution with Excel 2016, you can rely on linked back tables (i.e. queries over the data model materialized in Excel tables and then loaded back in the model). The only limitation is that the size of linked back tables cannot exceed the physical limit of 1M rows of Excel, whereas DAX calculated tables have no limit in size and can work in many more scenarios, resulting in a very elegant and neat model.

我认为这特别适用于Power BI，特别是使用新表"功能和SSAS表格使用新的计算表:

I think it is for Power BI particularly using the "New Table" feature and SSAS tabular using new calculated table:

用于Power BI

仅在Power BI Desktop中可用，而在任何Excel中均不可用 版本或SSAS表格.此功能本质上是已计算 表"功能.您可以通过任何可返回以下内容的有效DAX度量: 值表，该表将物化并加载到 数据模型.

This is only available in Power BI Desktop and not in any of the Excel versions or SSAS Tabular. This feature is essentially a "Calculated Table" function. You can pass any valid DAX measure that returns a table of values, and the table will be materialised and loaded into the data model.

并使用SSAS

如何创建计算表

How to create a calculated table

首先，确认表格模型的兼容性级别为1200或 更高.您可以在以下位置检查模型上的兼容性级别"属性 SSDT.

First, verify the tabular model has a compatibility level of 1200 or higher. You can check the Compatibility Level property on the model in SSDT.

切换到数据视图.您无法在以下位置创建计算表 图表视图.

Switch to the Data View. You can't create a calculated table in Diagram View.

选择表格>新的计算表格.

Select Table > New calculated table.

键入或粘贴DAX表达式(请参见下文以了解一些想法).

Type or paste a DAX expression (see below for some ideas).

命名表格.

创建与模型中其他表的关系.请参阅创建 两个表之间的关系(SSAS表格)，如果您需要帮助 此步骤.

Create relationships to other tables in the model. See Create a Relationship Between Two Tables (SSAS Tabular) if you need help with this step.

在模型或 在Excel中使用Analyze进行临时数据浏览.

Reference the table in calculations or expressions in your model or use Analyze in Excel for ad hoc data exploration.

这篇关于在Excel 2016数据模型中创建计算表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！