Chris Webb's BI Blog

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

Aliasing Columns in DAX

with 2 comments

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

2 Responses

Subscribe to comments with RSS.

  1. This column aliasing technic is key when dealing with many-to-many relationship.
    The CrossJoin will not work until you have renamed one of the Key.
    When using a Bridge Table usually you will keep the original name of the Keys. It’s not an issue until dealing with DAX Queries.

    EXAMPLE
    BridgeTable1Table2 (FK1,FK2)
    DimTable1 (FK1, Attribute1)
    DimTable2 (FK2, Attribute2)
    FactTable (Key1,FK1,Attribute3)

    Until you rename FK1 in BridgeTable1Table2 or FactTable, you will not be able to do a CrossJoin on FactTable and BridgeTable1Table2 (required to propagate filters)

    grumelo

    April 21, 2015 at 5:21 pm


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 4,002 other followers

%d bloggers like this: