THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
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)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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,

John

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

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Archives

Privacy Statement