SELECTCOLUMNS DAX Function (Table manipulation)
Returns a table with selected columns from the table and new columns specified by the DAX expressions.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
|
Table Iterator |
The table from which columns are selected. |
|
| Name | Optional Repeatable |
The name of the new column to be added. |
|
Expression Row Context |
Repeatable |
The expression for the new column to be added. |
Return values
A table with the same number of rows as the table specified as the first argument. The returned table has one column for each pair of Name, Expression arguments, and each expression is evaluated in the context of a row from the specified Table argument.
Remarks
SELECTCOLUMNS has the same signature as ADDCOLUMNS, and has the same behavior, except that instead of starting with the <Table> specified, SELECTCOLUMNS starts with an empty table before adding columns.
SELECTCOLUMNS keeps the data lineage of the columns assigned to a simple column reference. Any different expression breaks the data lineage.
The <Name> argument can be skipped if the correspondent <Expression> argument is a simple column reference of the iterated table; the <Name> argument is required to name the output column generated by any other DAX expression.
When the <Name> argument is specified, it defines a new name for the column reference, regardless of the data lineage result.
» 1 related function
Examples
-- SELECTCOLUMNS is an iterator that returns a selection of
-- columns / expressions evaluated in the row context during
-- the iteration over its first parameter.
EVALUATE
SELECTCOLUMNS (
TOPN ( 10, 'Product' ),
"Product Category", 'Product'[Category],
"Product Subcategory", 'Product'[Subcategory],
"Product Color", 'Product'[Color],
"Product Name", 'Product'[Product Name],
"#Products", CALCULATE ( COUNTROWS ( 'Product' ) ),
"Sales", [Sales Amount]
)
| Product Category | Product Subcategory | Product Color | Product Name | #Products | Sales |
|---|---|---|---|---|---|
| Computers | Computers Accessories | Black | Contoso Rechargeable Battery E100 Black | 1 | 602.30 |
| Computers | Computers Accessories | Black | Contoso Dual USB Power Adapter – power adapter E300 Black | 1 | 2,655.35 |
| Computers | Computers Accessories | Black | Contoso Car power adapter M90 Black | 1 | 984.53 |
| Computers | Computers Accessories | Black | Contoso Notebook Peripheral Kit M69 Black | 1 | 1,066.73 |
| Computers | Computers Accessories | Black | Contoso Mouse Lock Bundle E200 Black | 1 | 554.26 |
| Computers | Computers Accessories | Black | Contoso Education Supplies Bundle E200 Black | 1 | 1,157.62 |
| Computers | Computers Accessories | Black | Contoso Laptop Starter Bundle M200 Black | 1 | 419.10 |
| Computers | Computers Accessories | Black | Contoso Education Essentials Bundle M300 Black | 1 | 144.40 |
| Computers | Computers Accessories | Black | Contoso Desktop Alternative Bundle E200 Black | 1 | 181.70 |
| Computers | Computers Accessories | Black | Contoso Power Inverter – DC to AC power inverter E900 Black | 1 | 365.09 |
-- SELECTCOLUMNS has a row context that can be used to write
-- expressions that navigate through relationships.
EVALUATE
CALCULATETABLE (
SELECTCOLUMNS (
Sales,
"Order Number", Sales[Order Number],
"Order Line Number", Sales[Order Line Number],
"Customer", RELATED ( Customer[Name] ),
"Product", RELATED ( 'Product'[Product Name] ),
"Quantity", Sales[Quantity],
"Line Amount", Sales[Quantity] * Sales[Net Price]
),
'Date'[Date] = DATE ( 2007, 9, 19 ),
Customer[Customer Type] = "Person"
)
| Order Number | Order Line Number | Customer | Product | Quantity | Line Amount |
|---|---|---|---|---|---|
| 20070919123936 | 1 | Vazquez, Monique | Contoso 512MB MP3 Player E51 Blue | 4 | 46.76 |
| 20070919525615 | 1 | Hall, Destiny | The Phone Company Touch Screen Phones – CRT M11 Grey | 4 | 680.40 |
| 20070919123935 | 1 | Barnes, Alexis | Contoso 512MB MP3 Player E51 Blue | 1 | 11.69 |
| 20070919123937 | 1 | Ruiz, Eddie | Contoso 512MB MP3 Player E51 Blue | 1 | 11.69 |
| 20070919123941 | 1 | Anderson, Eduardo | Contoso 512MB MP3 Player E51 Blue | 1 | 11.69 |
| 20070919427828 | 1 | Gao, Ernest | Fabrikam Social Videographer 2/3” 17mm E100 Grey | 1 | 144.00 |
| 20070919712464 | 1 | Cook, Jared | MGS Rise of Nations2009 E152 | 1 | 38.70 |
| 20070919712465 | 1 | Patel, Cassandra | MGS Rise of Nations2009 E152 | 1 | 38.70 |
| 20070919712466 | 1 | He, Willie | MGS Rise of Nations2009 E152 | 1 | 38.70 |
| 20070919712467 | 1 | Jones, Jennifer | MGS Rise of Nations2009 E152 | 1 | 38.70 |
| 20070919525616 | 1 | Rubio, Jésus | The Phone Company Touch Screen Phones – CRT M11 Grey | 1 | 170.10 |
| 20070919525617 | 1 | Shan, Leonard | The Phone Company Touch Screen Phones – CRT M11 Grey | 1 | 170.10 |
| 20070919525618 | 1 | Hernandez, Albert | The Phone Company Touch Screen Phones – CRT M11 Grey | 1 | 170.10 |
-- Column names can be omitted when there is a simple column reference
EVALUATE
CALCULATETABLE (
SELECTCOLUMNS (
Sales,
Sales[Order Number],
Sales[Order Line Number],
"Customer", RELATED ( Customer[Name] ),
RELATED ( 'Product'[Product Name] ),
Sales[Quantity],
"Line Amount", Sales[Quantity] * Sales[Net Price]
),
'Date'[Date] = DATE ( 2007, 9, 19 ),
Customer[Customer Type] = "Person"
)
| Order Number | Order Line Number | Customer | Product Name | Quantity | Line Amount |
|---|---|---|---|---|---|
| 20070919123936 | 1 | Vazquez, Monique | Contoso 512MB MP3 Player E51 Blue | 4 | 46.76 |
| 20070919525615 | 1 | Hall, Destiny | The Phone Company Touch Screen Phones – CRT M11 Grey | 4 | 680.40 |
| 20070919123935 | 1 | Barnes, Alexis | Contoso 512MB MP3 Player E51 Blue | 1 | 11.69 |
| 20070919123937 | 1 | Ruiz, Eddie | Contoso 512MB MP3 Player E51 Blue | 1 | 11.69 |
| 20070919123941 | 1 | Anderson, Eduardo | Contoso 512MB MP3 Player E51 Blue | 1 | 11.69 |
| 20070919427828 | 1 | Gao, Ernest | Fabrikam Social Videographer 2/3” 17mm E100 Grey | 1 | 144.00 |
| 20070919712464 | 1 | Cook, Jared | MGS Rise of Nations2009 E152 | 1 | 38.70 |
| 20070919712465 | 1 | Patel, Cassandra | MGS Rise of Nations2009 E152 | 1 | 38.70 |
| 20070919712466 | 1 | He, Willie | MGS Rise of Nations2009 E152 | 1 | 38.70 |
| 20070919712467 | 1 | Jones, Jennifer | MGS Rise of Nations2009 E152 | 1 | 38.70 |
| 20070919525616 | 1 | Rubio, Jésus | The Phone Company Touch Screen Phones – CRT M11 Grey | 1 | 170.10 |
| 20070919525617 | 1 | Shan, Leonard | The Phone Company Touch Screen Phones – CRT M11 Grey | 1 | 170.10 |
| 20070919525618 | 1 | Hernandez, Albert | The Phone Company Touch Screen Phones – CRT M11 Grey | 1 | 170.10 |
Data lineage and column reference can be different in the SELECTCOLUMNS result.
EVALUATE
FILTER (
CALCULATETABLE (
SELECTCOLUMNS (
Sales,
Sales[Line Number],
RELATED ( 'Product'[Product Name] )
),
Sales[Order Number] = 269500
),
-- Both column name and data lineage
-- are 'Product'[Product Name]
LEFT ( 'Product'[Product Name], 1 ) = "T"
)
EVALUATE
FILTER (
CALCULATETABLE (
SELECTCOLUMNS (
Sales,
Sales[Line Number],
"@Name", RELATED ( 'Product'[Product Name] )
),
Sales[Order Number] = 269500
),
-- Must use [@Name] as a column reference
-- and not 'Product'[Product Name], even if
-- the data lineage is still 'Product'[Product Name]
LEFT ( [@Name], 1 ) = "T"
)
Related articles
Learn more about SELECTCOLUMNS in the following articles:
-
Using GENERATE and ROW instead of ADDCOLUMNS in DAX
This article explains how to improve DAX queries using GENERATE and ROW instead of ADDCOLUMNS when you create table expressions. » Read more
-
Querying raw data to Tabular
This article describes how to extract raw data stored in the Tabular engine, used by Analysis Service Tabular, Power BI, and Power Pivot. » Read more
-
From SQL to DAX: Projection
This article describes projection functions and techniques in DAX, showing the differences between SELECTCOLUMNS, ADDCOLUMNS, and SUMMARIZE. » Read more
-
Implementing real-time updates in Power BI using push datasets instead of DirectQuery
Push datasets are an efficient and inexpensive way to implement real-time updates in Power BI reports and dashboards. They can provide better performance and scalability than DirectQuery at the price of a small development cost. » Read more
-
Using tuple syntax in DAX expressions
This article describes the use of the tuple syntax in DAX expressions to simplify comparisons involving two or more columns. » Read more
-
Replacing relationships with join functions in DAX
This article describes how to join tables in DAX when there are no relationships in the data model. The data lineage plays an essential role in this scenario. » Read more
-
Preparing a data model for Sankey Charts in Power BI
This article describes how to correctly shape a data model and prepare data to use a Sankey Chart as a funnel, considering events related to a customer (contact, trial, subscription, renewal, and others). » Read more
-
How to enable the Single Value option in a Power BI slicer
This article describes how to enable the Single Value slicer option in Power BI by adding additional metadata to the model with Tabular Editor. » Read more
Related functions
Other related functions are:
Last update: Nov 14, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/selectcolumns-function-dax