The SELECTCOLUMNS function returns a table made up of columns calculated from an existing table or from an expression that returns a table.
SELECTCOLUMNS(
table,
name,
scalar_expression
[,name, scalar_expression]
)
- table: Reference to an existing table or expression that returns a table.
- name: Name to give to the calculated column.
- scalar_expression: DAX expression that returns a scalar expression as a column reference, an integer, or a text string.
The SELECTCOLUMNS function returns a table.
The SELECTCOLUMNS function behaves similarly to that of the ADDCOLUMNS function except for the fact that, instead of adding the columns to the table included as the first argument, they are added to an empty table.
The name of the calculated columns must be enclosed in double quotes.
The result of this function removes duplicate rows. See example below.
If we have a Geography table containing information about geographic locations, we can create a table from it that includes the postal code, the country and the sales in each location with the following measure:
Sales per territory =
SELECTCOLUMNS(
Geography,
"Postal code", Geography[PostalCode],
"Country", Geography[Country],
"Sales", [Total sales]
)
The result of the function removes duplicate rows. This can be easily verified if we start from the following scenario:
- Vendors table:
- Sales table:
Note that each vendor has made two sales and that vendor 1's sales have been of the same product (product 1). The two sales of the other two sellers have been of different products.
Now let's create a calculated table with the SELECTCOLUMNS function in which, starting from the sales table, we show the identifiers of the sellers:
Sales listing =
SELECTCOLUMNS(
Ventas,
"Vendedor", Ventas[Id Vendedor]
)
We see that it only shows one row per seller. Now let's add a second column with the identifiers of the products sold:
Sales listing =
SELECTCOLUMNS(
Ventas,
"Vendedor", Ventas[Id Vendedor],
"Producto", Ventas[Id Producto]
)
Again we see that the combination (Seller = 1, Product = 1) only appears once, when there are two records with this content.
- Creating a table with the average sales per salesperson
- Obtaining the date of the first and last sale of each seller
- Count of the number of distinct values in a column
- Aggregation of two tables with the same structure
- Renaming the fields of a table
- Viewing the days of occupation of a resource
- Calculation of the number of days between purchases
- Generating a table with a column of random values
