DAX in SQL Server AddColumns to Add New Columns to Query Results

By: Haroon Ashraf

Overview

We already know we can simply add new columns with Summarize function. However, Summarize is not only way to add columns, a better function AddColumns is available. The AddColumns function adds column(s) without the need for grouping or summarizing data.

The syntax of ADDCOLUMNS is as follows:

ADDCOLUMNS(<Table>,<Name of Column>,<Expression>)

ADDCOLUMNS takes a table or table expression as an argument followed by the name of the column to be added followed by the expression of the column such as SUM, COUNT etc.

Adding Pounds into Pence Column

The Total Price in the Orders table is in pounds, so let's create a new column to convert pounds into pence by multiplying Total Price with 100 since there are 100 pence in 1 pound.

EVALUATE(ADDCOLUMNS(Orders,"Pounds into Pence",Orders[TotalPrice]*100))

Adding 10 more to the existing Quantity of Sold Products

Let's create a DAX query to add 10 more to the existing quantity of the products sold to all the customers.

Again please refer to the syntax:

ADDCOLUMNS(<Table>,<Name of Column>,<Expression>)

We can simply replace <Table> in ADDCOLUMNS with SUMMARIZE as this will help us to get columns from multiple tables plus any other grouping we desire although we are not after any grouping.

ADDCOLUMNS(SUMMARIZE(<Table>,<Group By Column>,<Name of Column>,<Expression>),<Name of Column>,<Expression>)

Next replace <Name of Column> with “Quantity+10” and <Expression> with Orders[Quantity]+10.

The final DAX query becomes:

EVALUATE(ADDCOLUMNS(SUMMARIZE(Orders,Customer[Name],OrderType[Name],Orders[Quantity]),"Quantity+10",Orders[Quantity]+10))

Adding Sum of Price Column for May 2017

Well if you look at the heading you can guess here comes the filter. Whatever we are after we need to narrow down our results to see data for the month of May and year 2017.

Let's refer back to the original construct:

ADDCOLUMNS(<Table>,<Name of Column>,<Expression>)

Start from the second and third arguments this time where we need to define the column to be added which is sum of price:

ADDCOLUMNS(<Table>,”Sum of Price”,SUM(Orders[TotalPrice]))

Replace first argument with SUMMARIZE:

EVALUATE(ADDCOLUMNS(SUMMARIZE(Orders,Customer[Name],Product[Name],DimDate[MonthName],Orders[TotalPrice]),"Sum of Price",SUM(Orders[TotalPrice])))

Formatting the query:

EVALUATE
 (
   ADDCOLUMNS (
       SUMMARIZE (
            Orders,
            Customer[Name],
            Product[Name],
            DimDate[MonthName],
                        Orders[TotalPrice]
                                   ),
              "Sum of Price",SUM ( Orders[TotalPrice] )
              )
)

SUMMARIZE will then be an argument for Filter:

EVALUATE
 (
   ADDCOLUMNS
    (
       Filter
        (
           SUMMARIZE (
                Orders,
                Customer[Name],
                Product[Name],
                DimDate[MonthName],
                Orders[TotalPrice]
                                 ),DimDate[MonthName]="May 2017"
                    ),
       "Sum of Price",SUM( Orders[TotalPrice] )
    )
)

In the above DAX query SUM(Orders[TotalPrice]) gives us overall sum of price of all the products sold.

Using Calculate to Sum Price

Please note in the above script if we use CALCULATE to sum Price then we get Sum of Price according to the grouped data while they both make sense.

EVALUATE
 (
   ADDCOLUMNS
    (
       Filter
        (
           SUMMARIZE (
                Orders,
                Customer[Name],
                Product[Name],
                DimDate[MonthName]
                                  ),DimDate[MonthName]="May 2017"
                    ),
       "Sum of Price",CALCULATE(SUM( Orders[TotalPrice] ))
    )
)

In the above DAX query Orders[TotalPrice] in SUMMARIZE has been removed since it is no longer needed.

Please note the same (Sum of Price) can also be achieved by using the SUMMARIZE function, but using ADDCOLUMNS has performance benefits over the SUMMARIZE function.

Adding Count of all Online Orders

The following script gives us count of all the Online Orders:

EVALUATE(FILTER(ADDCOLUMNS(SUMMARIZE(Orders,OrderType[Name]),"Total Orders",CALCULATE(COUNTROWS(Orders))),OrderType[Name]="Online Order"))

Formatted style:

EVALUATE
 (
   FILTER (
       ADDCOLUMNS (
           SUMMARIZE ( Orders, OrderType[Name] ),
                      "Total Orders",CALCULATE (COUNTROWS ( Orders ) )
                                   ),
                OrderType[Name] ="Online Order"
                      )
  )

Summary

Congratulations! You have successfully learned to write basic DAX queries against a tabular model (Analysis Services Database) which are not only far quicker than querying a relational database using T-SQL queries, but also have special data analysis features beyond the reach of T-SQL.