How to use AddColumns function in DAX and Power BI

AddColumns is a DAX function that is helpful often when writing calculations in Power BI. In this article and video, I’ll explain how you can use it to add calculated columns on the fly to the virtual tables in measures or directly in a table.

AddColumns DAX Tabular Function

AddColumns is a tabular function in DAX. It means it returns a table, not a value. Tabular functions cannot be used directly in a measure, they have to be embedded inside other functions, or they can be used as a table expression.

The signature of using this function is as below;

ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…)

• Table: the table that we want to add columns to it
• Name: the name of the new calculated column
• Expression: the expression of the new calculated column
• you can add multiple columns using this function.

As an example, I can use the function to add a column to the customer table;

add col example = ADDCOLUMNS(
    DimCustomer,
    'Total revenue from the customer',
    CALCULATE(
        SUM(FactInternetSales[SalesAmount]),
        RELATEDTABLE(FactInternetSales)
        )
        )

The code above adds a new column to the DimCustomer, named “Total revenue from the customer”, and the expression for this new calculated column is the CALCULATE part of the expression above.

The above expression can be written as a new table in Power BI;

You can use the AddColumns to add more than one column, like below;

ADDCOLUMNS(
    DimCustomer,
    'Total revenue from the customer',
    CALCULATE(
        SUM(FactInternetSales[SalesAmount]),
        RELATEDTABLE(FactInternetSales)
        ),
    'Order Count',
    COUNTROWS(
        RELATEDTABLE(FactInternetSales)
    )
        )

The code above adds two columns to the DimCustomer table;

AddColumns in virtual tables

You can use AddColumns to create a calculated table (such as the examples above, or creating a date table using DAX). However, the most common way of using it is to use it inside a measure. Because AddColumns is a tabular function, to use it in a measure, you need other functions too.

Because AddColumns returns a table, then the result of that can be used as an input of another function that accepts a table.

For example, let’s say we want to randomly draw a customer for a weekly prize draw. To randomly the customer, one way is to add a random number to each customer, and then pick the customer with the highest random number.

The expression below adds a column to DimCustomer with a Random number between 0 to 1 (generated by RAND());

var customers=ADDCOLUMNS(
    DimCustomer,
    'Rand',
    RAND())

Now the result of the expression above is in a table variable, you can use that to pick the one with the highest random value;

var one_Customer=TOPN(1,customers,[Rand],DESC)

As you can see, the TOPN function uses the result of AddColumns (the customers variable) as the input table.

and finally we can return the customer’s full name for that one customer;

CONCATENATEX(one_Customer,[FullName])

Altogether, the expression for the measure is like below;

Draw a customer = 
var customers=ADDCOLUMNS(
    DimCustomer,
    'Rand',
    RAND())
var one_Customer=TOPN(1,customers,[Rand],DESC)
return
CONCATENATEX(one_Customer,[FullName])

If you run the code above on the same dataset, you would likely get another customer as a result, because the rand() function generates different values each time.

There are at least dozen other ways to fetch a random record from a table. This example here just provided to show you how AddColumns works inside a measure.

If you are interested in learning another real-world example of using AddColumns inside a measure take a look at my example of Dynamic Row-level security using many-to-many and hierarchical scenarios here.

Summary

AddColumns is a DAX function that returns a table. the returned table includes all the columns from the input table plus the new calculated columns. The expression written for the AddColumns will run for every row in the input table and generates the new column using it.

AddColumns is a table manipulation function, it does not change the existing rows and columns, but it adds new columns to it.

AddColumns can be used to create a calculated table. but the main usage of that is inside measures to add columns to a virtual table.

Download Sample Power BI File

Download the sample Power BI report here:

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.