Data Modeling With Dax 简明教程

Extending the Data Model

在本章中，您将学习如何扩展在前面的章节中创建的数据模型。扩展数据模型包括：

In this chapter, you will learn how to extend the data model created in the previous chapters. Extending a data model includes −

  1. Addition of tables

  2. Addition of calculated columns in an existing table

  3. Creation of measures in an existing table

其中，创建度量至关重要，因为它涉及在数据模型中提供新的数据见解，这些见解将使用户在分析数据和做出决策时可以避免返工并节省时间。

Of these, creating the measures is crucial, as it involves providing new data insights in the data model that will enable those using the data model avoid rework and also save time while analyzing the data and decision making.

由于损益分析涉及使用时间段并且您将使用 DAX 时间智能函数，因此您需要在数据模型中拥有“日期”表。

As Profit and Loss Analysis involves working with time periods and you will be using DAX Time Intelligence functions, you require a Date table in the data model.

如果您不了解“日期”表，请通读章节——理解“日期”表。

If you are new to Date tables, go through the chapter – Understanding Date Tables.

您可以按以下方式扩展数据模型 -

You can extend the data model as follows −

  1. To create a relationship between the data table, i.e. Finance Data table and the Date table, you need to create a calculated column Date in the Finance Data table.

  2. To perform different types of calculations, you need to create relationships between the data table - Finance Data and the lookup tables – Accounts and Geography Locn.

  3. You need to create various measures that help you perform several calculations and carry out the required analysis.

这些步骤实质上构成了使用数据模型进行损益分析的数据建模步骤。但是，这是对您要使用 Power Pivot 数据模型执行的任何类型数据分析执行的一系列步骤。

These steps essentially constitute the data modeling steps for Profit and Loss Analysis using the data model. However, this is the sequence of steps for any type of data analysis that you want to perform with Power Pivot data model.

此外，您还将在后续章节中了解如何创建度量以及如何在 Power 透视表中使用它们。这将让您充分了解使用 DAX 建模数据和使用 Power 透视表对数据进行分析的情况。

Further, you will learn how to create the measures and how to use them in the Power PivotTables in the subsequent chapters. This will give you sufficient understanding of data modeling with DAX and data analysis with Power PivotTables.

Adding a Date Table to the Data Model

为跨越财政年度的时间段创建“日期”表，如下所示 -

Create a Date table for the time periods spanning the fiscal years as follows −

  1. Create a table with a single column with header – Date and contiguous dates ranging from 7/1/2011 to 6/30/2018 in a new Excel worksheet.

  2. Copy the table from Excel and paste it into the Power Pivot window. This will create a new table in the Power Pivot data model.

  3. Name the table as Date.

  4. Ensure that the Date column in the Date table is of data type - Date (DateTime).

接下来，您需要根据以下步骤向“日期”表中添加计算列——财政年度、财政季度、财政月份和月份 -

Next, you need to add the calculated columns – Fiscal Year, Fiscal Quarter, Fiscal Month and Month to the Date table as follows −

Fiscal Year

假设财政年度末为 6 月 30 日。那么，一个财政年度从 7 月 1 日跨度到 6 月 30 日。例如，时期 7/1/2011 (7/1/2011) 至 6/30/2012 (6/30/2012) 将是财政年度 2012 年。

Suppose the fiscal year end is June 30th. Then, a fiscal year spans from 1st July to 30th June. For example, the period July 1st, 2011 (7/1/2011) to June 30th, 2012 (6/30/2012) will be the fiscal year 2012.

在“日期”表中，假设您想表示与 FY2012 相同的内容。

In the Date table, suppose you want to represent the same as FY2012.

  1. You need to first extract the financial year part of the Date and append it with FY. For the dates in the months July 2011 to December 2011, the financial year is 1+2011. For the dates in the months January 2012 to June 2012, the financial year is 0+2012. To generalize, if the Month of Financial Year End is FYE, do the following − Integer Part of ((Month – 1)/FYE) + Year Next, take the rightmost 4 characters to obtain the Financial Year.

  2. In DAX, you can represent the same as − RIGHT(INT((MONTH('Date'[Date])-1)/'Date'[FYE])+YEAR('Date'[Date]),4)

  3. Add the calculated column Fiscal Year in the Date table with the DAX formula − ="FY"&RIGHT(INT((MONTH('Date'[Date])-1)/'Date'[FYE])+YEAR('Date'[Date]),4)

Fiscal Quarter

如果 FYE 表示财政年度结束的月份，则财政季度可获得为

If FYE represents the month of financial year end, the financial quarter is obtained as

Integer Part of ((Remainder of ((Month+FYE-1)/12) + 3)/3)

Integer Part of ((Remainder of ((Month+FYE-1)/12) + 3)/3)

  1. In DAX, you can represent the same as − INT((MOD(MONTH('Date'[Date])+'Date'[FYE]-1,12)+3)/3)

  2. Add the calculated column Fiscal Quarter in the Date table with the DAX formula − ='Date'[FiscalYear]&"-Q"&FORMAT( INT((MOD(MONTH('Date'[Date]) + 'Date'[FYE]-1,12) + 3)/3), "0")

Fiscal Month

如果 FYE 表示财政年度结束，则财政月份可获得为

If FYE represents the financial year end, the financial month period is obtained as

(Remainder of (Month+FYE-1)/12) + 1

(Remainder of (Month+FYE-1)/12) + 1

  1. In DAX, you can represent the same as − MOD(MONTH('Date'[Date])+'Date'[FYE]-1,12)+1

  2. Add the calculated column Fiscal Month in the Date table with the DAX formula − ='Date'[Fiscal Year]&"-P" & FORMAT(MOD(MONTH([Date])+[FYE]-1,12)+1,"00")

Month

最后，添加计算的列 Month，表示财年中表示月份数，如下所示 −

Finally, add the calculated column Month that represents the month number in a financial year as follows −

=FORMAT(MOD(MONTH([Date])+[FYE]-1,12)+1,"00") & "-" & FORMAT([Date],"mmm")

生成日期表如下面截图所示。

The resulting Date table looks like the following screenshot.

month

标记表 – 指定日期为日期表，以 - 日期作为具有唯一值的列，如下面截图所示。

Mark the table – Date as Date Table with the column - Date as the column with unique values as shown in the following screenshot.

mark as data table

Adding Calculated Columns

要创建财务数据表和日期表之间的关系，需要在财务数据表中创建一列日期值。

To create a relationship between the Finance Data table and the Date table, you require a column of Date values in the Finance Data table.

  1. Add a calculated column Date in the Finance Data table with the DAX formula − = DATEVALUE ('Finance Data'[Fiscal Month])

Defining Relationships Between Tables in the Data Model

在数据模型中有以下表 −

You have the following tables in the data model −

  1. Data table - Finance Data

  2. Lookup tables - Accounts and Geography Locn

  3. Date table - Date

若要在数据模型中的表格之间定义关系，请执行以下步骤：

To define Relationships between the tables in the data model, following are the steps −

  1. View the tables in the Diagram View of the Power Pivot.

  2. Create the following relationships between the tables − Relationship between Finance Data table and Accounts table with the column Account. Relationship between Finance Data table and Geography Locn table with the column Profit Center. Relationship between Finance Data table and Date table with the column Date.

relationships

Hiding Columns from Client Tools

若数据表格中包含任何不作为任何数据透视表的字段的列，则可将它们隐藏在数据模型中。然后，它们将不会在“数据透视表字段”列表中显示。

If there are any columns in a data table that you won’t be using as fields in any PivotTable, you can hide them in the data model. Then, they will not be visible in the PivotTable Fields list.

在“财务数据”表格中，有 4 个列（财政月份、日期、帐户和利润中心），这些列将不会用作任何数据透视表的字段。因此，可隐藏它们，以便它们不会显示在“数据透视表字段”列表中。

In the Finance Data table, you have 4 columns – Fiscal Month, Date, Account and Profit Center that you won’t be using as fields in any PivotTable. Hence, you can hide them so that they do not appear in the PivotTable Fields list.

  1. Select the columns - Fiscal Month, Date, Account, and Profit Center in the Finance Data table.

  2. Right-click and select Hide from Client Tools in the dropdown list.

hiding columns from client tools

Creating Measures in the Tables

你现在已经准备好使用数据模型和 Power 数据透视表来进行数据建模和 DAX 分析。

You are all set for data modeling and analysis with DAX using the data model and Power PivotTables.

在后续章节中，你将学习如何创建度量，以及如何在 Power 数据透视表中使用它们。你将在数据表格（即财务数据表格）中创建所有度量。

In the subsequent chapters, you will learn how to create measures and how to use them in Power PivotTables. You will create all the measures in the data table, i.e. Finance Data table.

你将在数据表（财务数据）中使用 DAX 公式创建度量，你可将其用于任意数量的数据透视表，以进行数据分析。度量本质上是元数据。在数据表中创建度量是数据建模的一部分，而在 Power 数据透视表中对它们进行汇总是数据分析的一部分。

You will create measures using DAX formulas in the data table – Finance Data, which you can use in any number of PivotTables for the data analysis. The measures are essentially the metadata. Creating measures in the data table is part of data modeling and summarizing them in the Power PivotTables is part of data analysis.