“Mark as Date Table”: When not to use it

In PowerPivot 1 (2008 R2 version), you had to specify an extra parameter in your Time Intelligence calculations when the relationship between the fact table and the date table was based on smart key integers:

=TOTALYTD(

SUM( FactResellerSales[SalesAmount] ) ,

DimDate[FullDateAlternateKey] ,

ALL(DimDate)

)

If you didn’t do that, the output would incorrectly show the figures only for the current month in context, instead of the YTD amount (or QTD, etc).  The only other way to avoid this behavior was creating the relationship between the tables using date types instead of integers, which is not generally the case when reading from relational databases.

In PowerPivot 2 (2012 version), a new option was added to the PowerPivot Window ribbon to help avoid this confusion and add consistency to the output of Time Intelligence calculations.   This button is called ‘Mark as Date Table’ and when using it, you can forget about the need to specify ALL in your time-driven calculations as it is not necessary.

image

image

Interestingly, though, the fix for one problem can be the genesis of another one.  Imagine for a moment we want to create a report in which we are displaying monthly and daily values, yet we want to clear the filter context at the daily level.  In other words, we want the daily values to display the monthly amount.

After we have configured the table as a ‘Date Table’, we are unable to get the proper value. As an example, lets compare these two DAX expressions. They clear the filter context on either the smart key or the date type on the calendar table:

Measure using Integer Type:=CALCULATE( SUM( FactResellerSales[OrderQuantity] ), ALL(DimDate[DateKey] ) )

and

Measure using Date Type:=CALCULATE( SUM( FactResellerSales[OrderQuantity] ), ALL(DimDate[FullDateAlternateKey] ) )

What we want to test is the ability to get monthly values at the day level.  The following matrix shows success/failure on the desired output:

  Measure using Integer Type Measure using Date Type
Relationship based on Integers (smart keys) Fails: filter context not affected Fails:  filter context removed completely
Relationship based on Dates Fails: filter context not affected Fails:  filter context removed completely

Interestingly, though, if we remove the demarcation as ‘Date Table’, we get the following:

  Measure using Integer Type Measure using Date Type
Relationship based on Integers (smart keys) Fails: filter context not affected Succeeds: Filter context set properly
Relationship based on Dates Fails: filter context not affected Fails:  filter context removed completely

One combination succeeds, and allows us to show the monthly values at the daily level when using the date type column on the row labels.  Unchecking the ‘mark as date table’ option of the calendar table and using smart keys to enable the relationships, we can then clear the filter context by using ALL over the date type column (FullDateAlternateKey):

image

If you need to absolutely go this way and remove the demarcation of date tables to enable this functionality then you will have to go back to using ALL as an extra parameter to time intelligence functions.  Although a bit annoying, at least is good to have the work around handy.

Advertisements

3 Responses to “Mark as Date Table”: When not to use it

  1. Colin Banfield says:

    Javier,

    Very interesting observation. The lingering question is, what is the reason for this behavior? Do you plan to follow up with an explanatory post?

    • javierguillen says:

      Hi Colin,

      Although the behavior seems buggy, it is never a good idea to clear the filter context on the column that connects two tables. For example, if I have a product table and a sales table joined by the ProductKey, and I create a calculation that uses ALL on that field, the output will again ignore the command to clear the filter (the output will be the same as the regular additive measure).

      So this not-very-intuitive output can be replicated with non-date types as well. The interesting thing – though – is that when marking a table as date, PowerPivot/Tabular appears to internally use the date type as part of the relationship… even if the relationship was defined using integers. That would make sense in a way, as marking as date forces you to specify a date type and changes the nature in which the context is propagated (to enable easier time intelligence).

      I am interested in investigating this further, and will post a follow up blog if I uncover something else.

      Javier

      • Colin Banfield says:

        Thanks for the response! I look forward to hearing what you discover upon further investigation. One issue with unchecking Mark as Date Table is that you lose date spefic filtering options when you add a date column to the row or column area of the PivotTable. This isn’t a problem for folks that never use these filters though.

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

%d bloggers like this: