ADDCOLUMNS Function in DAX

ADDCOLUMNS Function in DAX

This function is used to add calculated fields (user defined) as columns to a table or a table expression in the data model.

 Syntax:

                    ADDCOLUMNS (<table>, <name>, <expression>[, <name>, <expression>]…)

                                It returns the table with the columns of the table along with the derived columns.

Parameters:

Table  - Any DAX expression that returns a table of data.

Name - The name given to the column, enclosed in double quotes.
Expression - Any DAX expression that returns a scalar expression, evaluated for each row of table.

Example:

We have a dataset with Orders of the business in “Orders” and returned orders in “Retruns” with the reference of “Order ID”.

Following example will create a new summary table with the name – “Return_Order_Summary” with sales amount for the respective Order ID.

Data in the table looks like  –

Another Scenario where we can use this Function is – Generating Calendar Dimension

Many times, we use the date hierarchy in the analysis. Power BI generates the hierarchy for the dates. But, it will generate only the predefined levels in the date hierarchy. It shows Year, Quarter, Month and Day as Below –

But, if we want to have our defined levels, we have to have our own calendar with the specified start date and End Date.

We can use this ADDCOLUMNS function along with CALENDAR function to generate calendar Dimension.

Steps to create the above date dimension in Power BI Model –

1. Open Power BI, Go to Modelling Menu
2. Click on New Table icon in the ribbon
3. Write the following Expression –
   
4. Validate the expression.
5. It will create the date dimension table starting from minimum of order date till today. You can give our own start date and end date in the calendar function.
6. Following is the Data we can see in the dimension -

Thanks....

Please share your Comments and Suggestions....