Overview
We already know we can simply add new columns with the Summarize function. However, Summarize is not the 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.
Explanation
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.
Haroon Ashraf’s deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional.
He holds BSc (Gold Medal) and MSc Degrees in Computer Science and also received the OPF merit award.
His programming career began in 2006 working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.
His work and interest revolves around Business Intelligence and Database Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration.
He has recently earned “Knowledge Management and Big Data in Business” certificate from The Polytechnic University of Hong Kong.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2020 | Author of the Year Contender – 2018-2020
