Chris Webb's BI Blog

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

DAX Queries, Part 3

with 3 comments

Following on from my previous post, let’s now look at how you can add derived columns to a table expression. Let’s start with a simple query that returns every column in DimDate:

evaluate(
DimDate
)

If I want to add some columns to this query without doing any grouping we can use the AddColumns function like so:

evaluate(
AddColumns(
DimDate
, “Calendar Year Name”
, “Calendar Year ” & DimDate[CalendarYear]
)
)

image

Here I’m adding a new column (shown on the far right in the resultset in the above screenshot) called Calendar Year Name that does a simple concatenation of the string “Calendar Year” with the actual Calendar Year value from the table. You can add multiple columns in this way with more than one column name/expression pair.

The important thing to notice here is that you can’t use this new column to group by in a Summarize() function, so the following query:

evaluate(
Summarize(
AddColumns(
DimDate
, “Calendar Year Name”
, “Calendar Year ” & DimDate[CalendarYear]
)
, [Calendar Year Name]
–, DimDate[CalendarYear]
, “Sum of Sales”
, Sum(FactInternetSales[SalesAmount])
)
)

image

…gives results you may not be expecting: instead of getting the sum for each year, you get the sum for all years repeated; I’m told this scenario may be changed to throw an error at RTM. AddColumns() should only be used for formatting a resultset.

If you uncomment the line grouping by DimDate[CalendarYear], you’ll see you get meaningful results:

image

 

In part 4, I’ll take a look at the Crossjoin() function.

Written by Chris Webb

July 18, 2011 at 4:08 pm

Posted in DAX

3 Responses

Subscribe to comments with RSS.

  1. I’m wondering how to explain row context and filter context when you start using aggregation functions.
    Now it is clear to me why you get these results:
    – AddColumns defines a Row Context for each row of the table passed as first parameter
    – The Summarize gets the table passed as first parameter and applies a filter context – however, because you grouped on a calculated column, the filter context doesn’t automatically propagate to other existing tables (it’s like having no relationships between tables)
    – When you add the DimDate[CalendarYear] column, you are referencing a column of the original table and in this case the filter context propagates over relationships

    But I spent several minutes to understand it and I’m still not sure how to explain the general behavior… :)

    Marco

    Marco Russo

    August 16, 2011 at 2:52 pm

  2. Just run the query against the RTM version.

    evaluate(
    Summarize(
    AddColumns(
    Date
    , “Calendar Year Name”
    , “Calendar Year ” & ‘Date’[Calendar Year]
    )
    , [Calendar Year Name]
    , “Sum of Sales”
    , Sum(‘Internet Sales’[Sales Amount])
    )
    )
    –This query still give you the same amount for all [Calendar Year Name]. It is not throwing error.

    evaluate(
    Summarize(
    AddColumns(
    Date
    , “Calendar Year Name”
    , “Calendar Year ” & ‘Date’[Calendar Year]
    )
    , ‘Date’[Calendar Year]
    , [Calendar Year Name]
    , “Sum of Sales”
    , Sum(‘Internet Sales’[Sales Amount])
    )
    )
    –This query give you the correct answer

    George Qiao

    April 4, 2012 at 1:15 pm

  3. [...] part 3, I’ll take a look at how to add derived [...]


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 3,296 other followers

%d bloggers like this: