A few years ago I wrote this post on how to alias columns in a table in DAX, using a combination of AddColumns() and Summarize(). The good news is that in Excel 2016/the Power BI Designer/SSAS Tabular 2016 there’s a new DAX function specifically for this purpose: SelectColumns(). Here’s an example of how it can be used:
Imagine you have the following source table, called Products:
You can write a DAX query to get all the rows and columns from this table like so:
[sourcecode language=”text” padlinenumbers=”true”]
EVALUATE Products
[/sourcecode]
Here’s the output of that query in DAX Studio (and remember, DAX Studio can connect to data loaded into the Power BI Designer, which is what I’m doing here):
You can alias the columns in this table using SelectColumns() very easily, like so:
[sourcecode language=”text”]
EVALUATE
SELECTCOLUMNS (
Products,
"Column One", Products[Product],
"Column Two", Products[Colour]
)
[/sourcecode]
Here’s the output:
The syntax for SelectColumns() is straightforward: the first parameter is a table expression, and after that there are pairs of parameters consisting of:
- A new column name
- An expression returning a column from the table given in the first parameter
As you can see in the output of the query above, I’ve renamed the Product column “Column One” and the Colour column “Column Two”.
This means I can now crossjoin a table with itself without needing to worry about conflicting column names, like so:
[sourcecode language=”text”]
EVALUATE
CROSSJOIN (
Products,
SELECTCOLUMNS (
Products,
"Column One", Products[Product],
"Column Two", Products[Colour]
)
)
[/sourcecode]
One other interesting thing to note about SelectColumns() is that it allows you to do projection in a DAX query easily – as Marco notes here, it was possible before but it wasn’t pleasant. For example, the query:
[sourcecode language=”text”]
EVALUATE
SELECTCOLUMNS (
Products,
"Just Colour", Products[Colour]
)
[/sourcecode]
Returns:
Notice how there are three rows in the output here and that the value Green occurs twice. If you’re a true DAX afficionado, you might get excited about that.
great post, Chris.
it would be nice if this improvement could make its way to SSAS2012-2014 🙂
Reblogged this on SutoCom Solutions.
Definitely a better development experience for DAX novices used to writing SQL queries from SSRS or any client for that matter.
Why will the following not show unique values (i tried both distinct and Values, same result):
EVALUATE
SELECTCOLUMNS(
‘Calendar’ ,
“Just Color” , CALCULATE( DISTINCT( ‘Calendar'[Year] ) )
)
SELECTCOLUMNS doesn’t remove duplicates, and you are using DISTINCT/VALUES in the wrong place here. Try something like EVALUATE VALUES(‘Calendar'[Year])
Also note you can do more than alias:
• Rename/alias a column (as the article mentions)
• Retain a column as is
• Reuse the same column multiple times in expressions or as different types
SELECTCOLUMNS (
Products,
“Column One”, [Product], // Select column and alias
“Date”, [Date], // Select column and keep original name
“Year”, YEAR([Date]), // Select same column in expression
“Display”, FORMAT([Date],”Short Date”), // Select same column as different type
)
Aside: Looks like WordPress stripped tabs/spaces/formatting. I was thinking about switching my blog to WP, but I wish there were a way to give commenting developers more flexibility to keep code prettier. First world problems.
Chris – Fantastic! This is the solution I have been looking for! And you found it almost 5 years ago!
I wrote a VB macro to update all the headings to remove the square brackets. It works, then about two seconds later, Excel refreshes the headings with square brackets. The timing is pretty humorous, actually – “Yes!….No!” I feel like Excel is laughing at me. I am wondering if there might be some setting that will stop it from doing that.