Start preparing for your Next Exam | Use coupon – TOGETHER | Avail 30% discount

Creating Calculated Columns

  1. Home
  3. Creating Calculated Columns

You may use calculated columns, that use Data Analysis Expressions (DAX) formulas for defining a column’s values. This involves anything from putting together text values from a couple of different columns to calculating a numeric value from other values. Moreover, calculated columns are related to measures in that both are based on DAX formulas, but they vary in how they are used. In general, we use measures in a visualization’s Values area, for calculating results based on other fields. Also, you can use calculated columns as new Fields in the rows, axes, legends, and group areas of visualizations.

Steps to create a calculated column with values from related tables

  • Firstly, to create your new column in the ProductSubcategory table, right-click or select the ellipsis … next to ProductSubcategory in the Fields pane, and select New column from the menu.
  • Secondly, when you select New column, the Formula bar appears along the top of the Report canvas, ready for you to name your column and enter a DAX formula.
  • By default, a new calculated column is named Column. 
  • In case, you want the values in your new column to start with the name in the ProductCategory field. Since this column is in a different but related table, you can use the RELATED function to help you get it.
  • Now, as wou want the ProductCategory column from the ProductCategory table. Select ProductCategory[ProductCategory], press Enter, and then type a closing parenthesis.
  • Moreover, as you want dashes and spaces to separate the ProductCategories and ProductSubcategories in the new values, so after the closing parenthesis of the first expression, type a space, ampersand (&), double-quote (“), space, dash (–), another space, another double-quote, and another ampersand.
  • Then, enter an opening bracket ([), and then select the [ProductSubcategory] column to finish the formula.
  • Lastly, complete the formula by pressing Enter or selecting the checkmark in the formula bar. The formula validates, and the ProductFullCategory column name appears in the ProductSubcategory table in the Fields pane.

Steps to create a calculated column that uses an IF function

  • Firstly, create a new calculated column in the Stores table and name it Active StoreName in the formula bar.
  • In the second step, After the = sign, begin typing IF. The suggestion list will show what you can add. Select IF.
  • Then, if the first argument for IF is a logical test of whether a store’s Status is “On”. Type an opening bracket [, which lists columns from the Stores table, and select [Status].
  • Next, right after [Status], type =”On”, and then type a comma (,) to end the argument. Moreover, the tooltip suggests that you now need to add a value to return when the result is TRUE.
  • If the store’s status is “On”, you want to show the store’s name. Type an opening bracket ([) and select the [StoreName] column, and then type another comma. The tooltip now indicates that you need to add a value to return when the result is FALSE.
  • You want the value to be “Inactive”, so type “Inactive”, and then complete the formula by pressing Enter or selecting the checkmark in the formula bar.
  • You can use your new Active StoreName column in visualizations just like any other field. To show SalesAmounts by Active StoreName, select the Active StoreName field or drag it onto the Report canvas, and then select the SalesAmount field or drag it into the table. In this table, active stores appear individually by name, but inactive stores are grouped together at the end as Inactive.
Microsoft Exam DA-100 Free Practice Test
Menu