Tiny Lizard

Power Pivot Consulting and Training

  • About
  • Blog
  • Power Pivot
  • Training
  • Contact

ALL() vs ALLSELECTED()

September 7, 2014 By Scott Senkeresty 2 Comments

I rarely use ALLSELECTED().  Last time I did, I had to go look at the MSDN docs, never a good sign.  But I have to say, unlike some of the docs… this one seems both accurate and understandable!

image

I decided to play with it a bit today; here are the measures I tested with:

Total Intensity:=SUM(Colors[Intensity])
Total Intensity, ALL Level:=CALCULATE([Total Intensity], ALL(Colors[Level]))
Total Intensity – ALLSELECTED Level:=CALCULATE([Total Intensity], ALLSELECTED(Colors[Level]))

And here is my test pivot before I sliced anything.  Notice both ALL and ALLSELCTED are showing the same thing (just clearing the filter context on the Level field).

image

But watch what happens when I select just the Awesome and Lame levels:

image

As expected, the ALL() is unchanged.  However… the ALLSELECTED is now showing the total of (just) all the Awesome and Lame values (notice the 4954.0 in the Total Intensity Grand Total … same in all rows of ALLSELECTED column).

In this way, the measure using ALLSELECTED is returning the “Visual Total”.  As the MSDN definition says, ALLSELECTED is removing ONLY filters created by the rows/columns of the pivot table.   All other filters are respected.  We saw the Slicers respected here, but I also tested with report filters and Value/Label filters (like Top 10 and Greater Than), and they behaved like slicers.  And for clarity, ALL() on the other hand doesn’t care where the filter came from… it is gone.

A pretty useful tool in your belt, in my opinion, especially when you want these types of “visual totals” or “% of Visual” measures.

Bug in SAMEPERIODLASTYEAR()?
DAX Table Query Example

Filed Under: Power Pivot

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 280 other subscribers

Comments

  1. Ben says

    March 26, 2015 at 6:38 pm

    Thanks for the post. I’ve noticed that when I remove all the filters for a slicer it breaks the ALLSELECTED functionality. For example, if I use it to calculate a percentage, it will assign 100% to sub-levels within the visible total.
    Is this a known issue?

    Thanks,

    Ben

    Reply
    • Scott Senkeresty says

      April 1, 2015 at 8:33 pm

      Hi Ben, sorry for the delayed reply. I don’t know of any issues here, and fear I am not totally getting you. In my example above, when Level has nothing sliced (so, no filters) — my measure is returning the total of all rows — which is what I would expect.

      If you want to send me a sample workbook, or jump over to the Mr Excel powerbi forums… I am sure we can get to the bottom of this 🙂

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Blog Posts

  • The streak is alive! August 10, 2017
  • DAX KEEPFILTERS Plus Bonus Complaining! July 20, 2017
  • VAR: The best thing to happen to DAX since CALCULATE() July 11, 2017
  • Review: Analyzing Data with Power BI and Power Pivot for Excel June 20, 2017
  • Power BI Date Table June 2, 2017

Follow on Twitter

My Tweets

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 280 other subscribers

Copyright © 2022 Tiny Lizard