Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Aliasing Columns in DAX

leave a comment »

Creating a copy of a column with a new name is pretty simple in DAX: you can just use the AddColumns() function. For example if we take a model with the DimProductCategory table from Adventure Works in, we could create a copy of the EnglishProductCategoryName column like so:

evaluate
addcolumns(
DimProductCategory
, "Copy of Category Name"
, DimProductCategory[EnglishProductCategoryName])

image

However, in some calculations and queries I’ve been playing around with, this isn’t enough: I’ve not only needed to create a copy of the column but also to remove the original. So for example to crossjoin the DimProductCategory table with itself and get a cartesian product of all the possible combinations of Category name you can’t just do this:

evaluate
crossjoin(
summarize(
DimProductCategory
, DimProductCategory[EnglishProductCategoryName])
,
summarize(
DimProductCategory
, DimProductCategory[EnglishProductCategoryName])
)

Because you’ll get the following error:

 Function CROSSJOIN does not allow two columns with the same name ‘DimProductCategory’[EnglishProductCategoryName].

What you need to do is add the new column with AddColumns() and then use Summarize() to get a new table that only contains the values in this new column, like so:

evaluate
crossjoin(
summarize(
DimProductCategory
, DimProductCategory[EnglishProductCategoryName])
,
summarize(
addcolumns(
DimProductCategory
, "Copy of Category Name"
, DimProductCategory[EnglishProductCategoryName])
, [Copy of Category Name])
)

image

Written by Chris Webb

January 12, 2012 at 12:01 pm

Posted in DAX

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,857 other followers

%d bloggers like this: