57.1 DAX Formulas for PowerPivot – Chapter 9

We were introduced to the ALL() function in the previous chapter. This chapter goes into more details on ALL() and its siblings, ALLEXCEPT() and ALLSELECTED().

As mentioned in the last chapter, ALL() can be used in the filter arguments of the CALCULATE() function to clear the currently applied filters from the table or columns passed to the ALL() function. When you pass a table as the argument, filters will be cleared for all columns in the table. When you pass individual column arguments, all columns must belong to the same table. Two examples are given for using the ALL() function: percentage of parent and showing the percentage sales based on products selected in a slicer.

ALL() has a couple of related functions: ALLEXCEPT(), handy when you want to remove filters from almost all the columns of a table; and ALLSELECTED(), which keeps all filters except row and column filters. An example of ALLSELECTED()  is shown that allows ALL() functionality but filtered by a slicer control. This is kind of like visual totals for those who know SSAS multidimensional modeling.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s