ADDCOLUMNS
A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
What is ADDCOLUMNS?
ADDCOLUMNS is a DAX function that allows you to add one or more columns to a table, based on the result of a DAX expression or formula. This can be useful for creating calculated tables that aggregate and analyze data in new and useful ways.
How to Use ADDCOLUMNS
The syntax for ADDCOLUMNS is straightforward. The function takes two arguments: the first is the name of the table you want to add columns to, and the second is a table expression that defines the new columns you want to add.
Here’s an example of how to use ADDCOLUMNS to create a new table with three columns:
NewTable = ADDCOLUMNS(
OldTable,
"NewColumn1", [Expression1],
"NewColumn2", [Expression2],
"NewColumn3", [Expression3]
)
In this example, “OldTable” is the name of the table you want to add columns to, and “NewColumn1”, “NewColumn2”, and “NewColumn3” are the names of the new columns you want to create. The expressions [Expression1], [Expression2], and [Expression3] define the formulas or calculations that will populate these columns.
Creating Calculated Tables with ADDCOLUMNS
One of the most powerful use cases for ADDCOLUMNS is to create calculated tables that aggregate and analyze data in new and useful ways. Let’s look at an example.
Suppose you have a table of sales data, with columns for “Product”, “Region”, “Quarter”, and “Revenue”. You want to create a new table that shows the total revenue for each product, broken down by region and quarter.
Here’s how you could use ADDCOLUMNS to create this table:
SalesByProduct = ADDCOLUMNS(
SUMMARIZE(
SalesTable,
SalesTable[Product],
SalesTable[Region],
SalesTable[Quarter]
),
"TotalRevenue", SUM(SalesTable[Revenue])
)
In this example, we use the SUMMARIZE function to group the sales data by product, region, and quarter. The result is a table that shows each combination of product, region, and quarter, along with the count of rows in the original sales data that match that combination.
We then use ADDCOLUMNS to add a new column called “TotalRevenue”, which calculates the sum of the “Revenue” column for each combination of product, region, and quarter.
The result is a new table that shows the total revenue for each product, broken down by region and quarter. This table can be used to create powerful visualizations and reports that help you analyze your sales data in new and useful ways.
ADDCOLUMNS is a powerful DAX function that allows you to add new columns to a table, based on the result of a DAX expression or formula. This can be useful for creating calculated tables that aggregate and analyze data in new and useful ways. By understanding the basics of how to use ADDCOLUMNS, you can unlock the full power of the DAX language in your Power BI reports.
