With the modeling capabilities in Power BI, you can connect to multiple data sources, then combine them in ways that create a unique model of data that suits your needs.


資料模型簡介Introduction to modeling your data

歡迎使用 Power BI 引導式學習課程的<模型>一節。Welcome to the Modeling section of the Guided Learning course for Power BI. 本節說明如何使用 Power BI Desktop 準備隨時可用的連接資料。This section shows you how to get your connected data ready for use, using Power BI Desktop. 通常,您會連接至多個資料來源以建立報表,而且您需要所有的資料一起運作。Often, you'll connect to more than one data source to create your reports, and you'll need all of that data to work together. 模型是您的解決之道。Modeling is how you get it there.

若要建立不同資料來源之間的邏輯連接,您要建立關聯性To create a logical connection between different data sources, you create a relationship. 資料來源之間的關聯性讓 Power BI 知道這些資料表彼此如何相關聯,讓您建立有趣的視覺效果和報表。A relationship between data sources enables Power BI to know how those tables relate to one another, allowing you to create interesting visuals and reports. 本節會說明關聯性 (雖然只以資料為中心),甚至會示範如何在沒有關聯性時建立關聯性。This section explains relationships (only the data-centric ones, though), and even shows you how to create relationships when none exists.

本課程始終堅持學習之旅要和 Power BI 的工作流程一致。As always in this course, your learning journey follows the same path as the flow of work in Power BI. 本節大部分的工作仍要使用 Power BI Desktop,但這裡完成的工作會直接影響 Power BI 服務運作。We'll still be in Power BI Desktop for most of this section, but the work done here has direct affect on working in the Power BI service.

資料模型簡介Introduction to modeling your data

既然已檢閱過如何匯入及轉換資料,現在可以開始建立模型。Now that we've reviewed how to import your data and transform it, it's time to start modeling.

Power BI 的優點之一是,您不需要將資料壓平合併成一份資料表。One of Power BI's strengths is that you don't need to flatten your data into one table. 而是可以使用多個來源的多份資料表,定義它們之間的關聯性Instead, you can use multiple tables from multiple sources, and define the relationship between them. 您也可以建立自己的自訂計算,並指派新的計量以檢視特定區段的資料,以及在視覺效果中使用這些新的量值,以利建立模型。You can also create your own custom calculations and assign new metrics to view specific segments of your data, and use these new measures in visualizations for easy modeling.

如何管理資料關聯性How to manage your data relationships

Power BI 可讓您以視覺化方式設定資料表或元素之間的關聯性。Power BI allows you to visually set the relationship between tables or elements. 若要查看資料的圖表檢視,請使用報表畫布旁螢幕最左側的 [關聯性檢視]。To see a diagrammatic view of your data, use the Relationship view, found on the far left side of the screen next to the Report canvas.

從 [關聯性] 檢視中,您可以看到代表每份資料表及其資料行的區塊,以及表示它們之間關聯性的線條。From the Relationships view, you can see a block that represents each table and its columns, and lines between them to represent relationships.

新增和移除關聯性很簡單。Adding and removing relationships is simple. 若要移除關聯性,請以滑鼠右鍵按一下它並選取 [刪除]。To remove a relationship, right-click on it and select Delete. 若要建立關聯性,請拖放要連結資料表的欄位。To create a relationship, drag and drop the fields that you want to link between tables.

若要隱藏報表中的資料表或個別資料行,請在 [關聯性] 檢視中以滑鼠右鍵按一下它,然後選取 [在報表檢視中隱藏]。To hide a table or individual column from your report, right-click on it in the Relationship view and select Hide in Report View.

如需資料關聯性的更詳細檢視,請選取 [首頁] 索引標籤中的 [管理關聯性]。這會開啟 [管理關聯性] 對話方塊,以清單型式而不以視覺化圖表來顯示關聯性。For a more detailed view of your data relationships, select Manage Relationships in the Home tab. This will open the Manage Relationships dialog, which displays your relationships as a list instead of a visual diagram. 您可以在這裡選取 [自動偵測],尋找新資料或更新資料的關聯性。From here you can select Autodetect to find relationships in new or updated data. 選取 [管理關聯性] 對話方塊中的 [編輯],手動編輯關聯性。Select Edit in the Manage Relationships dialog to manually edit your relationships. 您也可以在這裡找到設定關聯性的 [基數] 和 [交叉篩選方向] 進階選項。This is also where you can find advanced options to set the Cardinality and Cross-filter direction of your relationships.

[基數] 選項為 [Many to One] (多對一) 和 [一對一]。Your options for Cardinality are Many to One, and One to One. [Many to One] (多對一) 是事實維度類型關聯性;例如,每項產品有多個資料列的銷售資料表,和以專有唯一資料列列出產品之資料表的比對。Many to One is the fact to dimension type relationship, for example a sales table with multiple rows per product being matched up with a table listing products in their own unique row. [一對一] 常用於連結參考資料表中的單一項目。One to One is used often for linking single entries in reference tables.

關聯性預設將設成雙向的交叉篩選。By default, relationships will be set to cross-filter in both directions. 單向的交叉篩選過去限制了關聯性的某些模型化功能。Cross-filtering in just one direction limited some of the modeling capabilities in a relationship.

設定資料間的正確關聯性,可讓您建立跨多個資料元素的複雜計算。Setting accurate relationships between your data allows you to create complex calculations across multiple data elements.

建立計算結果欄Create calculated columns

建立計算結果欄是擴充和增強資料的簡單方法。Creating calculated columns is a simple way to enrich and enhance your data. 計算結果欄是新的資料行,建立方法是定義轉換或合併現有資料之兩或多個元素的計算。A calculated column is a new column that you create by defining a calculation that transforms or combines two or more elements of existing data. 例如,您可以將兩個資料行合併成一個來建立新的資料行。For example, you can create a new column by combining two columns into one.

建立計算結果欄的一個有力因素是,沒有唯一欄位可用以建立關聯性時,它可建立資料表之間的關聯性。One useful reason for creating a calculated column is to establish a relationship between tables, when no unique fields exist that can be used to establish a relationship. 當您在 Power BI Desktop 中建立簡單的資料表視覺效果,所有項目都取得相同的值,但您知道基礎資料不同時,缺少關聯性就顯而易見。The lack of a relationship becomes apparent when you create a simple table visual in Power BI Desktop, and you get the same value for all entries, yet you know the underlying data is different.

若要使用資料的唯一欄位來建立關聯性,您可以,例如,合併「區域號碼」和「電話號碼」資料行的值 (資料中有這些值時),為「完整電話號碼」建立新的計算結果欄。To create a relationship with unique fields in data, you can, for example, create a new calculated column for "Full Phone Number" by combining the values from the "Area Code" and "Local Number" columns when those values exist in your data. 計算結果欄是有用的工具,可以迅速建立模型和視覺效果。Calculated columns are a useful tool for quickly creating models and visualizations.

若要建立計算結果欄,請選取 Power BI Desktop 報表畫布左側的 [資料檢視]。To create a calculated column, select the Data view in Power BI Desktop from the left side of the report canvas.

從 [模型] 索引標籤中,選取 [新增資料行]。From the Modeling tab, select New Column. 這可讓您啟用公式列,輸入使用 DAX (資料分析運算式) 語言的算式。This will enable the formula bar where you can enter calculations using DAX (Data Analysis Expressions) language. DAX 是 Excel 也使用的功能強大公式語言,可讓您建立穩健的計算。DAX is a powerful formula language, also found in Excel, that lets you build robust calculations. 當您輸入公式時,Power BI Desktop 會顯示符合的公式或資料元素,協助您更快建立公式。As you type a formula, Power BI Desktop displays matching formulas or data elements to assist and accelerate the creation of your formula.

當您輸入運算式時,Power BI 公式列會建議特定的 DAX 函數及相關的資料欄。The Power BI formula bar will suggest specific DAX functions and related data columns as you enter your expression.

每份資料表只要建立了計算結果欄,就可以當成唯一索引鍵建立資料表間的關聯性。Once the calculated columns are created in each table, they can be used as a unique key to establish a relationship between them. 移至 [關聯性] 檢視,就可以在資料表間拖曳欄位來建立關聯性。Going to Relationship view, you can then drag the field from one table to the other to create the relationship.

回到 [報表] 檢視,現在每個區域都有不同的值。Returning to Report view, you now see a different value for each district.

建立計算結果欄也可以執行很多其他作業。There are all sorts of other things you can do by creating calculated columns, too.

最佳化資料模型Optimizing data models

匯入的資料通常會包含報表和視覺化工作實際上不需要的欄位,因為它是額外的資訊,或者因為其他資料行已有可用資料。Imported data often contains fields that you don't actually need for your reporting and visualization tasks, either because it's extra information, or because that data is already available in another column. Power BI Desktop 有最佳化資料的工具,為您建立報表和視覺效果以及檢視共用報表提供更多助力。Power BI Desktop has tools to optimize your data, and make it more usable for you to create reports and visuals, and for viewing your shared reports.

隱藏欄位Hiding fields

若要隱藏 Power BI Desktop [欄位] 窗格中的資料行,請以滑鼠右鍵按一下它,然後選取 [隱藏]。To hide a column in the Fields pane of Power BI Desktop, right-click on it and select Hide. 請注意,隱藏的資料行並未刪除;如果您在現有的視覺效果中使用了該欄位,資料仍然位於該視覺效果中,而且資料也還可以用在其他視覺效果中,隱藏的欄位只是不顯示在 [欄位] 窗格中而已。Note that your hidden columns are not deleted; if you've used that field in existing visualizations, the data is still in that visual, and you can still use that data in other visualizations too, the hidden field just isn't displayed in the Fields pane.

如果您在 [關聯性] 檢視中檢視資料表,隱藏的欄位則會以灰色呈現。再次強調,其資料仍然可用,也仍然是模型的一部分,只是從檢視中隱藏起來而已。If you view tables in the Relationships view, hidden fields are indicated by being grayed out. Again, their data is still available and is still part of the model, they're just hidden from view. 以滑鼠右鍵按一下欄位,選取 [取消隱藏],即可取消隱藏任何已隱藏的欄位。You can always unhide any field that has been hidden by right-clicking the field, and selecting unhide.

依其他欄位排序視覺效果資料Sorting visualization data by another field

[模型] 索引標籤提供的 [依資料行排序] 工具,可以確保資料依您想要的順序顯示。The Sort by Column tool, available in the Modeling tab, is very useful to ensure that your data is displayed in the order you intended.

常見的範例為,包含月份名稱的資料預設依字母排序,所以 "August" (八月) 出現在 "February" (二月) 前面。As a common example, data that includes the name of the month is sorted alphabetically by default, so for example, "August" appears before "February".

在這個例子中,依序選取 [欄位] 清單中的欄位和 [模型] 索引標籤的 [依資料行排序],然後選擇要排序的欄位即可以修正問題。In this case, selecting the field in the Fields list, then selecting Sort By Column from the Modeling tab and then choosing a field to sort by can remedy the problem. 在本例中, "MonthNo" 類別目錄排序選項會依預期順序排列月份。In this case, the "MonthNo" category sort option orders the months as intended.

設定欄位的資料類型是另一種最佳化資訊的方法,以便您正確處理資料。Setting the data type for a field is another way to optimize your information so it's handled correctly. 若要從報表畫布變更資料類型,請選取 [欄位] 窗格中的資料行,然後使用 [格式] 下拉式功能表選取其中一個格式化選項。To change a data type from the report canvas, select the column in the Fields pane, and then use the Format drop-down menu to select one of the formatting options. 為顯示該欄位所建立的所有視覺效果都會自動更新。Any visuals you've created that display that field are updated automatically.

建立導出量值Create calculated measures

「量值」是存在於 Power BI 資料模型中的計算。A measure is a calculation that exists in your Power BI data model. 若要建立量值,請在 [報表] 檢視中,從 [模型] 索引標籤選取 [新增量值]。To create a measure, in Report view select New Measure from the Modeling tab.

DAX (Power BI 中的資料分析運算式語言) 很棒的一點就是擁有許多實用的函數,特別是以時間為基礎的計算函數,例如「年初至今」或「每年」。One of the great things about DAX, the Data Analysis Expression language in Power BI, is that it has lots of useful functions, particularly around time-based calculations such as Year to Date or Year Over Year. 使用 DAX,您可以定義一次時間量值,然後視需要依您資料模型中的許多不同欄位進行分割。With DAX you can define a measure of time once, and then slice it by as many different fields as you want from your data model.

在 Power BI 中,定義的計算稱為「量值」。In Power BI, a defined calculation is called a measure. 若要建立「量值」,請從 [常用] 索引標籤選取 [新增量值]。這會開啟公式列,您可以在此輸入定義量值的 DAX 運算式。To create a measure, select New Measure from the Home tab. This opens the Formula bar where you can enter the DAX expression that defines your measure. 當您輸入時,Power BI 會依您輸入的計算,建議相關的 DAX 函數和資料欄位,您也可以取得說明一些語法和函數參數的工具提示。As you type, Power BI suggests relevant DAX functions and data fields as you enter your calculation, and you'll also get a tooltip explaining some of the syntax and function parameters.

如果您的計算特別長,您可以在運算式編輯器中輸入 ALT-Enter,加入額外的分行符號。If your calculation is particularly long, you can add extra line breaks in the Expression Editor by typing ALT-Enter.

建立新量值之後,它會出現在畫面右側 [欄位] 窗格的其中一個資料表中。Once you've created a new measure, it will appear in one of the tables on the Fields pane, found on the right side of the screen. Power BI 會將新量值插入您目前選取的資料表,而且不論此量值在您資料中的實際位置為何,您都可以選取量值並使用 [主資料表] 下拉式功能表,輕鬆地移動量值。Power BI inserts the new measure into whichever table you have currently selected, and while it doesn't matter exactly where the measure is in your data, you can easily move it by selecting the measure and using the Home Table drop-down menu.

量值的使用方式就像是任何其他資料表資料行︰只要將其拖放到報表畫布或視覺效果欄位即可。You can use a measure like any other table column: just drag and drop it onto the report canvas or visualization fields. 量值也會與交叉分析篩選器緊密整合,以快速分割您的資料,這表示您可以定義一次量值,然後用於許多不同的視覺效果中。Measures also integrate seamlessly with slicers, segmenting your data on the fly, which means you can define a measure once, and use it in many different visualizations.

計算 DAX 函數是強大的函數,可進行各種實用的計算,特別適用於財務報告和視覺效果。The Calculate DAX function is a powerful function that enables all sorts of useful calculations, which is especially useful for financial reporting and visuals.

建立導出的資料表Create calculated tables

導出的資料表是 DAX 中的函數,可讓您表示一套完整的新模型功能。Calculated tables are a function within DAX that allows you to express a whole range of new modeling capabilities. 例如,如果您想要執行不同類型的合併聯結,或根據作用中公式的結果來快速建立新的資料表,導出的資料表可協助您達到此目的。For example, if you want to do different types of merge joins or create new tables on the fly based on the results of a functional formula, calculated tables are the way to accomplish that.

若要建立導出的資料表,請移至 Power BI Desktop 中的 [資料檢視],您可以從報表畫布左側啟用此功能。To create a calculated table, go to Data view in Power BI Desktop, which you can activate from the left side of the report canvas.

從 [模型] 索引標籤選取 [新增資料表] 以開啟公式列。Select New Table from the Modeling tab to open the formula bar.

在等號左側輸入新資料表的名稱,在右側輸入您要用來形成該資料表的計算。Type the name of your new table on the left side of the equal sign, and the calculation that you want to use to form that table on the right. 完成計算之後,新資料表會出現在模型的 [欄位] 窗格中。When you're finished your calculation, the new table appears in the Fields pane in your model.

建立之後,您可以像是任何其他資料表,在關聯性、公式和報表中使用導出的資料表。Once created, you can use your calculated table as you would any other table in relationships, formulas, and reports.

探索以時間為基礎的資料Explore your time-based data

您可以使用 Power BI 輕鬆地分析以時間為基礎的資料。It's easy to analyze time-based data with Power BI. Power BI Desktop 中的模型工具會自動包含產生的欄位,您只要按一下,就能向下鑽研年、季、月和日。The modeling tools in Power BI Desktop automatically include generated fields that let you drill down through years, quarters, months, and days with a single click.

當您使用日期欄位在報表中建立資料表視覺效果時,Power BI Desktop 會自動包含依時間週期劃分的細項。When you create a table visualization in your report using a date field, Power BI Desktop automatically includes breakdowns by time period. 例如,Power BI 已自動將 [日期] 資料表中的單一日期欄位分成「年」、「季」、「月」和「日」,如下圖所示。For example, the single date field in the Date table was automatically separated into Year, Quarter, Month and Day by Power BI, as shown in the following image.

視覺效果預設會在「年」層級顯示資料,但您可以開啟視覺效果右上角的 [向下鑽研] 加以變更。Visualizations display data at the year level by default, but you can change that by turning on Drill Down in the top right-hand corner of the visual.

現在當您按一下圖表中的直條或線條時,就會向下鑽研到下一層時間階層,例如從「年」到「季」。Now when you click on the bars or lines in your chart, it drills down to the next level of time hierarchy, for example from years to quarters. 您可以繼續向下鑽研,直到您到達最細微的一層階層,在此範例中為「日」。You can continue to drill down until you reach the most granular level of the hierarchy, which in this example is days. 若要在時間階層內回頭向上移動,請按一下視覺效果左上角的 [向上鑽研]。To move back up through the time hierarchy, click on Drill Up in the top left-hand corner of the visual.

您也可以向下鑽研視覺效果上顯示的所有資料,而不是某個選取的週期,方法是使用同時位在視覺效果右上角的全部鑽研雙箭號圖示。You can also drill down through all of the data shown on the visual, rather than one selected period, by using the Drill All double-arrow icon, also in the top right-hand corner of the visual.

只要您的模型包含日期欄位,Power BI 就會自動為不同的時間階層產生不同的檢視。As long as your model has a date field, Power BI will automatically generate different views for different time hierarchies.

若要回到個別日期,而不是使用日期階層,只要以滑鼠右鍵按一下 [欄位] 中的資料行名稱 (在下圖中,資料行的名稱是 InvoiceDate),然後從出現的功能表中選取資料行名稱,而非 [日期階層]。To get back to individual dates rather than using the date hierarchy, simply right-click the column name in the Fields well (in the following image, the name of the column is InvoiceDate), then select the column name from the menu that appears, rather than Date Hierarchy. 您的視覺效果接著會顯示根據該資料行資料的資料,而且不使用日期階層。Your visual then shows the data based on that column data, without using the date hierarchy. 需要回到使用日期階層?Need to go back to using the date hierarchy? 沒問題,只要再一次按滑鼠右鍵並從功能表選取 [日期階層]。No problem - just right-click again and select Date Hierarchy from the menu.

後續步驟Next steps

恭喜您!Congratulations! 您已完成本節的 Power BI 引導式學習課程。You've completed this section of the Guided Learning course for Power BI. 現在,您已了解 模型 資料,即可開始了解下一節中等您來挖掘的有趣項目︰ 視覺效果Now that you know about modeling data, you're ready to learn about the fun stuff waiting in the next section: Visualizations.

之前提過,本課程讓您遵循一般的 Power BI 工作流程來建立您的知識︰As mentioned before, this course builds your knowledge by following the common flow of work in Power BI:

  • 將資料帶入 Power BI Desktop 並建立報表。Bring data into Power BI Desktop, and create a report.
  • 發行至 Power BI 服務,在此建立新的視覺效果及建立儀表板Publish to the Power BI service, where you create new visualizations and build dashboards
  • 與其他人共用儀表板,尤其是行動工作者。Share your dashboards with others, especially people who are on the go
  • 使用 Power BI Mobile 應用程式來檢視共用的儀表板和報表,並與之互動。View and interact with shared dashboards and reports in Power BI Mobile apps

即使您可能不是單獨完成所有工作,還是會 了解 這些儀表板的建立方式,以及如何連接資料... 當您完成本課程時,您將能夠建立一個屬於自己的儀表板。While you might not do all that work yourself, you'll understand how those dashboards were created, and how they connected to the data... and when you're done with this course, you'll be able to create one of your own.

下一節見!See you in the next section!


You are on a roll. Keep up the good work in learning about Power BI! You've completed the Modeling section of Microsoft Power BI Guided learning. Next up is visualizations.





  • Davidiseminger
  • olprod
  • Alisha-Acharya