Understanding DAX ALLSELECTED Function

The ALLSELECTED function in DAX has the reputation of being tricky. In this not so tiny post, I’ll do my best to cover all the nitty gritties of ALLSELECTED as clearly and as jargon free as possible.

Get a coffee and enjoy!

A Video will help a lot to understand the ALLSELECTED Function

ALLSELECTED Syntax & Explanation

The ALLSELECTED function can have any of the following parameters

• One or more columns (from the same table)
• A Table
• No Input

We’ll take a look all the 3 possibilities

= ALLSELECTED ( <columnName1>, <columnName2>... )   -- Single or Multiple Columns
= ALLSELECTED ( <table> )                           -- Table
= ALLSELECTED ( )                                   -- No Input

And here is the simplest way to explain what the ALLSELECTED does..

ALLSELECTED returns all the visible values in the filter context.

If you have understood (or perhaps not understood) the above statement, I’ll use different examples to repeatedly demonstrate the same.

To begin with there are 2 ways you can use the ALLSELECTED function.

1. Along with CALCULATE to modify / overwrite your current filter context.
2. As a Table to display all the visible values in the filter context.

Let’s hit on the use of ALLSELECTED with CALCULATE function and then use it as a Table.

ALLSELECTED on a Column

I am using the following Data Model

And the following Measures

Total Sales = SUM(Sales[SalesAmount])

AllSel on Category Column = 
CALCULATE(
    [Total Sales],
    ALLSELECTED(Products[Category])
)

And here is a simple Pivot Table

To understand whats going on, notice a few things..

1. For year 2003 we see 2 different numbers for Accessories and Bikes under Total Sales.
2. For AllSel on Category Column we see the SUM of Accessories and Bikes. The reason is, the ALLSELECTED function restores both Accessories and Bikes as visible filters and hence you see the SUM of both.
3. This is because we have used ALLSELECTED on a single Category Column.

This restoring of the visible values in the filter context is termed as Shadow Filter Context. I don’t know why that term but that is what the pros say! 🙂

What is Shadow Filter Context?

Don’t get confused by this fancy term, think of this as your regular filter context. Let me explain diagrammatically as simple as possible.

In simple words..

Restoring the *last visible filters on the visual is Shadow Filter Context.

*The word “last“, has a slight catch which I’ll explain a bit later.. you still with me?

ALLSELECTED on Multiple Columns

Consider a new measure (ALLSELECTED used on 2 columns – Category and Color) and the changes in the old measure in the following pivot table.

AllSel on Category & Color Column = 
CALCULATE(
    [Total Sales],
    ALLSELECTED(Products[Category], Products[Color])
)

Let’s evaluate the numbers marked as 1,2 & 3

1. Since ALLSELECTED is working only on category Column. Visible Filters are
   • Category = Accessories & Bikes (ALLSELECTED restored both the filters)
   • Color = Black is still applied.
   • Hence a + b
2. Although the filter context at the total level is Accessories but since the visible filters are restored, it becomes the total of Accessories and Bikes. i.e the value “c”
3. For the 3rd value the filters from both the columns are restored
   • Categories  = Accessories & Bikes
   • Color = Black and Silver
   • The total all visible values is “c” and hence that becomes number 3

Allselected on a Table

Consider this measure where ALLSELECTED is used on the Products Table.

AllSel on Products Table = 
CALCULATE(
    [Total Sales],
    ALLSELECTED(Products)
)

Here is a quick explanation..

• In this case visible values from all Product Table Columns will be restored.
• But the Year filter is still applied and hence you see different values for 1 and 2

Allselected with no Parameters

And if you remove all parameters from ALLSELECTED, this how it looks.

AllSel Blank = 
CALCULATE(
    [Total Sales],
    ALLSELECTED()
)

• When you don’t input any parameters, it looks for all columns and their visible values.
• The visible values in this case are
  • Year = 2001, 2002, 2003, 2004
  • Category = Accessory and Clothing
  • Color = Black and Silver
• Once these filters are restored what you see is the grand total of Total Sales displayed throughout.

ALLSELECTED as a Table

Consider the following measure.

AllSelected SUMX = 
SUMX(
    ALLSELECTED(Products[Category]),
    [Total Sales]
)

and it’s result..

Notice that

1. I am using the ALLSELECTED Function to create a table for SUMX to iterate on.
2. For the first number, although the Category is Accessories but the ALLSELECTED again restores the filter context and brings all 3 visible / selected categories i.e. “Accessories, Bikes and Clothing”
3. The SUMX loops through each one of them and then totals the sales value for each.

Even though used as a table, the native behavior of ALLSELECTED does not change.

A Catch in Shadow Filter Context

Remember the asterisk (*), I placed at the start of this post while explaining Shadow Filter Context.

“Restoring the *last visible filters on the visual is Shadow Filter Context”

Let’s explore the catch in the word “Last”. Consider the following measure

AllSel Category Names = 
CONCATENATEX(
    CALCULATETABLE(
        ALLSELECTED(Products[Category]),
        Products[Category] IN {'Clothing', 'Accessories'}
    ),
    Products[Category],
    UNICHAR(10)
)

And those were my 2 cents about ALLSELECTED..

My god, I can’t imagine I am finally closing this post that at the start seemed it would never end. If you have been with me all this while, I bet you have questions, please post them in the comments below.

I’ll be glad to help