top of page
Writer's pictureAntriksh Sharma

ADDCOLUMNS Function in DAX for Power BI, Power Pivot and SSAS

Updated: May 18, 2021

ADDCOLUMNS is a DAX table function that allows user to add new columns to the existing data.


It has 3 main Arguments

  1. Table - The table on which you want to create the new column, ADDCOLUMNS is going to iterate this table and will create a row context on this table and for each row it will compute the DAX code or expression supplied in the each "Expression" argument

  2. Name - The name of the new column

  3. Expression - A measure or any DAX code that has to be evaluated in a row context, if a measure is used then Context Transition is initiated.

Please note you are not limited to only 1 new column, you can create as many as you want.


Here is the snapshot of the data model:

ADDCOLUMNS Examples:


In the below image I have created a new column on the existing Products table, for the basic example I have only added 1 for each row of the table that is supplied to the first argument of the ADDCOLUMNS.

Now let's increase the difficulty a little bit.


In the below image I have changed the code, now 1 will only appear where the Product Color of the currently iterated row is equal to Red otherwise we get a 0.

Now let's call a Measure in the Expression argument.


For each row of the table provided in the first argument of ADDCOLUMNS, the hidden CALCULATE around the [Total Sales] measure is going to initiate the Context Transition and then will transform the currently iterated row into an equivalent filter context, which in turn will filter the Sales table and on which the code inside the [Total Sales] measure will compute the sales amount


The code of the Total Sales is as following:

Total Sales = 
SUMX ( 
    Sales, 
    Sales[Quantity] * Sales[Net Price]
) 

Adding more columns


Just to complete the example and to show that you can create multiple columns I have added back the code of the [One] column shown earlier.

New Column on Unique Table.


By default ADDCOLUMNS doesn't remove duplicates so if you only want to return unique values you will have to use a table function that returns only unique rows, those functions are ALL, VALUES, ALLNOBLANKROW, ALLEXCEPT, SUMMARIZE, DISTINCT, ALLSELECTED


The major difference between VALUES / ALL and DISTINCT / ALLNOBLANKROW / ALLSELECTED / SUMMARIZE is that VALUES and ALL return the Blank row that is added to the one side of the relationship by the DAX engine when there is referential integrity issue


The next 2 images show the difference in the result returned by VALUES and DISTINCT, you can try other functions to check what they return


VALUES:

DISTINCT

Filtering the new table

You have the option of limiting the rows of the final table by either filtering out the values of the table supplied in the first column or by filtering based on the newly created column


Let's filter out the products that are Silver, Blue, White. and Red with Sales Amount greater than 85,000


Below are 4 examples of restricting the result of ADDCOLUMNS


Filtering the result after computing everything with ADDCOLUMNS:

Partial filtering done of Products[Color] before ADDCOLUMNS creates the Sales Amount column of Colors table

Using CALCULATETABLE to apply a filter to the filter context and in this filter context the DISTINCT and [Total Sales] are evaluated and then the outer FILTER checks for Sales Amount > 1000

This time the boolean filter of [Total Sales] > 85000 is included inside CALCULATETABLE

Data Lineage of the Newly created columns


Please note that the newly create columns inside ADDCOLUMNS do not have any relation with the existing column of the model whatsoever, if you want to use that column for applying filters to the filter context then you need to create data lineage of the new column as if it was part of the model using TREATAS


To demonstrate the above example I am going to compute the Last date each color was ever bought and then will use that to filter the sales table

Now if I apply this table to the filter context using CALCULATETABLE it will work partially and will only filter the sales table for the products that are Red or Black but not for the MAX date that we have calculated.


In the below image you can see that we only see all the values of Sales Order Date even the ones that do not correspond to the Max Sales Date column

To modify the lineage of the Max Sales Date column inside ADDCOLUMNS we need to use TREATAS so that when we apply the values returned by ADDCOLUMNS to the filter context Max Sales Date column can filter the Sales Order Date column.


The below image shows that how we can create a lineage between columns returned by ADDCOLUMNS and the existing columns of the model, now Products[ProductKey] and Products[Color] already have a data lineage with the model but we still need to include them inside TREATAS corresponding to the columns supplied in the first argument.

Now we get the rows of the sales table that corresponds to the Max Sales Date column.

Using ADDCOLUMNS in Measures

ADDCOLUMNS isn't just used to create tables but you can also use it as a temporary table inside a measure.

Solving a real world scenario using ADDCOLUMNS

One of the very common requirement that I have seen from users of DAX is that they want to add Rank to the existing table using ADDCOLUMNS, let's see how you can achieve that.


The code has been commented so you can see how it is executed:

A better way of writing the above piece of DAX would be to use nested ADDCOLUMNS, that way we won't have to initiate Context Transition 3 times


5,585 views0 comments

Commentaires


bottom of page