skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile


 
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 original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

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