Dax Function: ADDCOLUMNS

Category: Table Manipulation Functions

The ADDCOLUMNS function in Power BI is a Data Analysis Expressions (DAX) function used to add calculated columns to a table. The new columns are defined by expressions and are calculated row by row for the specified table.

Purpose

  • Enables the creation of calculated columns in a tabular data structure without modifying the original table.

  • Useful for enriching data with additional attributes or metrics dynamically.

Type of Calculations

  • Row-by-row evaluations of expressions for each record in the specified table.

  • Supports both scalar and aggregate calculations.

Practical Use Cases

  1. Data Enrichment: Add derived metrics to an existing table (e.g., profit margin, sales rank).

  2. Temporary Calculations: Create calculated columns for visualization or intermediate analysis without altering the original dataset.

  3. Dynamic Attributes: Add columns based on complex logical or mathematical expressions.


ADDCOLUMNS(table, columnName1, expression1, [columnName2, expression2, ...])
ParameterTypeDescription
tableTableThe input table to which new columns will be added.
columnName1StringThe name of the first new column to add. Must be enclosed in double quotes.
expression1ExpressionThe DAX expression used to calculate values for the new column.
[columnNameN]String(Optional) Additional column names to add.
[expressionN]Expression(Optional) Additional DAX expressions for calculating column values.

How Does ADDCOLUMNS Dax Works

  1. Input Table: Starts with a given table as the base.

  2. Column Definitions: Adds new columns defined by columnName and expression pairs.

  3. Row-by-Row Calculation: Evaluates the specified DAX expressions for each row of the table.

Key Points

  • The input table remains unchanged; the function returns a new table with added columns.

  • Supports using multiple columns and expressions in a single function call.

  • Expressions can reference columns from the input table or other related tables.

What Does It Return?

  • Table: Returns a new table with the same rows as the input table but includes the additional calculated columns.

When Should We Use It?

  • Evaluate Maturity-Yield Securities: Ideal for analyzing securities that pay a lump sum at maturity.

  • Compare Investment Options: Assess yield differences between securities with varying terms.

  • Financial Reporting: Include accurate yield calculations in reports for stakeholders.

Examples

Basic Usage :

Add a “Total Sales” column to a Sales table:


ADDCOLUMNS(Sales, "Total Sales", Sales[Quantity] * Sales[Price])

Result: Returns a table with a new column named “Total Sales,” which multiplies Quantity and Price.

Column Usage

Add “Discounted Price” and “Profit” columns to a Products table:


ADDCOLUMNS(
Products,
"Discounted Price", Products[Price] * (1 - Products[Discount]),
"Profit", Products[Revenue] - Products[Cost]
)

Result: Adds two columns:

  • Discounted Price: Calculates the price after applying a discount.

  • Profit: Subtracts Cost from Revenue.

Advanced Usage

Add a “Total Region Sales” column using a related table:


ADDCOLUMNS(
Regions,
"Total Region Sales", CALCULATE(SUM(Sales[Amount]), RELATEDTABLE(Sales))
)

Result: Adds a column showing the total sales for each region by aggregating the related Sales table.

Tips and Tricks

  • Use descriptive column names to avoid conflicts or confusion.

  • Combine with FILTER or SUMMARIZE for powerful data transformations.

  • Ensure expressions are optimized for large datasets to maintain performance.

  • Avoid using column names that already exist in the input table to prevent overwriting.

  • Be cautious with resource-intensive expressions in large datasets.

Performance Impact of ADDCOLUMNS DAX Function:

  • For large datasets, ensure efficient expressions to avoid high memory or processing costs.

  • Minimize the use of overly complex calculations within the function.

Related Functions You Might Need

FunctionDescription
SUMMARIZEGroups a table by one or more columns and adds calculated expressions.
GENERATECreates a table by combining each row from two tables.
SELECTCOLUMNSReturns a table with selected columns and calculated expressions.

Want to Learn More?
For more information, check out the official Microsoft documentation for ADDCOLUMNS You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What does the ADDCOLUMNS function do?

The ADDCOLUMNS function adds calculated columns to an existing table based on specified DAX expressions.

2. Does ADDCOLUMNS modify the original table?

No, it returns a new table without altering the original input table.

3. Can ADDCOLUMNS handle multiple new columns at once?

Yes, you can add multiple columns by providing additional columnName and expression pairs.

4. How does ADDCOLUMNS differ from SELECTCOLUMNS?

ADDCOLUMNS appends calculated columns to the existing structure, while SELECTCOLUMNS creates a new table with specified columns.

5. Is ADDCOLUMNS resource-intensive?

It can be if the expressions involve complex calculations or large datasets. Optimize your DAX code for performance.

Back to module
Dax Function: Table Manipulation Functions
Next article
Dax Function: ADDMISSINGITEMS