Power Bi 简明教程
Power BI - Data Modeling
在本章，您将了解 Power BI 中的数据建模。
In this chapter, you will learn about data modeling in Power BI.
Using Data Modeling and Navigation
数据建模是 BI 工具中利用关系来连接多个数据源的一项功能。关系定义了数据源如何彼此连接，您可以在多个数据源之上创建有趣的数据可视化。
Data Modeling is one of the features used to connect multiple data sources in BI tool using a relationship. A relationship defines how data sources are connected with each other and you can create interesting data visualizations on multiple data sources.
利用建模功能，您可以在现有表中构建自定义计算，而这些列可直接显示到 Power BI 可视化中。这使得业务能够定义新的度量并针对这些度量执行自定义计算。
With the modeling feature, you can build custom calculations on the existing tables and these columns can be directly presented into Power BI visualizations. This allows businesses to define new metrics and to perform custom calculations for those metrics.
在上面的图片中，您能看到一个通用的数据模型，其显示了两个表之间的关系。这两个表利用列名“Id”进行连接。
In the above image, you can see a common data model, which shows a relationship between two tables. Both tables are joined using a column name “Id”.
类似地，在 Power BI 中，您可以设置两个对象之间的关系。要设置关系，您必须在常见的列之间拖拽一条线。您还可以在 Power BI 中的数据模型中查看“关系”。
Similarly, in Power BI, you set the relationship between two objects. To set the relationship, you have to drag a line between the common columns. You can also view the “Relationship” in a data model in Power BI.
要在 Power BI 中创建数据模型，您需要将所有数据源添加到 Power BI 新报告选项中。要添加数据源，请转到“获取数据”选项。然后，选择您想要连接的数据源并单击“连接”按钮。
To create data model in Power BI, you need to add all data sources in Power BI new report option. To add a data source, go to the Get data option. Then, select the data source you want to connect and click the Connect button.
一旦添加了数据源，它将显示在右侧边栏中。在以下图片中，我们使用 2 个 xls 文件来导入数据 - 客户和产品。
Once you add a data source, it is presented on the right side bar. In the following image, we have used 2 xls file to import data - Customer and Product.
在 Power BI 的屏幕左侧，您有以下三个选项卡 −
In Power BI on the left side of the screen, you have the following three tabs −
-
Report
-
Data
-
Relationships
当您导航到“报告”选项卡时，您可以看到为数据可视化选择的一个仪表板和图表。您可以根据需要选择不同类型的图表。在我们的示例中，我们从可用可视化中选择了“表”类型。
When you navigate to the Report tab, you can see a dashboard and a chart selected for data visualization. You can select different chart types as per your need. In our example, we have selected a Table type from available Visualizations.
当您转到“数据”选项卡时，您可以看到来自数据源的所有数据，这些数据符合已定义的关系。
When you go to the Data tab, you can see all the data as per the defined Relationship from the data sources.
在“关系”选项卡中，您可以看到数据源之间的关系。当您将多个数据源添加到 Power BI 可视化时，该工具会自动试图检测列之间的关系。当您导航到“关系”选项卡时，您可以查看关系。您还可以使用“创建关系”选项在列之间创建一个关系。
In the Relationship tab, you can see the relationship between data sources. When you add multiple data sources to Power BI visualization, the tool automatically tries to detect the relationship between the columns. When you navigate to the Relationship tab, you can view the relationship. You can also create a Relationship between the columns using Create Relationships option.
您还可以在数据可视化中添加和移除关系。要移除关系，您必须右键单击并选择“删除”选项。要创建一个新的“关系”，您只需拖放您想要在数据源之间链接的字段即可。
You can also add and remove relationships in data visualization. To remove a relationship, you have to right-click and select the “Delete” option. To create a new “Relationship”, you just need to drag and drop the fields that you want to link between the data sources.
您还可以使用关系视图在报表中隐藏特定列。要隐藏某一列，请右键单击该列名称，并选择“在报表视图中隐藏”选项。
You can also use the Relationship view to hide a particular column in the report. To hide a column, right-click on the column name and select the “Hide in report view” option.
Creating Calculated Columns
可以通过结合现有数据中的两个或更多元素在 Power BI 中创建计算列。您还可以对现有列应用计算，以定义新指标或结合两列来创建一列新列。
You can create calculated columns in Power BI by combining two or more elements of the existing data. You can also apply calculation on an existing column to define a new metric or combine two columns to create one new column.
您甚至可以创建计算列来建立表之间的关系，而且还可以使用它来设置两个表之间的关系。
You can even create a calculated column to establish a relationship between the tables and it can also be used to setup a relationship between two tables.
要创建新的计算列，请导航到屏幕左侧的数据视图选项卡，然后单击建模。
To create a new calculated column, navigate to Data View tab on the left side of the screen and then click Modeling.
当您导航到建模选项卡时，您可以在屏幕顶部看到一个新建列选项。这样也会打开公式栏，您可以在其中输入 DAX 公式以执行计算。DAX（数据分析表达式）是一种强大的语言，也用于 Excel 中执行计算。您还可以通过更改公式栏中的列文本重新命名该列。
When you navigate to the Modeling tab, you can see a New Column option at the top of the screen. This also opens the formula bar, where you can enter DAX formula to perform calculation. DAX- Data Analysis Expression is a powerful language also used in Excel to perform calculations. You can also rename the column by changing the Column text in the formula bar.
在以下示例中，让我们创建一个新列：产品代码 (Product_C)，它是从 Prod_Id 列的最后三个字符派生的。然后，写入以下公式 −
In the following example, let us create a new column: Product Code (Product_C), which is derived from the last three characters of Prod_Id column. Then, write the following formula −
Product_C = RIGHT( Sheet1[Prod_Id],3)
还提供了很多公式，您可以使用它们来创建计算列。您必须输入用作计算的公式的第一个字符，如下面的屏幕截图所示。
A long list of formulas is also provided that you can use for creating calculated columns. You have to enter the first character of formula to be used in calculations as shown in the following screenshot.
Creating Calculated Tables
您还可以创建 Power BI 中的数据建模中的新计算表。要创建新表，请导航到屏幕左侧的数据视图选项卡，然后转到屏幕顶部的建模选项。
You can also create a new calculated table in data modeling in Power BI. To create a new table, navigate to the Data View tab on the left side of the screen, and then go to the Modeling option at the top of the screen.
DAX 表达式用于创建新表。您必须在等号的左侧输入新表的名称和 DAX 公式以执行计算以在右侧形成该表。当计算完成后，新表会出现在您模型的“字段”窗格中。
DAX expression is used to create the new table. You have to enter the name of a new table on the left side of the equal sign and DAX formula to perform the calculation to form that table on the right. When the calculation is complete, the new table appears in the Fields pane in your model.
在以下示例中，让我们定义新表 - Table_CustC，它返回包含另一个表中一列中的唯一值的单列表。
In the following example, let us define a new table - Table_CustC that returns a one column table containing unique values in a column in another table.
如以下屏幕截图所示，新表会添加到 Power BI 屏幕中的“字段”部分下。一旦根据您的要求创建计算列和计算表，您就可以在 Power BI 的“报表”选项卡中使用字段。
A new table is added under the “Fields” section in Power BI screen as shown in the following screenshot. Once the calculated column and calculated tables are created as per your requirement, you can use the fields in the Report tab in Power BI.
要添加这些对象，您必须选择一个复选框，并且若可能，系统会自动检测到关系。如果不是，那么您可以拖动您要连接的列。
To add these objects, you have to select a checkbox and a relationship is automatically detected if possible. If not, then you can drag the columns that you want to connect.
要查看报表，请导航到报表选项卡，在报表视图中，您既可以查看“计算列”，还可以查看新“计算表”中的字段。
To view the report, you navigate to the Report tab and you can see both “Calculated columns” and fields from the new “Calculated table” in the report view.
Managing Time-Based Data
Power BI 默认允许深入钻取基于时间的数据。当您在分析中添加日期字段并在数据可视化中启用深入钻取后，它会带您深入了解下一层基于时间的数据。
Power BI allows to drill through time-based data by default. When you add a date field in your analysis and enable drill on your data visualization, it takes you to the next level of time-based data.
让我们考虑我们在 Power BI 可视化中添加了基于时间的表。我们在报表中添加了收入和年份列。
Let us consider we have added Time-based table in Power BI visualization. We have added Revenue and Year column in our report.
我们可以使用顶部的选项在可视化中启用深入钻取功能。一旦我们启用深入钻取功能，并单击图表中的条形图或折线图，它就会深入钻取到时间层级的下一层。 Example: 年份 → 季度 → 月份。
We can enable the drill feature in visualizations using the option at the top. Once we enable the drill feature and click the bars or lines in the chart, it drills down to the next level of time hierarchy. Example: Years → Quarters → Months.
我们还可以在层级中使用转到下一层选项来执行深入钻取。
We can also use Go to the next level in the hierarchy option to perform a Drill.
