Loosen restrictions on contiguous date selections error - by Christopher Webb

Status : 


Sign in
to vote
ID 565032 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 6/3/2010 2:03:35 PM
Access Restriction Public


Having played around with DAX time intelligence functions, I've got the ‘Function XXXX only works with contiguous date selections’ error a few times. After some email correspondence with Marius Dumitru I understand the reason why this error is occurring, but in at least one case I've found a query where I get the error and I think I should be getting results. 

Consider a previous period growth calculation. If you put dates on rows and measures on columns, then for each date you would expect to see the previous period growth calculation return results and you do. Then imagine you want to see growth for all dates in January 2002 and January 2003, so you can compare the growth values - you add year to slice and choose 2002 and 2003, then add month and choose January, and you get the error. However in this case the slicers are only being used to control which dates are being displayed on rows, and it still makes sense to see the result of the calculation for an individual date.
Sign in to post a comment.
Posted by Cos2008 on 11/14/2013 at 6:12 AM
this issue was posted in 2010 and Microsoft has done nothing about it? We're continuing to implement Tabular in a large healthcare organization, but with issues like this (and we've stumbled on this already), it makes it very difficult to embrace this product, as a mature product, across the company!!
Posted by Microsoft on 7/12/2010 at 3:53 PM
Chris -- Thank you for filing this design change request. We will consider your suggestion for a future release of SQL Server.

Thank you for your assistance,
The Analysis Services team
Posted by Tomislav Piasevoli on 6/3/2010 at 3:11 PM
I agree. Moreover, I think it should work always, not in a special case with dates only. If you'll support it in future, enabling it for any scenario is easier and efficient than checking when to enable and when not to enable. And since DAX operates on leaves, there's always a collection of dates that satisfy such time expressions. Leaving the results on dates only or aggregating them when i.e. quarters are on rows/columns (however non-sense it may seem) makes no difference technically. End users will learn eventually what makes sense and what is too complicated to explain to themselves or to their superiors/collegues. But when they need it, as in Chris' example on his blog, they'll appreciate the possibility and not complain about restriction. That's how I think at least. And I remember what "arbitrary shaped sets" ment when you worked with MDX in SSAS - look for another way how to solve the problem, break them down by yourself or similar. There it made sense from the performance perspective if nothing else, here there's no need for that other than being too cautious about how end users might hurt themselves, I presume. How about a little faith in them?

Excel has faith in users when they enter numbers as strings. It just puts a small hint beside the cell. Could it be done here too? Not in the cell, might be too much, but in the column name instead? A hint which says there's a possible wrong interpretation in result? That way you don't prevent, you enable and guide which is a better option.