The SELECTCOLUMNS function returns a table with new columns specified by the user.
Syntax
SELECTCOLUMNS(<Table>, <Name>, <Expression>, <Name>, …)
Parameters
Table: Any DAX expression that returns a table.
Name: The name given to the column, enclosed in double quotes.
Expression: Any expression that returns a scalar value, like a column reference, integer, or string value.
Let’s get started
Import below dataset into Power BI Desktop
| Region | Country | City | Sales |
|---|---|---|---|
| North | India | Delhi | 1000 |
| North | India | Chandigarh | 1500 |
| South | India | Bangalore | 2000 |
| South | India | Chennai | 2500 |
| East | India | Kolkata | 1800 |
| West | India | Mumbai | 3500 |
| West | India | Pune | 1200 |
Example Scenario:
Suppose you have a table named Sales with columns Region, Country, City, and Sales, and you want to create a new table with only the Region, Sales, along with two new columns:
- CountryCity: A concatenation of the
CountryandCitycolumns. - Discount: Creates a column named Discount with values calculated as 10% of the sales.
Now follow these steps:
Step-1: Click on the Modeling tab in the ribbon and then click on New Table.
Step-2: In the formula bar, enter the SELECTCOLUMNS DAX function to create the new table. For example:
NewTable = SELECTCOLUMNS( Â Sales, Â "Region", Sales[Region], Â "CountryCity", 'Sales'[Country] & " " & 'Sales'[City], "Sales", Sales[Sales], "Discount", 'Sales'[Sales] * 0.1 )
I hope you now have a better understanding of how to create a new table with specified columns using the SELECTCOLUMN function.
Another examples:
Scenario 1: Using SELECTCOLUMNS with FILTER
FilteredSales = SELECTCOLUMNS( FILTER( Sales, Sales[Sales] > 2000 ), "Region", Sales[Region], "CountryCity", Sales[Country] & " " & Sales[City], "Sales", Sales[Sales], "Discount", Sales[Sales] * 0.1 )
Scenario 2:Using SELECTCOLUMNS with FILTER
CategorizedSales = SELECTCOLUMNS( ADDCOLUMNS( Sales, "SalesCategory", IF(Sales[Sales] > 3000, "High", "Low") ), "Region", Sales[Region], "CountryCity", Sales[Country] & " " & Sales[City], "Sales", Sales[Sales], "SalesCategory", [SalesCategory] )
I hope you enjoyed the post. Your valuable feedback, questions, or comments about this post are always welcome.
nice topic you covered Brother Kudos.
can i know what is the key difference between selected columns and summarize function.