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]
)
)
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])
)
)
…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:
In part 4, I’ll take a look at the Crossjoin() function.
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
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
Very late to the party (!), but it now looks like the original query works. It produces the correct aggregation on ‘Internet Sales'[Sales Amount] without the need for ‘Date'[Calendar Year]