上QQ阅读APP看书，第一时间看更新

Building calculated columns

Open the pbix file Chapter 4 – Leveraging DAX from the book files

Calculated columns are stored in the table in which they are assigned, and the values are static until the data is refreshed. You will learn more about refreshing data in a later chapter.

There are many use cases for calculated columns, but the two most common are as follows:

Descriptive attributes
Concatenated key columns

Now you are going to create your first calculated column. Before you get started, though, you need to first know that Power BI Desktop has IntelliSense. IntelliSense will help you out a lot when writing code, as you will discover very soon. This built-in functionality will autocomplete your code as you go, and will also help you explore and discover new functions in the DAX language. In order to take advantage of IntelliSense, you simply need to start typing in the formula bar. Now you are ready to start writing DAX!

Click on the Data View—this is located on the left side of the Power BI Desktop screen. Next, click on the customer table from the Fields list. Once the customer table has been selected, click New Column—this is found under the modeling ribbon, as shown in the following screenshot:

Figure 1- New column

You will now see the text Column = in the formula bar. First, name the new column by replacing the default text of Column with Full Name. Then, move your cursor to after the equals sign and type a single quote character. Immediately after typing the single quote character, a list of autocomplete options will appear preceding the formula bar. This is IntelliSense at work. The first option in this list is the name of the table you currently have selected—Customer. Click the Tab key and the name of the table will automatically be added to the formula bar, as shown in the following screenshot:

Figure 2-Adding name of the table

At some point, you will inevitably discover that you can reference just the column name. As a best practice, we recommend always referencing both the table and column name anytime you use a column in your DAX code.

Next, type an opening square bracket into the formula bar followed by a capital letter F, making [F. Once again, you will immediately be presented with autocomplete options. The list of options has been limited to only columns that contain the letter f, and the first option available from the dropdown is First Name. Click tab to autocomplete. The formula bar should now contain the following formula:

Full Name = 'Customer'[First Name]

The next step is to add a space, followed by the last name. There are two options in DAX for combining string values. The first option is the concatenate function. Unfortunately, concatenate only accepts two parameters; therefore, if you have more than two parameters, your code will require multiple concatenate function calls. On the other hand, you also have the option of using the ampersand sign (&) to combine strings. The ampersand will first take both input parameters and convert them into strings. After this data conversion step, the two strings are then combined into one. Let's continue with the rest of the expression. Remember to use the built-in autocomplete functionality to help you write code.

Next, add a space and the last name column. To add a space—or any string literal value for that matter—into a DAX formula, you will use quotes on both sides of the string. For example, " " inserts a space between the first and last name columns. The completed DAX formula will look like the following:

Full Name = 'Customer'[First Name] & " " & 'Customer'[Last Name]