使用 Power BI Desktop 中的導出資料行Using calculated columns in Power BI Desktop

透過導出資料行,您可以將新資料加入模型中已有的資料表。With calculated columns, you can add new data to a table already in your model. 您會建立定義資料行值的資料分析運算式 (DAX) 公式,而不是從資料來源查詢值並將其載入新的資料行。But instead of querying and loading values into your new column from a data source, you create a Data Analysis Expressions (DAX) formula that defines the column’s values. 在 Power BI Desktop 中,導出資料行是透過 [報表檢視] 中的 [新增資料行] 功能來建立。In Power BI Desktop, calculated columns are created by using the New Column feature in Report View.

與在 [查詢編輯器] 中使用 [新增自訂資料行] 建立做為查詢一部分的自訂資料行不同,在 [報表檢視] 或 [資料檢視] 中建立的導出資料行會依據您已經載入模型的資料。Unlike custom columns created as part of a query by using Add Custom Column in Query Editor, calculated columns created in Report View or Data View are based on data you’ve already loaded into the model. 例如,您可能會選擇串連不同但相關的兩個資料表之兩個不同資料行中的值、執行加法,或擷取子字串。For example, you might choose to concatenate values from two different columns in two different but related tables , perform addition, or extract sub-strings.

您所建立的導出資料行會出現在 [欄位] 清單中,就像是其他任何欄位一樣,但這些資料行會有特殊圖示,顯示其值為公式的結果。Calculated columns you create appear in the Fields list just like any other field, but they’ll have a special icon showing its values are the result of a formula. 您可以為資料行指定任何名稱,並將其加入報表視覺效果,就像是其他欄位一樣。You can name your columns whatever you want, and add them to a report visualization just like other fields.

導出資料行使用資料分析運算式 (DAX) 來計算結果,這是可處理類似 Power BI Desktop 中關聯式資料的公式語言。Calculated columns 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. 若要深入了解 DAX,請參閱本文結尾的<深入了解>一節。To learn more about DAX, see the Learn more section at the end of this article.

DAX 公式類似 Excel 公式。DAX formulas are similar to Excel formulas. 事實上,DAX 有許多函數與 Excel 相同。In fact, DAX has many of the same functions as Excel. 不過,DAX 函數是為了處理報表中以互動方式交叉篩選或篩選的資料,就像是在 Power BI Desktop 中一樣。DAX functions, however, are meant to work over data interactively sliced or filtered in a report, like in Power BI Desktop. 當您為新資料行建立 DAX 公式時,它會計算資料表中每個資料列的結果,這與 Excel 不同;在 Excel 中,資料表中的每個資料列可以有不同的公式。Unlike Excel, where you can have a different formula for each row in a table, when you create a DAX formula for a new column, it will calculate a result for every row in the table. 系統會視需要重新計算資料行值,例如當基礎資料重新整理及值變更時。Column values are recalculated as necessary, like when the underlying data is refreshed and values have changed.

以下舉例說明Let’s look at an example

Jeff 是 Contoso 的出貨經理。Jeff is a shipping manager at Contoso. 他想要建立報表,以顯示不同城市的出貨編號。He wants to create a report showing the number of shipments to different cities. 他有一個 [Geography] 資料表,內含代表城市和州的個別欄位。He has a Geography table with separate fields for city and state. 但是,Jeff 希望報表能夠顯示為同一個資料列上的單一值 [City, State]。But, Jeff wants his reports to show City, State as a single value on the same row. 目前,Jeff 的 [Geography] 資料表沒有他想要的欄位。Right now, Jeff’s Geography table doesn’t have the field he wants.

但透過導出資料行,Jeff 可以將 [City] 資料行中的城市與 [State] 資料行中的州直接放在一起,或串連在一起。But with a calculated column, Jeff can simply put together, or concatenate, the cities from the City column with the states from the State column.

Jeff 以滑鼠右鍵按一下 [Geography] 資料表,然後按一下 [新增資料行]。Jeff right clicks on the Geography table and then clicks New Column. 接著在公式列中輸入下列 DAX 公式:He then enters the following DAX formula into the formula bar:

這個公式會直接建立名為 CityState 的新資料行;針對 [Geography] 資料表中的每個資料列,它會擷取 [City] 資料行中的值、加入逗號和空格,再串連 [State] 資料行中的值。This formula simply creates a new column named CityState, and for each row in the Geography table, it takes values from the City column, adds a comma and a space, and then concatenates values from the State column.

現在 Jeff 會有他想要的欄位。Now Jeff has the field he wants.

他可以將該欄位連同出貨編號一起加入報表畫布。He can add it to his report canvas along with the number of shipments. 只要幾個動作,很快地 Jeff 便有 [City, State] 欄位。Very quickly and with minimal effort, Jeff now has a City, State field. 他可以加入幾乎任何類型的視覺效果。He can add to just about any type of visualization. Jeff 也會看到在建立地圖視覺效果時,Power BI Desktop 甚至知道如何讀取新資料行中的 [City, State] 值。Jeff even sees that when he creates a map visualization, Power BI Desktop even knows how to read the City, State values in his new column.

深入了解Learn more

我們在此僅快速介紹導出資料行。We’ve only provided a quick introduction to calculated columns here. 請務必參閱在 Power BI Desktop 中建立導出資料行教學課程,您可以從中下載範例檔案,並取得有關如何建立更多資料行的逐步解說課程。Be sure to see the Create calculated columns in Power BI Desktop tutorial, where you can download a sample file and get step-by-step lessons on how to create more columns.

若要深入了解 DAX,請參閱 Power BI Desktop 中的 DAX 基本概念.To learn more about DAX, see DAX basics in Power BI Desktop.

若要深入了解您建立作為查詢一部分的資料行,請參閱 Power BI Desktop 中的常見查詢工作中的<建立自訂資料行>一節。To learn more about columns you create as part of a query, see the Create custom columns section in Common query tasks in Power BI Desktop.