THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

The ALLSELECTED function under the cover #dax #tabular #powerpivot #powerbi

I and Alberto Ferrari recently completed the writing of The Definitive Guide to DAX, and we spent months to correctly describe the internals of evaluation context in this language. There are many details that make data model working with both DAX and MDX, and sometime there are behaviors that are not intuitive to understand.

A function that seems to work like magic is ALLSELECTED, which is very useful when you create measures that will be used in Excel pivot tables. What is not obvious is that the DAX engine has to realize what the user is selecting on a pivot table that generates a query in MDX. In reality, there is no other communication between client and server other than the MDX query, and ALLSELECTED is not related to MDX, it is a DAX function!

Alberto extracted from the book part of this description and published the Understanding ALLSELECTED article on SQLBI. You will see that the magic in this function is just a particular manipulation of the filter context, which keeps track of the iterated table in the filter context every time a context transition happens. Not clear enough? Well, the article explains this better!

Published Wednesday, August 5, 2015 11:42 AM by Marco Russo (SQLBI)



John said:

Hi Marco,

What is the equivalent of ALLSELECTED in a MDX query?  Does it use a dynamic named set or some other method?  If a dynamic named set is used, is the dynamic named set created in Vertipaq at design or query run time?  Is it possible to replicate the ALLSELECTED behavior in a Multidimensional measure without using a dynamic named set?

Thank you,


July 4, 2017 3:40 AM

Marco Russo (SQLBI) said:

John, you don't have an equivalent of ALLSELECTED in MDX.

July 11, 2017 7:55 PM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement