ADDCOLUMNS in DAX: Add Calculated Columns to a Table

What Is ADDCOLUMNS in DAX?

ADDCOLUMNS is a DAX table function used to add calculated columns to an existing table expression. It returns a new table containing the original columns along with the additional calculated columns.

The basic syntax is:

ADDCOLUMNS(
    <Table>,
    "Column Name 1", <Expression 1>,
    "Column Name 2", <Expression 2>
)

The first argument is the table expression. Each remaining pair contains a new column name and the expression used to calculate its values.

Understanding the Tax Calculation

Before writing the queries, let’s understand the calculation used in this example.

Tax Amount = Sales Amount × (Tax / 100)

Invoice Amount = Sales Amount + Tax Amount

In this example, the tax rate is 18%. Therefore, the tax amount is calculated by multiplying the sales amount by 18 / 100.

1. Defining the Tax Variable

Query

DEFINE
    VAR Tax = 18

Step-by-step explanation

Step 1: DEFINE
DEFINE is used to declare variables and other query definitions before the query expression.

Step 2: VAR Tax = 18
This creates a variable named Tax and assigns it the value 18.

The variable can then be used in the calculations inside the DAX query.

2. Retrieving the Original Sales Table

Query

EVALUATE
    Sales

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the result of a table expression.

Step 2: Sales
The Sales table is returned as it is, without adding any calculated columns.

What will you get?

The result contains the original columns and rows from the Sales table.

3. Adding Tax Amount and Invoice Amount

Query

ADDCOLUMNS(
    Sales,
    "Tax Amount", Sales[Sales Amount] * (Tax/100),
    "Invoice Amount" , Sales[Sales Amount] + Sales[Sales Amount] * (Tax/100)
)

Step-by-step explanation

Step 1: ADDCOLUMNS
ADDCOLUMNS is used to add calculated columns to the table expression.

Step 2: Sales
This is the source table. All original columns from Sales are retained in the result.

Step 3: “Tax Amount”
This is the name of the first calculated column.

Step 4: Sales[Sales Amount] * (Tax/100)
This expression calculates the tax amount for each row.

For example, if the Sales Amount is 1,000:

1000 * (18/100) = 180

Step 5: “Invoice Amount”
This is the name of the second calculated column.

Step 6: Sales[Sales Amount] + Sales[Sales Amount] * (Tax/100)
This expression calculates the invoice amount by adding the tax amount to the sales amount.

1000 + 1000 * (18/100) = 1180

What will you get?

The result contains all original columns from the Sales table, plus the two new calculated columns.

The example values above are for illustration. Your actual result will contain the data available in your Sales table.

4. Creating a New Table with Selected Columns

In the previous query, we added calculated columns to the entire Sales table. In this query, we first select the required columns and then add the calculations.

Query

ADDCOLUMNS(
    SELECTCOLUMNS(
        Sales,
        Sales[Sales Amount]
    ),
    "Tax Amount", Sales[Sales Amount] * (18/100),
    "Invoice Amount" , Sales[Sales Amount] + Sales[Sales Amount] * (18/100)
)

Step-by-step explanation

Step 1: SELECTCOLUMNS
SELECTCOLUMNS is used to create a table containing only the selected columns.

Step 2: Sales
This is the source table from which the column is selected.

Step 3: Sales[Sales Amount]
This selects the Sales Amount column from the Sales table.

Step 4: ADDCOLUMNS
The result of SELECTCOLUMNS is passed to ADDCOLUMNS, which adds the calculated columns.

Step 5: “Tax Amount”
This creates a new column containing the tax calculation.

Step 6: “Invoice Amount”
This creates a new column containing the invoice amount calculation.

What will you get?

The result contains only the selected Sales Amount column and the two calculated columns.

Unlike the previous query, this result does not include every column from the original Sales table.

Understanding the Difference Between the Two Queries

Important Notes

1. ADDCOLUMNS returns a table expression

ADDCOLUMNS returns a table, so it can be used inside EVALUATE to display the result.

2. ADDCOLUMNS does not modify the original table

The original Sales table remains unchanged. The calculated columns are added to the table expression returned by the query.

3. SELECTCOLUMNS and ADDCOLUMNS can be combined

This combination is useful when you want to control the columns in the output and then add calculated columns.

4. The tax rate can be reused

In the first query, the Tax variable is used in both calculations. In the second query, the tax rate is written directly as 18/100.

DEFINE
    VAR Tax = 18

EVALUATE
    ADDCOLUMNS(
        SELECTCOLUMNS(
            Sales,
            Sales[Sales Amount]
        ),
        "Tax Amount", Sales[Sales Amount] * (Tax/100),
        "Invoice Amount", Sales[Sales Amount] + Sales[Sales Amount] * (Tax/100)
    )

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist