ADDCOLUMNS function DAX

ADDCOLUMNS is a POWER BI Table Manipulation Functions in DAX which is used to add a calculated columns in table.




Basically, ADDCOUMNS returns a table with new column specified in DAX expression.

SYNTAX
ADDCOLUMNS(<table_name>, <column_name>, <expression>
[, <column_name>, <expression>]…)

table_name is a table or any DAX express that returns a table.
column_name is name that is specified to the column, must enclosed in double quotes.
expression is a DAX expression that returns a scalar value, It is evaluated for each rows of table.

Lets look at an example of ADDCOULMNS.

Using ADDCOULMNS to add a new column in table

Here we have a table salesData which is created using a DATATABLE function DAX as shown below.

SalesData =
DATATABLE(
  "SalesId", INTEGER,
  "Year", STRING,
  "Amount_Per_Qty",CURRENCY,
  "Quantity", INTEGER,
  {
   {1, 2017, 5000, 4600},
   {2, 2018, 5500, 4590},
   {3, 2018, 6000, 2345},
   {4, 2020, 5600, 1000}
  }
  )

Now we will add a new column named “Total Sales  Amount” into this table using a ADDCOLUMNS Function. Value for “Total Sales  Amount” will be a multiplication of Amount_Per_Qty with Quantity.

Lets add a new columns into table SalesData.

SalesData =
ADDCOLUMNS(
DATATABLE(
   "SalesId", INTEGER,
   "Year", STRING,
   "Amount_Per_Qty",CURRENCY,
   "Quantity", INTEGER,
   {
     {1, 2017, 5000, 4600},
     {2, 2018, 5500, 4590},
     {3, 2018, 6000, 2345},
     {4, 2020, 5600, 1000}
   }
   ),
   "TOTAL SALES AMOUNT", [Amount_Per_Qty] * [Quantity]
   )

As you can see, the new column “TOTAL SALES AMOUNT” is added to the table SalesData.

Using ADDCOLUMNS with SUMMARIZE table

You can also use ADDCOLUMNS to add new column in Summarize table as shown below.

Measure_Summarize =
SUMMARIZE('Global-Superstore', 'Global-Superstore'[City],
"SALES", SUM('Global-Superstore'[Sales]),
"Quantity", SUM('Global-Superstore'[Quantity]),
"Profit", SUM('Global-Superstore'[Profit])

)

Lets add new column named “Total Sales”, and value for that column will be a Quantity * SALES.

Following DAX expression uses ADDCOLUMNS to add a column into Summarize table and returns new table.

Measure_Summarize =
ADDCOLUMNS(
 SUMMARIZE('Global-Superstore', 'Global-Superstore'[City],
 "SALES", SUM('Global-Superstore'[Sales]),
 "Quantity", SUM('Global-Superstore'[Quantity]),
 "Profit", SUM('Global-Superstore'[Profit])
  ),
  "Total Sales", [SALES] * [Quantity]
  )

Table Manipulation Functions

SUMMARIZE

SUMMARIZECOLUMNS

VALUES

DATATABLE

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 250 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.