Power Pivot Principles: The A to Z of DAX Functions – ALLSELECTED

In our long-established Power Pivot Principles articles, we are starting a new series on the A to Z of Data Analysis eXpression (DAX) functions. This week we look at the ALLSELECTED function – which is a very useful tool…

The ALLSELECTED function

Consider the following PivotTable:

Let’s do something this time that is actually quite awkward to code in Excel.  We’ll start by creating the following measure:

Our PivotTable may look like this now:

Big deal, you might say – this is akin to the ALL function discussed a couple of months ago (July newsletter). It’s not though; it is a big deal. To demonstrate, let’s also insert a slicer for the months (i.e. the field that is the subject of the ALLEXCEPT function):

Choosing months on the slicer will result in the PivotTable recalculating the total sales for the months selected:

This only considers the months selected, not all months. Now that is not straightforward in Excel, especially if we were to apply ALLEXCEPT to a field that does not appear in the PivotTable.

For le
coup de grâce, let’s create the following measure:

If you don’t know what the BLANK() function is, it simply returns blank (nothing), which may suppress the context in a PivotTable.

Our PivotTable will now have different weightings of sale percentages depending on our selection of months and suppress any missing data / zero values:

Come back next week for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.