Partners,,,, russianxnxx

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.



I am using the following Data Model

Data Model

And the following Measures

Total Sales = SUM(Sales[SalesAmount])
AllSel on Category Column = 
    [Total Sales],

And here is a simple Pivot Table

AllSelected on a Single Column

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.

Shadow Filter Context


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 = 
    [Total Sales],
    ALLSELECTED(Products[Category], Products[Color])

DAX AllSelected on 2 Columns

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 = 
    [Total Sales],

DAX AllSelected on full Table

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 = 
    [Total Sales],

DAX AllSelected no Parameters

  • 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.



Consider the following measure.

AllSelected SUMX = 
    [Total Sales]

and it’s result..

DAX AllSelected as Table

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 = 
        Products[Category] IN {'Clothing', 'Accessories'}

and the results..

DAX AllSelected last shadow filter context

  • Although you might think that the visible categories are – “Accessories, Bikes and Clothing”, but notice the ALLSELECTED wrapped in the CALCULATETABLE where I modified the visible filters to only show 2 of them – “Clothing and Accessories”
  • My Point – The Filters restored, are last visible filters to the ALLSELECTED function. By last I mean the filters which are the closest / explicit to the ALLSELECTED function. It would be fair to re-iterate that both CALCULATE and CALCULATETABLE create explicit filters.

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

    ⬇️ Pop in your Name & Email to get the file!


    Tagged In .

    Topics that I write about...