How to use AddColumns function in DAX and Power BI

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
How to use AddColumns DAX function in 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;

AddColumn DAX function 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 used to add more than one column

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])
AddColumns used to draw a random record

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.

AddColumn in DAX and Power BI adds new columns to the existing table

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:

    Enter Your Email to download the file (required)

    Video

    FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
    Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
    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 nine 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.
    His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
    He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
    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.

    Leave a Reply