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;
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;
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.
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: