ADDCOLUMNS is a DAX table function that allows user to add new columns to the existing data.
It has 3 main Arguments
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
Name - The name of the new column
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
Kommentarer