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!
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).
But watch what happens when I select just the Awesome and Lame levels:
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.
- 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
Scott Senkeresty here… Founder and Owner of Tiny Lizard. Yes, I have done some stuff: A Master’s Degree in Computer Science from California Polytechnic State University in beautiful San Luis Obispo, California. Over twelve years developing software for Microsoft, across Office, Windows and Anti-Malware teams… Read More >>
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
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 🙂