Tabular - Timeline slicer and Date Table not possible with null dates - by CUArmy

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 800310 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 9/11/2013 2:55:29 PM
Access Restriction Public


If for large models--and generally as a best practice--you choose to use an integer to represent the DateKey in a time dimension, you must then use an additional field as the DateValue which would have a data type of Date in Tabular. To then represent missing or unknown dates, you would need a dummy row, and you could choose either a strange DateValue like 1900 or, better, you can simply leave the DateValue blank for this record; this is especially important if your company is actually old enough to have fact data this old. Finally, you want to take full advantage of the Time Intelligence features in Tabular, so you use the "Mark as Date Table" option and you choose the DateValue column. 

No errors are immediately thrown for your lone NULL DateValue, but your model will no longer process because the "Mark as Date Table" function has set the DateValue column as the row identifier which cannot have NULL values. This doesn't make a lot of sense and it side-steps a best practice for performance of using int or bigint types for surrogate keys. Moreover, if you actually try to use a date for your missing/unknown date row, your timeline control will not make sense, as it will incorporate this outlier-date into the timeline (also know that Excel--with the Timeline slicer--will not allow dates older than 1900 or greater than 9999--both of which seem to be unreasonable for missing/unknown dates).
Sign in to post a comment.
Posted by CUArmy on 9/12/2013 at 3:54 PM
Yes, this is the how we solved the problem, and it definitely makes sense. Thank you!
Posted by Microsoft on 9/12/2013 at 3:31 PM
Thanks for filing this.

Date tables in PowerPivot must by definition have a contiguous set of dates. This is the way that the feature was designed. You can use surrogate keys that are integers, but using a blank date causes lots of problems for the way the formula engine works with dates in DAX functions.

We do have another way of handling data for which the matching date is not in the table. You don't need to add your own row with a surrogate key of 0. Instead, PowerPivot will automatically create a blank row that is matched up with any transaction dates that aren't included in the date table.

Please let us know if we misunderstand the scenario.

Analysis Services Product Team