Previously I explained the AddColumns function as a function that adds one or more columns to the existing table, In this article and video, I’ll explain another similar and useful function with slightly different behavior: SelectColumns, and you will learn how you can use it in Power BI and DAX.
SELECTCOLUMNS is a Tabular function in DAX
Similar to the AddColumns and many other functions, SELECTCOLUMNS is a tabular function in DAX, which means it returns a table as a result. Tabular functions are used a lot inside other functions when writing a measure.
The signature of the SelectColumns function is as below;
SELECTCOLUMNS(<table>, <name>, <scalar_expression> [, <name>, <scalar_expression>]…)
- Table: the table to start with.
- Name: Name of the new column added
- Expression: The expression for the new column
- you can add more columns if you want
A very simple example of SelectColumns can be as below;
Select col example - subset =
SELECTCOLUMNS(
DimCustomer,
'Firstname',[FirstName],
'last name',[LastName]
)
The code above gives us a subset of the DimCustomer column with the two columns of FirstName and LastName. but the two columns’ names can be edited through the formula as you see.
In the example above, the column names only changed, but not the expression. expressions are merely the column value itself. However, you can use an expression that changes the value like below;
select col example with calculation =
SELECTCOLUMNS(
DimCustomer,'Full Name',DimCustomer[FullName],
'Revenue',
CALCULATE(
SUM(FactInternetSales[SalesAmount]),
RELATEDTABLE(FactInternetSales)
)
)
The code above, will generate a table with the same number of rows as the DimCustomer, but only with the full name column and a new calculated column of Revenue.
SelectColumns Vs. AddColumns
One big difference of SelectColumns and AddColumns is that AddColumns keep all the existing columns in the table and adds more columns to that, But the SelectColumns starts with no columns from the table, and builds a custom subset of columns or with additional calculated columns on it.
The difference of the SelectColumns with AddColumns is much more visible when you look at similar (but different) example of AddColumns with a totally different result;
AddColumns keeps the existing columns of the table, But SelectColumns start with no columns and adds into that.
Using SelectColumns in Measures as a virtual table
Similar to many other tabular functions, the main use case of SelectColumns is when you create a virtual table inside a measure. If you have a function that accepts a table as input, then SelectColumns can be used in there.
When creating a virtual table, there are scenarios that you need a table with only a subset of columns. For example, if you are using functions such as UNION, INTERSET, or EXCEPT, you need two tables to have a similar structure, SelectColumns is one of the functions that can help with that.
SelectColumn also can be more efficient than AddColumns, because the size of the table in memory normally is smaller. Here is a comparison of fetching a random customer using AddColumns method:
and an example of that using SelectColumns method:
The performance and also the size are much better using the SelectColumns. This is understandable in the example above, because the DimCustomer table has 30 columns, and using AddColumns the result table will have 30+ columns. But the SelectColumns result has just two columns output, which would take less memory.
This is not, of course, the case for all scenarios, and it depends. However, if you are creating a virtual table, reducing the size of that table using SelectColumns can have good performance impacts.
Summary
SelectColumns and AddColumns are very much the same. SelectColumns is a tabular function that returns a table with a subset of columns (but the same number of rows) from the original table and it may have additional calculated columns in it. SelectColumns can be used instead of AddColumns in many scenarios. And one of the most use-cases of this function is when used inside another function to create a virtual table to help the final calculation of a measure.
Download Sample Power BI File
Download the sample Power BI report here:
Video
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.