DAX Queries, Part 3

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.

3 thoughts on “DAX Queries, Part 3

  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

  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

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