Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RobertSlattery
Resolver III
Resolver III

Allselected function not removing row context for date hierarchy

Why does AllSelected not deliver the same behaviour in the two cases below? 

startDate = CALCULATE(
    FIRSTDATE('Dim Date'[DateRaw].[Date]),
    ALLSELECTED('Dim Date'[DateRaw].[Date])
)

image.png

8 REPLIES 8
Phil_Seamark
Employee
Employee

HI @RobertSlattery

 

I've edited this reply to remove my original answer because it was wrong.  Please see my next reply.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

 

ALL and ALLEXCEPT don't help me because I need to preserve the page context.

You are quite correct and my answer was poor.  In fact it woke me up to come and fix.  The ALLSELECTED function will only remove filters from tables or columns internal to your query (visual)

 

The syntax you are using only removes the filter column from a specific column in the hidden date table and nothing else.  

 

You are using ALLSELECTED to clear filters from the folloing column in your measure

Dim Date'[DateRaw].[Date]

 

Whereas you are ACTUALLY using the following column in your query

 

 

'Dim Date'[DateRaw].[Day]

 

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.

 

If you change your measure to the following, your measure will now return the same value

 

startDate = CALCULATE(
    FIRSTDATE('Dim Date'[DateRaw].[Date]),
    ALLSELECTED('Dim Date'[DateRaw].[Day])
)

image.png

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks for the insights, it is helping me to reason about this, even though your suggestion doesn't fix the problem.  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.  Second, your method ignores any slicer on the page which defeats the purpose of the exercise.

 

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.

 

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.  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...

image.png

 

 

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.  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.  So, there is no way that I can see, to use the automatic hierarchy in a visual in conjunction with a slicer.

 

My solution was to create my own Hierarchy that includes the [Date] column and use that column in the slicer.

 

image.png 

Then use the insights given by @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.

 

image.png 

The only thing to be careful of here is the sort order for the text, Month field.  You need to include a Month Number field and sort it by that...

image.png 

 

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.

Hi @RobertSlattery

 

I wasn't trying to suggest a solution to a problem you were grappling with.  I was just trying to explain why the two charts you posted returned different results for the same query.   My suggestion was just to show how you might modify the syntax if you wanted to align the two visuals.  Hopefully I was able to help shed some light on one of the finer details of DAX Data Modelling in Power BI. 🙂

 

The "filty hack" that you describe actually creates entire tables in the background.  Not just add columns (which I guess can be considered adding columns via a relationship).  It was added to Power BI to make life easier for newer users.  I'm not a fan and turn it off in the options when I'm building data models and always use my own date tables.

 

image.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark, your reply was indeed extremely helpful as I mentioned.

 

The only reason I use the Auto Date/Time feature is because the automatic hierarchy supports a Continuous X-axis like this...

 

 image.png 

but, if I roll my own hierarchy, it forces a Category axis...

image.png 

which is anoying.

 

The axis is forced to Category mode when it's dealing with a non-Date/Time field.  The field you are using at the bottom level is Integer (Day of the month).  

 

If you use a DateTime field the axis will auto-scale


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Yes I did try that as one of the many permutations and I got this...

image.png 

But after reading your suggestion I realised there was one more permutation: using the go to next level buttonimage.png instead of the expand down ne level button with this on the axis: image.png

This works for the lowest level but, not for the Month level...

image.png 

The months summarised without considering the years and even if you set the sort order to Month Number, still don't sort in order.  It's like they thought of everything and systematically eliminated the options to bump their frustrated customer KPI.

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.