Power BI - Columns and Measures

Overview

Creating a new column or a new measure both fundamentally do the same thing. They are both methods of manipulating data for easier visualization. For most purposes, creating a new column is the easier method, especially for users who are familier with Excel. The reason that a measure may be used, is that the new values created from it are calculated as they are needed, they are not stored. This means that when the column is hundreds of thousands, or even millions of rows, a measure will keep the program running smoothly and quickly, whereas a new column may impact performance and size of the file.

DAX

DAX is a collection of functions, of which data can be manipulated and is the basis behind both columns and measures. If there is a large number of rows imported into Power BI, it is important to write your DAX expressions correctly to avoid the program slowing down. It has been shown that Power BI can support datasets with >a trillion rows with correctly written DAX. Some common DAX functions include (but are not even close to limited to):

• SUM: This adds all the numbers in the specified column (SUM(<column>))
• FILTER: The filter functions allows the manipulation of data context to create new calculations. This is one of the most useful DAX functions and creating a working knowledge of it is very powerful (FILTER(<table>,<filter>))
• COUNT: The count function counts the number of rows in a given column (COUNT(<column>))

Example

Here we will continue the example started on the Power BI page.

Example column

First, navigate to the dataview with the table icon on the left side of the window.

Let's create a new column to change the format of the date. Select "New Column" in the "Table Tools" tab.

Now we can highlight the text field and enter the following:

Date_Column(New) = FORMAT(Sheet1[date],"ddd, mmm dd/yy")

Let's break this DAX expression down.

1. Date_Column(New): The text before the "=" sign specifies the name of the new column. This can be anything but for simplicity, we named it something easy to identify.
2. FORMAT(): This DAX function converts a value to text, in this case a date value to our specified format.
3. Sheet1[date]: As consistent with the usage of the FORMAT function, this specifies the value to be converted to text. "Sheet1" is the worksheet and "[date]" is the column to pull from. We can identify these two on the right side of the window.
4. "ddd, mmm dd/yy": This specifies the format that we would like our date in.

Upon clicking enter, the column should populate with the same dates, just reformatted.

Example Measure

Navigate back to the report view here

Lets create a new measure to find the sum of all the sizes. The twist of this measure will be that it will only account for jobs that were won.

Click "New Measure"

Enter the following DAX expression:

Volume Won = CALCULATE(SUM(sheet1[size (mil)]),FILTER(sheet1,sheet1[won/lost]="won"))

Let's break this expression down into smaller parts:

1. Volume Won: Again, just the name of the measure.
2. CALCULATE: This function allows the evaluation of an expression with filters applied. In the case, we see two inputs. The SUM input and the FILTER input, separated by a comma.
3. SUM: This is the expression to be evaluated. Just like the last time SUM was used, we are evaluating the sum of all the rows in the "size (mil)" column of the "sheet1" worksheet.
4. FILTER: The first and only filter applied to the calculate function. The calculate function can take more than one filter but for this example, we just want one. In the FILTER function, we must specify the sheet and the criteria. The sheet is "sheet1" and the criteria is that the row in the column "[won/lost]" is ="won".

This expression may seem complicated to those unfamiliar to coding but expressions like this can be really powerful once one becomes familiar with their usage. The new measure we just created can be seen here:

Please follow the next tutorial to learn about visualizations