Dax Function: SELECTCOLUMNS

Category: Table Manipulation Functions

The SELECTCOLUMNS function in Power BI is a Data Analysis Expressions (DAX) function that creates a new table by selecting specific columns from an existing table and optionally renaming them or applying expressions to generate new values.

Purpose

  • Column Selection: Enables you to extract specific columns from a table.

  • Dynamic Column Creation: Allows you to create new calculated columns in the resulting table.

  • Custom Table Creation: Builds a custom table structure for further analysis or use in measures and relationships.

Type of Calculations

  • Extracts and transforms columns from existing tables.

  • Applies custom calculations to columns when needed.

Practical Use Cases

  1. Data Preparation: Use it to clean and reshape data for specific analysis.

  2. Custom Tables: Create custom tables with selected or derived columns.

  3. Intermediate Calculations: Prepare intermediary results for further computation.


SELECTCOLUMNS (
<table>,
<columnname1>, <expression1>,
[<columnname2>, <expression2>]...
)</expression2></columnname2></expression1></columnname1></table>
ParameterTypeDescription
TableTableThe table from which columns will be selected.
ColumnName1StringThe name of the new column in the result.
Expression1Scalar or ColumnAn expression that defines the value for the column.
ColumnName2String (Optional)The name of another new column (optional).
Expression2Scalar or Column (Optional)An expression that defines the value for the second column (optional).

How Does SELECTCOLUMNS Dax Works

The SELECTCOLUMNS function creates a new table by:

  1. Selecting columns from the specified table.

  2. Renaming or Calculating: Renaming columns or using expressions to calculate values for the new columns.

  3. Returning the resultant table, which can then be used in other DAX functions, measures, or Power BI visuals.

Principle

If you input:

SELECTCOLUMNS ( Sales, "Region", Sales[Region], "Revenue", Sales[Amount] * 0.9 )

The output is:

Region | Revenue
 
North  | 450.00
  
South  | 900.00   

What Does It Return?

  • Type: A table.

  • Content: Contains the specified columns and their values derived from the provided expressions.

When Should We Use It?

  1. Intermediate Steps: Use in measures or calculated columns for structured outputs.

  2. Testing Expressions: Generate a quick table to visualize complex DAX expressions.

  3. Custom Outputs: Create dynamic single-row tables for specific reporting needs.

Examples

Basic Usage :

Select and Rename Columns:


SELECTCOLUMNS ( Sales, "RegionName", Sales[Region], "Revenue", Sales[Amount] )

Result:

RegionName |  Revenue 
North      |   500.00 
South      |  1000.00 

Column Usage

Create a New Column with Calculations:


SELECTCOLUMNS ( Sales, "Discounted Revenue", Sales[Amount] * 0.8 )

Result:

Discounted Revenue
            400.00
            800.00

Advanced Usage

Combine with FILTER:


SELECTCOLUMNS (
FILTER(Sales, Sales[Region] = "West"),
"West Revenue", Sales[Amount]
)

West Revenue
————
       1000.00
         200.00

Tips and Tricks

  • Combine with other functions like FILTER or SUMMARIZE for more advanced table operations.

  • Use descriptive column names to enhance readability.

  • Performance: Overuse on large tables with complex expressions can slow down reports.

  • Table Context: Ensure the input table exists and has the required columns.

Performance Impact of SELECTCOLUMNS DAX Function:

  • Optimizations: Combine with filtering functions (FILTER, CALCULATE) to reduce the data volume processed.

  • Avoid Redundancy: Don’t use it for trivial column selection if direct table references suffice.

Related Functions You Might Need

FunctionDescription
ADDCOLUMNSAdds calculated columns to a table.
UNIONCombines rows from multiple tables.
SUMMARIZECreates a summary table for the specified groups.
SELECTCOLUMNSReturns a table with selected columns.
 

Want to Learn More?
For more information, check out the official Microsoft documentation for SELECTCOLUMNS You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What does the SELECTCOLUMNS function do in Power BI?

It creates a new table by selecting and renaming columns from an existing table or applying expressions to create calculated columns.

2. Can SELECTCOLUMNS create new columns?

Yes, you can define new calculated columns using expressions in the function.

3. What’s the difference between SELECTCOLUMNS and ADDCOLUMNS?

SELECTCOLUMNS creates a new table with selected or calculated columns, while ADDCOLUMNS adds calculated columns to an existing table.

4. Is SELECTCOLUMNS suitable for large datasets?

It can handle large datasets but may impact performance if used with complex expressions or unfiltered tables.

5. Can SELECTCOLUMNS be combined with other DAX functions?

Yes, it is often used with functions like FILTER, CALCULATE, and SUMMARIZE for advanced table operations.

Previous article
Dax Function: ROW
Next article
Dax Function: SUBSTITUTEWITHINDEX