教學課程:在 Power BI Desktop 中建立您自己的導出資料行Tutorial: Create calculated columns in Power BI Desktop

有時候您分析中的資料,恰好未包含想取得結果的特定欄位。Sometimes the data you’re analyzing just doesn’t contain a particular field you need to get the results you’re after. 這是 [計算結果欄] 所在之處。This is where calculated columns come in. 計算結果欄使用資料分析運算式 (DAX) 公式,藉此定義資料行的值。Calculated columns use Data Analysis Expressions (DAX) formulas to define a column’s values. 這些值可以與任何項目有關,可以是放在一起的文字值,來自模型中其他位置的幾個不同資料行,或者可以是從其他值計算所得的數值。Those values can be just about anything, whether it be putting together text values from a couple of different columns elsewhere in the model, or calculating a numeric value from other values. 例如,假設您的資料具有 [城市] 與 [縣市] 的資料行 (像是在欄位清單中的欄位),但您要的是單一 [位置] 欄位,是同時具有兩者的單一值,例如佛羅里達州邁阿密。For example, let’s say your data has a City and State columns (as fields in the Fields list), but you want a single Location field that has both as a single value, like Miami, FL. 這正是計算結果欄的目的。This is precisely what calculated columns are for.

計算結果欄類似於兩者根據 DAX 公式所計算的量值,但它們所使用的方式又不同。Calculated columns are similar to measures in that both are based on a DAX formula, but they differ in how they are used. 在視覺效果的 [值] 區域最常使用量值,藉此根據您在資料表中資料列的其他欄位,或根據視覺效果的 [軸]、[圖例] 或 [群組] 區域,來計算結果。Measures are most often used in the Values area of a visualization, to calculate results based on other fields you have on a row in a table, or in an Axis, Legend, or Group area of a visualization. 另一方面,當您需要在資料表上的資料列、或在 [軸]、[圖例] 或 [群組] 區域中資料行的結果時,便會使用計算結果欄。Calculated columns on the other hand are used when you want the column’s results on that row in the table, or in the Axis, Legend, or Group area.

本教學課程將引導您了解,並在 Power BI Desktop 中建立一些您自己的計算結果欄。This tutorial will guide you through understanding and creating some of your own calculated columns in Power BI Desktop. 本文適用於已經熟悉使用 Power BI Desktop,用來建立更進階模型的 Power BI 使用者。It’s intended for Power BI users already familiar with using Power BI Desktop to create more advanced models. 您應該已經很熟悉使用 [查詢] 匯入資料、使用多個相關資料表,以及將欄位加入報表畫布。You should already be familiar with using Query to import data, working with multiple related tables, and adding fields to the Report Canvas. 如果您剛開始使用 Power BI Desktop,請務必參閱開始使用 Power BI DesktopIf you’re new to Power BI Desktop, be sure to check out Getting Started with Power BI Desktop.

若要完成本教學課程中的步驟,您將需要下載 Power BI Desktop 的 Contoso 銷售範例檔案。To complete the steps in this tutorial, you’ll need to download the Contoso Sales Sample for Power BI Desktop file. 這是在 Power BI Desktop 中建立您自己的量值教學課程中所使用的相同範例檔案。This is the same sample file used for the Create your own measures in Power BI Desktop tutorial. 它已經包含虛構公司 Contoso, Inc. 的銷售資料。因為檔案中的資料是從資料庫所匯入,所以您無法連接到資料來源或在查詢編輯器中檢視。It already includes sales data from the fictitious company, Contoso, Inc. Because data in the file was imported from a database, you won’t be able to connect to the datasource or view it in Query Editor. 當您自己的電腦上具備此檔案時,請直接在 Power BI Desktop 中開啟它。When you have the file on your own computer, go ahead and open it in Power BI Desktop.

讓我們來建立計算結果欄Let’s create a calculated column

假設我們想要在資料列的單一值中,顯示產品類別目錄與產品子類別目錄,例如行動電話 – 配件、行動電話 – 智慧型手機和 PDA 等等。Let’s say we want to display product categories together with product subcategories in a single value on rows, like Cell phones – Accessories, Cell phones – Smart phones & PDAs, and so on. 在 [報表檢視] 或 資料檢視,如果我們查看 [欄位] 清單中的產品資料表,就會發現提供的欄位不是我們要的欄位。In Report View or Data View (we're using Report View here), If we look at our product tables in the Fields list, we see there’s no field that gives is what we want. 不過,我們確實有 [ProductCategory] 和 [ProductSubcategory] 欄位,各自位於其自己的資料表中。We do, however, have a ProductCategory field and a ProductSubcategory field, each in their own tables.

我們將建立新的計算結果欄,將這兩個資料行的值結合成新資料行的新值。We’ll create a new calculated column to combine values from these two column into new values for our new column. 有趣的是,我們需要將兩個不同資料表的資料結合成單一資料行。Interestingly enough, we need to combine data from two different tables into a single column. 因為我們將使用 DAX 來建立新的資料行,所以我們可以充分利用既有模型的功能,包括現有不同資料表之間的關聯性。Because we’re going to use DAX to create our new column, we can leverage the full power of the model we already have, including the relationships between different tables that already exist.

建立 [ProductFullCategory] 資料行To create a ProductFullCategory column

  1. 以滑鼠右鍵按一下,或按一下 [欄位] 清單中的 [ProductSubcategory] 資料表向下箭號,然後按一下 [新增資料行]。Right click, or click the down arrow on the ProductSubcategory table in the Fields list, and then click New Column. 這樣可確保將我們新的資料行加入 [ProductSubcategory] 資料表。This will make sure our new column is added to the ProductSubcategory table.

    報表畫布或資料格上方會出現公式列。The formula bar appears along the top of the Report canvas or Data grid. 我們可以在其中將資料行重新命名,並輸入 DAX 公式。This is where we can rename our column and enter a DAX formula.

    根據預設,新的計算結果欄名稱就是「資料行」。By default a new calculated column is simply named Column. 如果我們沒有重新命名,則當我們建立另一個量值時,它就會命名為資料行 2、資料行 3,依此類推。If we don’t rename it, when we create another, it will be named Column 2, Column 3, and so on. 我們會想要讓資料行更容易識別,所以會將新的資料行重新命名。We want our columns to be more identifiable, so we’ll give our new column a new name.

  2. 因為 [資料行] 名稱已反白顯示在公式列中,所以只要輸入 ProductFullCategorySince the Column name is already highlighted in the formula bar, just type ProductFullCategory.

    現在我們可以開始輸入公式。Now we can begin entering our formula. 我們想要新資料行中的值開頭為 [ProductCategory] 資料表中的 [ProductCategory] 名稱。We want the values in our new column to start with the ProductCategory name from the ProductCategory table. 因為這個資料行位於不同但相關的資料表中,所以我們要使用 RELATED 函數來幫助我們取得資料行。Because this column is in a different, but related table, we’re going to use the RELATED function to help us get it.

  3. 在等號後面輸入 R。您會看到下拉式建議清單隨即出現,列出所有開頭字母為 R 的 DAX 函數。當我們輸入更多字母,建議清單就更能調整至接近我們所需的函數。After the equals sign, type R. You’ll see a dropdown suggestion list appear with all of the DAX functions beginning with the letter R. The more we type, the more the suggestion list is scaled closer to the function we need. 在函數旁可看到函數的描述。Next to the function you’ll see a description of the function. 向下捲動以選取 [RELATED],然後按 Enter 鍵。Select RELATED by scrolling down, and then pressing Enter.

    左括弧隨即出現,並出現另一個建議清單,其中有我們可傳遞至 RELATED 函數的可用資料行。An opening parenthesis appears along with another suggestion list of all of the available columns we can pass to the RELATED function. 同時也顯示預期參數的描述和詳細資訊。A description and details on what parameters are expected is also shown.

    運算式一律出現在左右括弧之間。An expression always appears between an opening and closing parenthesis. 在此情況下,我們的運算式將包含傳遞至 RELATED 函數的單一引數;也就是要從中傳回值的相關資料行。In this case, our expression is going to contain a single argument passed to the RELATED function; a related column to return values from. 資料行清單會自動縮小範圍,以便只顯示相關資料行。The list of columns is automatically narrowed down to show only the columns that are related. 在此情況下,我們想要的是在 [ProductCategory] 資料表的 [ProductCategory] 資料行。In this case, we want the ProductCategory column in the ProductCategory table.

    選取 [ProductCategory [ProductCategory]] ,然後輸入右括弧。Select ProductCategory[ProductCategory], and then type a closing parenthesis.


    語法錯誤通常因遺失或錯置右括弧所導致。Syntax errors are most often caused by a missing or misplaced closing parenthesis. 但如果您忘記,Power BI Desktop 通常會加入右括弧。But often Power BI Desktop will add it if you forget.

  4. 我們想要新增虛線符號來分隔每個值,因此請在第一個運算式的右括號之後,輸入空格、& 符號、引號、空格、虛線 (-)、另一個空格、右引號和另一個 & 符號。We want to add a dash symbol to separate each value, so after the closing parenthesis of the first expression, type a space, ampersand (&), quote, space, dash (-), another space, a closing quote, and then another ampersand. 您的公式現在看起來應該像這樣:Your formula should now look like this:

    ProductFullCategory = RELATED(ProductCategory[ProductCategory]) & " - " &ProductFullCategory = RELATED(ProductCategory[ProductCategory]) & " - " &


    按一下公式列右側的向下V形箭號,以展開工式編輯器。Click the down chevron on the right side of the formula bar to expand the formula editor. 按 Alt 鍵和 Enter 下移一行,然後按 Tab 來移動項目。Click Alt & Enter to move down a line, and Tab to move things over.

  5. 最後,輸入另一個左括弧,然後選取 [ProductSubcategory] 資料行以完成公式。Finally, enter another opening bracket and then select the [ProductSubcategory] column to finish the formula. 您的公式看起來應該像這樣:Your formula should look like this:

    您會發現我們在呼叫 [ProductSubcategory] 資料行的第二個運算式中並未使用另一個 RELATED 函數。You’ll notice we didn’t use another RELATED function in the second expression calling the ProductSubcategory column. 這是因為這個資料行所在的資料表,與我們建立新資料行的資料表相同。This is because this column is already in the same table we’re creating our new column in. 我們可以輸入 [ProductCategory],包含資料表名稱 (完整名稱) 或不含 (非限定)。We can enter [ProductCategory] with the table name (fully qualified) or without (non-qualified).

  6. 按下 Enter 或在公式列中按一下核取記號以完成公式。Complete the formula by pressing Enter or clicking on the checkmark in the formula bar. 公式會進行驗證並加入 [ProductSubcategory] 資料表中的欄位清單。The formula is validated and added to the field list in the ProductSubcategory table.

    您會發現在欄位清單中計算結果欄具有特殊的圖示。You’ll notice calculated columns get a special icon in the field list. 這顯示出它們包含公式。This shows they contain a formula. 在 Power BI Desktop 的外觀就只會如此顯示。They’ll only appear like this in Power BI Desktop. PowerBI 服務 (Power BI 網站) 中沒有變更公式的方式,所以計算結果欄欄位不會有圖示。In the PowerBI service (your Power BI site), there’s no way to change a formula, so a calculated column field doesn’t have an icon.

讓我們在報表中加入新的資料行Let’s add our new column to a report

現在我們可以將新的 [ProductFullCategory] 資料行加入報表畫布。Now we can add our new ProductFullCategory column to the report canvas. 讓我們依 [ProductFullCategory] 查看 [SalesAmount]。Let’s look at SalesAmount by ProductFullCategory.

將 [ProductFullCategory] 資料行從 [ProductSubcategory] 資料表拖曳至報表畫布,然後將 [SalesAmount] 欄位從 [Sales] 資料表拖曳至此圖表。Drag the ProductFullCategory column from the ProductSubcategory table onto the Report canvas, and then drag the SalesAmount field from the Sales table into the chart.

讓我們來建立另一個計算結果欄Let’s create another

您現在知道如何建立計算結果欄,讓我們來建立另一個。Now that you know how to create a calculated column, let’s create another.

Power BI Desktop 的 Contoso Sales 範例模型包含營運中和非營運中商店的銷售資料。The Contoso Sales Sample for Power BI Desktop model contains sales data for both active and inactive stores. 我們希望將針對非營運中商店所顯示的資料清楚識別出來。We want to make it really clear that data shown for inactive stores is identified as such. 事實上,我們需要名為 Active StoreName 的欄位。In-effect, we want a field named Active StoreName. 若要這樣做,我們要建立另一個資料行。To do this, we’ll create another column. 在此情況下,如果商店非營運中,我們要新的 [Active StoreName] 資料行 (做為欄位) 將此商店的名稱顯示為「非營運中」,但是當其為營運中的商店時,則顯示商店的實際名稱。In this case, when a store is inactive, we want our new Active StoreName column (as a field) to show the store’s name as “Inactive”, but show the store’s real name when it’s an active store.

幸好 [Store] 資料表有名為 [Status] 的資料行,如果商店為營運中,其值為 On,如果商店為非營運中,其值為 Off。Fortunately, our Stores table has a column named Status, with a value of On for active stores, and Off for inactive stores. 我們可以測試 [Status] 資料行中每個資料列的值,藉此在新的資料行中建立新的值。We can test values for each row in the Status column to create new values in our new column.

建立 [Active StoreName] 資料行To create an Active StoreName column

  1. 在 [Store] 資料表建立新的計算結果欄,名為 Active StoreNameCreate a new calculated column named Active StoreName in the Stores table.

    在這個資料行中,我們的 DAX 公式會檢查每間商店的狀態。For this column, our DAX formula is going to check each stores status. 如果商店狀態是 On,則我們的公式會傳回商店名稱。If a stores status is On, our formula will return the stores name. 如果是 Off,則名稱為「非營運中」。If it’s Off, it will have the name, “Inactive”. 若要這樣做,我們將使用邏輯 IF 函數,若結果為 True 或 False,則會測試商店狀態,然後傳回特定的值。To do this, we’ll use the logical IF function to test the stores status and return a particular value if the result is true or false.

  2. 開始輸入 IFBegin typing IF. 建議清單會顯示我們可以加入的項目。The suggestion list will show what we can add. 選取 [IF] 。Select IF.

    IF 的第一個引數是邏輯測試。The first argument for IF is a logical test. 我們想要測試商店狀態是否為 “On”。We want to test whether or not a store has a status of “On”.

  3. 輸入左括號 [,可以讓我們從 [Store] 資料表選取資料行。Type an opening bracket [ , which allows us to select columns from the Stores table. 選取 [Status] 。Select [Status].

  4. 在 [Status] 後緊接著輸入 ="On",然後輸入逗號 (,),以輸入第二個引數。Right after [Status], type ="On", then enter a comma (,) to enter the second argument. 工具提示會建議我們,需要加入當結果為 True 時的值。The tooltip suggests we need to add the value for when the result is true.

  5. 如果商店是 On,我們想要顯示此商店名稱。If the store is On, we want to show the store’s name. 輸入左括號 [ ,並選取 [StoreName] 資料行,然後輸入另一個逗號,讓我們可以輸入第三個引數。Type an opening bracket [ and select the [StoreName] column, and then type another comma so we can enter our third argument.

  6. 我們需要加入當結果為 False 時的值,在此情況下我們想要這個值為 “Inactive”We need to add a value for when the result is false, in this case we want the value to be “Inactive”.

  7. 按下 Enter 或在公式列中按一下核取記號以完成公式。Complete the formula by pressing Enter or clicking on the checkmark in the formula bar. 公式會進行驗證並加入 [Stores] 資料表中的欄位清單。The formula is validated and added to the field list in the Stores table.

    就像任何其他欄位,我們可以在視覺效果使用新的 [Active StoreName] 資料行。Just like any other field, we can use our new Active StoreName column in visualizations. 在此圖中,狀態為 On 的商店會各自依名稱顯示,但會將狀態為 Off 的商店分在同一組,並顯示為非營運中。In this chart, stores with a status of On are shown individually by name, but stores with a status of Off are grouped together and shown as Inactive.

我們所學的內容What we’ve learned

計算結果欄可以充實我們的資料,並且更易於了解。Calculated columns can enrich our data, providing easier insights. 我們學到如何使用公式列建立計算結果欄、如何使用建議清單,以及如何為新的資料行命名最合適的名稱。We've learned how to create calculated columns by using the formula bar, how to use the suggestions list, and how to best name our new columns.

後續步驟Next steps

如果您想要深入剖析 DAX 公式,並使用更進階的 DAX 公式建立計算結果欄,請參閱 Power BI Desktop 的 DAX 基本概念If you want to take a deeper dive into DAX formulas, and create calculated columns with more advanced DAX formulas, see DAX Basics in Power BI Desktop. 本文著重在 DAX 中的基本概念,例如語法、函數,並且更徹底了解內容。This article focuses on fundamental concepts in DAX, such as syntax, functions, and a more thorough understanding of context.

請務必將資料分析運算式(DAX) 參考加入我的最愛中。Be sure to add the Data Analysis Expressions (DAX) Reference to your favorites. 您可以在此找到 DAX 語法、運算子和超過 200 種 DAX 函數的詳細資訊。This is where you'll find detailed info on DAX syntax, operators, and the over 200 DAX functions.