Allselected function not removing row context for date hierarchy

Question

Why does AllSelected not deliver the same behaviour in the two cases below?&nbsp;startDate = CALCULATE(
    FIRSTDATE('Dim Date'[DateRaw].[Date]),
    ALLSELECTED('Dim Date'[DateRaw].[Date])
)

robertslattery · Answer

Yes I did try that as one of the many permutations and I got this...&nbsp;But after reading your suggestion I realised there was one more permutation: using&nbsp;the go to next level button&nbsp;instead of the expand down ne level button with this on the axis: This works for the lowest level but, not for the Month level...&nbsp;The months summarised without considering the years and even if you set the sort order to Month Number, still don't sort in order.&nbsp; It's like they thought of everything and systematically eliminated the options to bump their frustrated customer KPI.&nbsp;&nbsp;&nbsp;&nbsp;

phil_seamark · Answer

You are quite correct and my answer was poor.&nbsp; In fact it woke me up to come and fix.&nbsp; The ALLSELECTED function will only remove filters from tables or columns internal to your query (visual)
&nbsp;
The syntax you are using only removes the filter column from a specific column in the hidden date table and nothing else.&nbsp;&nbsp;
&nbsp;
You are using ALLSELECTED to clear filters from the folloing column in your measure
Dim Date'[DateRaw].[Date]
&nbsp;
Whereas you are ACTUALLY using the following column in your query
&nbsp;
&nbsp;
'Dim Date'[DateRaw].[Day]
&nbsp;
There is a very subtle difference and means your ALLSELECTED function not removing the filter off the .[DAY] and this filter is being propogated back to the 'Dim Date' table and restricting the rows visible to the FIRSTDATE function.
&nbsp;
If you change your measure to the following, your measure will now return the same value
&nbsp;
startDate = CALCULATE(
    FIRSTDATE('Dim Date'[DateRaw].[Date]),
    ALLSELECTED('Dim Date'[DateRaw].[Day])
)

&nbsp;
&nbsp;

phil_seamark · Answer

HI&nbsp;RobertSlattery
&nbsp;
I've edited this reply to remove my original answer because it was wrong.&nbsp; Please see my next reply.

robertslattery · Answer

Hi&nbsp;Phil_Seamark I don't understand your answer.&nbsp; In my experience, AllSelected removes the row context and preserves the visual state (as determined by the visual).&nbsp; This is exactly what it does for the right-hand side version, where I selected the date rather than the date hierarchy.&nbsp; In this case it removes the row context and I am left with the page context as defined by the slicer.&nbsp; How is this different to the left hand version?&nbsp;ALL and ALLEXCEPT don't help me because I need to preserve the page context.

robertslattery · Answer

Thanks for the insights, it is helping me to reason about this, even though your suggestion doesn't fix the problem.&nbsp; If you scroll down in your example, you will find that the start date you are calculating is actually the start date for the row month, it is not constant.&nbsp; Second, your method ignores any slicer on the page which defeats the purpose of the exercise.&nbsp;Based on your explanation, I realised that the hierarchy, filthy hack that it is, is simply adding columns and all of these columns that are involved in the row context, need to have their filters stripped back to the visual level.&nbsp;The point is, I am slicing (at page level) on 'Dim Date'[Date] but my visual is using the Date Hierarchy so I need to remove the row context from all of the hierarchy columns that are involved.&nbsp; There are two ways to do this...startDate3 = CALCULATE(
    FIRSTDATE('Dim Date'[Date]),
    ALLSELECTED('Dim Date'[Date].[Year]),
    ALLSELECTED('Dim Date'[Date].[Month]),
    ALLSELECTED('Dim Date'[Date].[MonthNo]),
    ALLSELECTED('Dim Date'[Date].[Day])
)startDate2 = CALCULATE(
    FIRSTDATE('Dim Date'[Date]),
    ALLSELECTED('Dim Date')
)As explained above, your suggestion, while very helpful, does not do this...startDate = CALCULATE(
    FIRSTDATE('Dim Date'[Date].[Date]),
    ALLSELECTED('Dim Date'[Date].[Day])
)And the results are sumarised below...&nbsp;&nbsp;Now I have the problem though, that, because the hierarchy elements in my visual are not filtered by the slicer, I see all dates rather than all visual dates.&nbsp; In other words, the filthy hack of a date hierarchy doesn't propagate the date filter to the hierarchy columns AND it is not possible to use 'Dim Date'[Date].[Date] on the slicer.&nbsp; So, there is no way that I can see, to use the automatic hierarchy in a visual in conjunction with a slicer.&nbsp;My solution was to create my own Hierarchy that includes the [Date] column and use that column in the slicer.&nbsp;&nbsp;Then use the insights given by&nbsp;Phil_Seamark to re-write the measure targeting that hierarchy...startDate4 = CALCULATE(
    FIRSTDATE('Dim Date'[Date]),
    ALLSELECTED('Dim Date'[Year]),
    ALLSELECTED('Dim Date'[Month]),
    ALLSELECTED('Dim Date'[Month Number]),
    ALLSELECTED('Dim Date'[Day Number])
)or, if I don't need to be so specific...endDate2 = CALCULATE(
    LASTDATE('Dim Date'[Date]),
    ALLSELECTED('Dim Date')
)and these work.&nbsp;&nbsp;The only thing to be careful of here is the sort order for the text, Month field.&nbsp; You need to include a Month Number field and sort it by that...&nbsp;&nbsp;After going through all this, the Line Graph visual insists on labeling every day at the lowest level so it's impossible to see the entire picture at day-level granularity.

Forum Discussion

Allselected function not removing row context for date hierarchy

9 Replies

Related content

Allselected function

Using ALLSELECTED in Divide function

Date hierarchy

Optimize AllSelected

include filters in allselected function