Create a subset of the table in Power BI and add calculations using SELECTCOLUMNS DAX Function

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
How SelectColumns DAX function works in Power BI

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.

Basics of SelectColumns DAX function in Power BI

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.

Using SelectColumns to add a new calculated column to the table in Power BI

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

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:

Checking the performance of AddColumns using DAX Studio

and an example of that using SelectColumns method:

Checking the performance of SelectColumns using DAX Studio

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:

    Enter Your Email to download the file (required)

    Video

    FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
    Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
    Reza Rad
    Trainer, Consultant, Mentor
    Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
    His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
    He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
    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.

    Leave a Reply