Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 10

Creating Calculated Columns

Recommended resources

Hello! Welcome back to our third module, "DAX Fundamentals."

In our last lesson, we established the crucial theoretical difference between calculated columns and measures. You learned that calculated columns are static, stored in your model, operate in a row context, and are ideal for creating categories to slice by. In contrast, measures are dynamic, calculated on-the-fly, operate in a filter context, and are used for aggregations.

Today, we'll put that theory into practice. Your learning outcome for this lesson is to write a calculated column to derive new information from existing row-level data. We'll move from knowing what a calculated column is to knowing how to build one yourself. This skill is essential for enriching your data model, adding valuable context that isn't present in your source data.

Let's begin.

The Anatomy of a Calculated Column

A calculated column extends a table by adding a new column whose values are determined by a DAX formula. The key principle, which we covered last time, is row context. This means the formula is evaluated independently for each row in the table.

Row-Level Data Illustration
This image illustrates the concept of row context. Imagine your DAX formula for a calculated column being applied to each of these individual rows, one at a time, using only the data within that specific row.

The syntax for creating a calculated column in Power BI is straightforward:

ColumnName = DAX Expression

Let's break that down with a simple reference.

Power BI DAX Tutorial for Beginners

This article from DataCamp provides a clear breakdown of the basic building blocks of a DAX formula and defines row context with a simple example.

Read the sections titled 'How to Write a DAX Formula' and 'Row context'. Focus on understanding the syntax components and how the row-by-row calculation works in the Cost Price Per Unit example.

As the article explains, the DAX expression for a calculated column can only reference values from the current row. For instance, in a Sales table, a formula like Sales[UnitPrice] * Sales[Quantity] works perfectly because, for each row, it takes the UnitPrice from that row and multiplies it by the Quantity from that same row.

Practical Application: Creating Your First Calculated Columns

Theory is important, but there's no substitute for seeing it in action. The following video provides a step-by-step guide for creating several different types of calculated columns, each with a distinct business purpose. We'll go through them one by one.

Example 1: Calculating Profit with Basic Arithmetic

The most common use for a calculated column is to perform a simple arithmetic calculation using values from other columns in the same row. Let's calculate the profit for each sale transaction.

📊 How to use Power BI DAX - Tutorial

This segment from Kevin Stratvert's tutorial will walk you through creating a 'Profit' column. This is a perfect example of a simple, row-level arithmetic calculation.

Watch the video from 20:37 to 22:14. Notice how the formula [Revenue] - [Cost] is straightforward because it operates within the row context. Also, pay attention to the discussion of why you might choose a calculated column over a measure in this scenario.

Example 2: Extracting Categories from a Date Column

Calculated columns are essential for creating new attributes you can use for filtering and grouping. A classic use case is extracting parts of a date, like the day of the week, which allows you to analyze sales patterns over time.

📊 How to use Power BI DAX - Tutorial

Now, let's see how to use a DAX function to derive a new categorical column from our existing 'Date' column.

Watch from 27:32 to 29:28. Observe how the WEEKDAY() function is used to convert the date for each row into a number representing the day of the week. This new column can now be used on a chart axis or in a slicer.

Example 3: Creating Categories with Text and Logic Functions

Let's look at a more complex example. You can use logical functions like IF and text functions like FIND to create a binary flag—a column that categorizes rows based on a condition. This is useful for segmenting data, such as identifying all products that contain a certain keyword.

📊 How to use Power BI DAX - Tutorial

This final segment demonstrates a powerful technique: creating a new category based on the text content of another column using logic.

Watch from 30:14 to 33:57. Follow the two-step process: First, using the FIND() function to locate a specific word ('chocolate') within the product name. Then, wrapping that logic inside an IF() statement to return a clean, readable category ('Has Chocolate' or 'No Chocolate').

Your Turn: Practice Creating Calculated Columns

Now it's time to apply what you've learned to our sales dataset. Open your Power BI file and complete the following tasks.

  1. Create a Gross Sales Column:

    • In the Sales table, create a new calculated column named Gross Sales.
    • The formula should calculate the total sales amount before any discounts.
    • Hint: The formula is Sales[Quantity] * Sales[Unit Price].

  2. Create an Order Size Category Column:

    • In the Sales table, create a new calculated column named Order Size.
    • This column should categorize orders based on their quantity. Use an IF statement to return "Bulk" for orders with a Quantity greater than 10, and "Standard" for all other orders.
    • Hint: The formula structure will be IF(Sales[Quantity] > 10, "Bulk", "Standard").

These exercises directly mirror the examples you've just seen and are fundamental tasks in data preparation.

Conclusion

In this lesson, you successfully transitioned from theory to practice by writing your first calculated columns in DAX. You have learned how to use row context to your advantage to create new information from your existing data.

Key Takeaways:

  • The syntax for a calculated column is Column Name = DAX Expression.
  • The expression is evaluated for each row, and it can only reference values from the current row (this is row context).
  • Calculated columns are powerful for:
    • Performing row-level arithmetic (e.g., Profit = Revenue - Cost).
    • Deriving new categories for slicing and filtering (e.g., extracting the Day of Week from a date or creating a logical flag like Has Chocolate).

Preview of the Next Lesson:
You have now enriched your data model with new, static columns. Our next step is to perform calculations on this data dynamically. In the next lesson, we will create our first measures using basic but essential aggregation functions like SUM, AVERAGE, COUNT, and DISTINCTCOUNT. This will allow us to start answering key business questions by summarizing our data.