skip to Main Content

 
No Filter

 

“He has no filter. He says whatever comes through his mind. He never edits himself.”

 

 

Revisiting ALL()

Yes, I’ve used ALL() a number of times already.  But I’ve never covered it directly – it’s always been a side topic to something else.  So I thought it was time I went back and made sure I had a specific post on it.

The short version is: ALL() removes filters from specific fields during a measure calculation.  It “De Niros” your measure… but in a controlled manner.

Syntax

    ALL( table_or_column, [column1], [column2], etc.)

So the first parameter is required, and can either be a single column or an entire table.

After that, you can specify as many other columns as you’d like…  as long as the first parameter didn’t specify a table. If your first  param is a table, you can’t specify additional columns.

Also note that even when specifying columns, the ALL() function requires you to always include the table name.

Lastly, all columns listed must be from the same table.

Legal Examples:

  1. ALL(Table1)
  2. ALL(Table1[Column1], Table1[Column2])

Illegal Examples:

  1. ALL([Column1])
  2. ALL(Table1, Table1[Column3])
  3. ALL(Sales[Column1], Customer[Column2])

When to use ALL() – Example #1

You never use ALL() by itself.  You always use it in the context of another function.  It’s all about setting context for a calculation.  It is not a calculation unto itself.

The simplest place to use ALL is in a measure that always shows you a grand total of something, regardless of what filters are in play.  Revisiting the Temperature Mashup demo for a moment, here’s a pivot sliced by Temperature, showing a normal Sum of Order Quantity:

Sum of Qty Only 
Now I add a measure that sets the Temperature table to ALL:

    [AllTempsQty] = CALCULATE(
                      SUM(Sales[OrderQuantity]), 
   
                    ALL(Temperature)
                    )
 

That ends up looking like:

PowerPivot Measure using ALL

(If you need a refresher on CALCULATE, which is probably the most useful function in all of DAX, check out this post.)

So, on Hot days, we sold a total of 2,245 Accessories.  And overall, across all temperatures, we sold 36,092 Accessories.

But note that the numbers are different across Category for both measures.  That’s because [AllTempsQty] did not use ALL against the Category columns (or table).

Cool.

Example #2 – % of Total

OK, but in many cases, you don’t actually want to see [AllTempsQty] in the pivot.  You just want to compare the filtered version of a measure to the unfiltered version.

So we can define something like:

  [PctOfAllTempsQty] = SUM(Sales[OrderQuantity])  / 
                       CALCULATE(
                          SUM(Sales[OrderQuantity]),
                          ALL(Temperature)
                       )

Which gives us:

Percentage of Total PowerPivot Measure using ALL function

Note that I switched Excel’s number formatting for this measure to be Percentage, keeping me from having to include * 100 in my measure.

Of course, if I wanted to keep my [AllTempsQty] measure, then I could have defined my new percent total measure as the following instead:

  [PctOfAllTempsQty] = SUM(Sales[OrderQuantity]) / 
                       [AllTempsQty]

                    

And that would have produced the same results.

Example #3 – Using ALL() in places other than CALCULATE()

If we revisit the post on derived measures, ALL() is an excellent modifier to use against an existing measure.

Let’s take a measure I defined in the Temperature Mashup, the [Qty per Day] measure:

Quantity per Day

And then we define a new measure:

  [QtyPerDayAllTemps] = [QtyPerDay](ALL(Temperature))
                 

That yields:

Using ALL function to derive another measure

You like?  I like 🙂

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 7 Comments
  1. You refer to AllTempsQty as a measure, but I thought that measures could only be used in the Values area of a pivot table.

    How did you use a measure as a column label? Or is this really a calculated column??

    The ALL function seems to be what I’m looking for. I’m trying to include a complete fiscal year column next to year to date columns…with multiple value fields.

    Thanks!

    1. Hi Chris. AllTempsQty is indeed a measure. The label for it does appear as a column header, but the data for it is indeed in the “values” area of the pivot.

      That’s just the default behavior in Excel 2007 and above – measures lay out as columns, even though they are not in the Columns area of the pivot.

  2. Hi guys. First of all thanks for your attention. Easy question

    I’m using this syntax in excel power pivot table

    =DISTINCT(ALL(Table2[sedi]) to get as result all the unique values that the field “sedi” displays, despite filters applied in the corresponding pivot table

    But i got as result “The DISTINCT function expects a column reference expression for argument ‘1’, but a table expression was used.”

    Any tips???

    thnx
    Dave

  3. Hi Rob,

    I have the following calculated columns in a ‘Product’ table (ProductKey has no duplicates)

    A) ‘Product’[Test 1]=CALCULATE(VALUES(’Product’[ProductKey])) yields the ProductKey per row, as expected

    B) ‘Product’[Test 2]=CALCULATE(ALL(’Product’[ProductKey])) ALSO yields the ProductKey per row, NOT AS EXPECTED.

    C) ‘Product’[Test 3]=CALCULATE(COUNTROWS(ALL(’Product’[ProductKey]))) however yields 2517 in all rows (size of the Product table) as expected.

    D) ‘Product’[Test 4]=CALCULATE(COUNTROWS(VALUES((’Product’[ProductKey])))) yields a 1 in all rows as expected.

    My question is, what is going on in ‘Product’[Test 2]? Since we are using ALL(), I would expect the operation to yield 2517 (the size of the unfiltered table) in all rows. Why doesn’t it? Why is the behavior different from what we see in C) and D) where the ALL does make a difference?

    Thank you very much

  4. Apologies. My previous post had a mistake. Please use this instead.

    Hi Rob,
    I have the following calculated columns in a ‘Product’ table (ProductKey has no duplicates)

    A) ‘Product’[Test 1]=CALCULATE(VALUES(’Product’[ProductKey])) yields the ProductKey per row, as expected
    B) ‘Product’[Test 2]=CALCULATE(ALL(’Product’[ProductKey])) ALSO yields the ProductKey per row, NOT AS EXPECTED.
    C)‘Product’[Test 3]=CALCULATE(COUNTROWS(ALL(’Product’[ProductKey]))) however yields 2517 in all rows (size of the Product table) as expected.
    D) ‘Product’[Test 4]=CALCULATE(COUNTROWS(VALUES((’Product’[ProductKey])))) yields a 1 in all rows as expected.

    My question is, what is going on in ‘Product’[Test 2]? Since we are using ALL(), I would expect the operation to yield an error for returning a table rather than a scalar. If the filter resulting from the context transition is overridden by the ALL() we would have multiple rows as a result of the CALCULATE. Why is the behavior here different from what we see in C) and D) where the use of ALL does result in different outcomes?

    Thank you very much

Leave a Comment or Question