Category Archives: 17576

PowerPivot & DAX & Data Explorer BlogRoll – 31st March 2013

Microsoft sneaked a couple of ne

PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 31st March 2013.


Checklist for Memory Optimizations in PowerPivot and Tabular Models –

Beyond Rational
MS BI # 85 – PowerPivot #2 – Download , Install , Connect to SQL Server and Getting Started –

Bacon Bits
Use Data Explorer to Hack into Very Hidden Sheets –

readwrite enterprise
Microsoft’s Data Explorer: Picking Up Where Bing Leaves Off –

Matt Masson
Consuming CANSIM Datasets using Data Explorer –

Always Show Yesterday, Today, or Tomorrow’s Data –

Lightswitch For The Enterprise
Self Reporting via PowerPivot In Highly Deferred Execution Mode –

Data Explorer
Data Explorer Online Search Overview –

New Version of Data Explorer Released –

Katie & Emil Newsletter
Add-in Data Explorer and Excel tutorial –

BI Beginner: Data Explorer is a must have –

Gobán Saor
M’s back – Create a PowerPivot Time Dimension using Excel’s Data

Gobán Saor
The Model of a very modern PowerPivot – without the add-in –

PowerPivot & DAX & Data Explorer BlogRoll – 24th March 2013

Microsoft sneaked a couple of ne

PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 24th March 2013.


Romi’s Stream
Data Explorer for Excel: An experiment with Sachin Tendulkar –

Kasper de Jonge PowerPivot Blog
NBA team spending and their results with Excel, PowerPivot and Data
Stairway to PowerPivot and DAX – Level 4: The DAX BLANK() Function –

5 Cool Things You Can Do With Data

How I used Data Explorer to create a demo – < a href=””>

ISFILTERED: A Better Way to Detect Totals –

Brent Greenwood’s BI Blog
Pareto Chart with

Gobán Saor
[Updated]Look’s like Star Schema is back on the menu! –

Chris Webb’s BI Blog
UK/US Date Format Bug in PowerPivot and SSAS Tabular –

Romi’s Stream
Data Explorer for Excel: An experiment with Sachin Tendulkar –

Machlis Musings
New Excel Data Explorer add-in bolsters data analysis –

Creating Hierarchies in

PowerPivot & DAX & Data Explorer BlogRoll – 17th March 2013

Microsoft sneaked a couple of ne

PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 17th March 2013.


MS Office Blog
Learn about Data Explorer Formulas –

Javier Guillén Pervasive Business Intelligence
PivotStream and Mariner join forces to deliver PowerPivot solutions –

Chris Webb’s BI Blog
Finding Shakespeare’s Favourite Words With Data

Erik Svensen
How to use Data Explorer for Excel to extract data from Statistics Denmark –

A new build of Data Explorer, and an Auto Update feature –

SSIS Junkie
M Lives! –

Gobán Saor
DAX’s back – Create DAX Measures in Excel 2013 without PowerPivot Add-in –

Kasper de Jonge PowerPivot Blog
What is eating up my memory the PowerPivot / Excel edition –

Some Random Thoughts
Unpivoting Data in Data

Javier Guillén Pervasive Business Intelligence
Configuring “drill to details” behavior in PowerPivot: Part 1 –

PowerPivot on Airplanes and NBC –

PowerPivot & DAX & Data Explorer BlogRoll – 10th March 2013

Microsoft sneaked a couple of ne

PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 10th March 2013.


Extended Results
Calendar Analytics –

PowerPivot Discovers a New Form of Communication in the Animal World, Makes Headlines in Science! –

Matt Masson
Using Data Explorer with Power View –

Customer Feedback SQL Server Content Samples
Data Explorer: An introduction to table relationships and the Expand operation –

“Introducing” Data

Chris Webb’s BI Blog
Data Explorer–Where Does The Real Work Get Done? –

Dynamics NAV Financials
PowerPivot to the People –

A funny thing happened in Redmond… –

Gerhard Brueckl’s BI Blog
Another Post about Calculating New and Returning Customers – Part 2 –

Chris Webb’s BI Blog
Calling A Web Service From Data Explorer, Part 2 –

Vignesh C
Consume csv with Data

Matt Masson
Dynamic Lookups With Data

Some Random Thoughts
Introduction to Data Explorer Preview for Excel –

Dan English’s BI Blog
Installing Data Explorer Preview & Demo with IMDB Data –

Chris Webb’s BI Blog
Calling A Web Service From Data Explorer, Part 1 –

PowerPivot & DAX & Data Explorer BlogRoll – 3rd March 2013

Microsoft sneaked a couple of ne

PowerPivot & DAX & Data Explorer activity announced on Twitter for the week ending 3rd March 2013.


Chris Webb’s BI Blog
Importing Data From Multiple Log Files Using Data

Traversing the Facebook Graph using Data

Matt Masson
Access the Windows Azure Marketplace from Data

Altius Consulting
Best Oscar winning Film? My first Data Explorer adventure… –

Ian’s SharePoint Blog
SharePoint OData and the Excel Data

Chris Webb’s BI Blog
Public Preview of Data

Microsoft “Data Explorer” Preview for Excel –

Toggling Conditional Formatting On/Off via Slicer –

SQLBI – Marco Russo
Update to the PowerPivot for Excel 2013 licensing –

Microsoft Launches Updated Office 365 For Business, Adds ProPlus With Full Office Apps And New Small And Medium Business Versions –

Importing SQL Server Data from Multiple Data Sources into PowerPivot for Excel –

How to buy PowerPivot 2013, and the $30 Volume Licensing Workaround –

Combining Data from Multiple Relational Data Sources into One Table in PowerPivot for Excel –

DAX, The Dark Knight

Excel Is Superman, DAX Is The Dark Knight

Excel Formulas Are easy, So Why Is DAX So Hard?

Recently, Rob Collie, aka PowerPivotPro, posted a blog entry entitled ‘The Ballad of Ken Puls, DAX Convert’. Whilst it was an interestiung post, this is not to specifically offer any comment on that blog, or to stroke Ken’s ego <g>, I was intrigued by the part where Rob mentioned that Ken had said, and I quote,


“I firmly believe that PowerPivot is the future of Excel.  No question in my mind.  It’s insanely easy to get some killer BI in your hands with very little learning, which is awesome.  But…  What I don’t get is DAX.  The light just isn’t going on for me.  If you want to mobilize the Excel Pro army to really make this take off, DAX needs to become accessible for us.”


This rang a bell with me, as about six weeks ago I taught a 2 day PowerPivot course with Chris Webb to a class of 15 technology savvy people. These are smart people, but it was clear when covering the more advanced aspects of DAX that they were straining to grasp some of the concepts. On my journey home I was pondering about this. I have stated before that I believe that Microsoft over-egg the simplicity of PowerPivot, suggesting that any user can pick up PowerPivot and create fantastic BI immediately. I think that, whilst easy to get started, to really get the true potential out of PowerPivot you have to have some understanding of Data Modelling.

Have Microsoft got it wrong with DAX as well when they took a deliberate decision to make DAX Excel-like in its syntax? In many ways, this is understandable, their experience of MDX, which really is hard, could weigh heavily on their outlook. But, it has been a double-edged sword in my view. Excel users people are quite ready to give DAX a go because it looks as easy as Excel formulas, but conversely, when they get to advanced DAX (CALCULATE, FILTER, ALL, etc.), they can easily get dis-heartened because it is not as easy as Excel formulas.

So I thought I would look at why DAX is so hard for an Excel user. I came up with 7 topics that I decided to look at, namely:

  • Data Typed
  • Integer Dates
  • Formula Formatting
  • Evaluation Context
  • Extendible
  • Debugging Aids
  • Ease of use


Data Typed

Integer Dates

Formula Formatting


Debugging Aids

DAX is great if your formula works, but when it doesn’t you need good tools to help you debug it. Unfortunately. Microsoft haven’t provided any debugging tools whatsoever, so debugging is hard, incredibly hard. It should be part of our course, but with no tools to talk about there is nothing to suggest yet.

Just to complicate matters, the data usually consists of thousands, or even hundreds of thousands or millions, of rows, and reducing it to a reduced set is not staright-forward.

Marco Russo, Darren Gosbell, Angry Koala, and Paul te Braak have created a nice Excel addin called DAX Studio which is as good as it gets. it is a useful tool for building DAX formulae, as described by Javier Guillen in his post Using DAX studio to create PowerPivot measures, and this technique can be used to rebuild the formula from scratch checking as you go, but it is not reall a debugging tool, and was not meant as such.

Compare this to Excel. In Excel, the helpful debugging facilities are:

  • Evaluate Formula
  • Watch Window
  • F9


The Evaluate Formula facility lets you evaluate a formula in the activecell, stepping through it as each stage of the formula seeing what the result at that stage is, until it is fully resolved. It allows allows stepping in and out of the cell value if the expression being evaluated is a single cell.

The Watch Window allows you to add a cell or a range of cells to be watched, thw window showing the current value, reflecting any changes.

F9 allows you to select any expression within the formula bar and see what it resolves to. The expression can be a single cell, a function, or a set of functions, as long as it is an expression that can be resolved in its own right.

So, three facilties to assist in debugging the formula should make Excel formula debugging easy? In reality, I never use Evaluate Formula or the Watch Window as they do not provide enough value in my view. F9 is very useful, in that it is possible to evaluate any part of the formula. This is in contrast to Evaluate Formula, which evaluates the whole formula which means that the TRUE path is only evaluated, whereas with F9 you can select any part and have it evaluated regardless of whether the actual data would force the formula to follow that path or not.

Okay, Excel has a few tools, which is a few more than DAX, but they are hardly what you would call sophisticated. It still mainly relies on experience, intuition, and insight to be able to debug the more complex Excel formulae. Debugging DAX is diffocult, but debugging complex Excel formulae is not the easiest task either.

Evaluation Context

Evaluation context is probably the

Evaluation context is the aspect of DAX that people struggle with most when they start with PowerPivot. Understanding filter context and row context, when each comes into play, how you can override aspects of the filter context, all of this can seem mind boggling at first.

Ease of use

CUBEs For Dessert

Microsoft sneaked a couple of ne


A few weeks ago I showed how to create a fully dynamic table using CUBE formulae against a PowerPivot model in Cooking With Cubes.This example presented data from the AdventureWorks database of sales of product categories over years, all integrated with a Fiscal Year slicer.

I recently had a similar need, but with the further challenge of presenting two levels in a hierarchy (actually, this was related tables in a snowflake schema). I needed to show all of the items in the first level, and for each of these items, all of the associated items at the second level including those items that have no sales value. The AdventureWorks database has a similar setup with products, where each sale is for a particular product, each product is of specific product sub-category, and each product sub-category is of a specific product category. Within this model, we would be showing sub-categories of Mountain Bikes and Road Bikes against the category of bikes, but not against any other category.


I will demonstrate my solution using the same AdventureWorks data. Whilst this will be similar to the previous post in showing how to manage the presentation using Excel formulae, it is extended to multiple data levels and some more complex formatting. Additionally, and the main point of this post, I show a technique to display the related values in that hierarchy, not simple with straight CUBE formulae, by adding some MDX trickery in the CUBE formulae.

Figure 1 shows the final results of all of the formulae and formatting.

Figure 1 – Final Results

Preparing With Good Ingredients

First I need to prepare the PowerPivot model so that I have have product name, product sub-category name, and product category name all on the Product table. To achieve this, I added two calculated columns

Formula 1 – PowerPivot Model Calculated Columns

Mixing It Up

The data is ready, so let’s build the required data. I need the following:

  1. a text definition of the cube
  2. a set of all categories
  3. a count if the items in the category set
  4. a list of all of the items in the year set, from the slicer or the dimension if no slicer selections
  5. a count if the items in the year set
  6. a list of all of the items in the category set (in columns)
  7. for each listed category, a set of all associated sub-category items
  8. for each sub-category set, a count of all items in that set
  9. a running total of the sub-category set counts
  10. for each sub-category set, a list of items (in rows)
  11. a reference cell for the sales measure

Taking these in the order given, showing cell(s), defined name, definition:

1. Cube Definition

This is simple text for the connection argument to the cube functions.
J1:[_model]PowerPivot Data
I am using Excel 2010 for this example, Excel 2013 would use the value ThisWorkbookdataModel.
In the previous example, I named this cell _cube, here I use _model.

2. Category Set

This is the set of all categories in the model. This is being taken from the calculated column for product category name in the Products table.

3. Category Set Count

A simple count of the items in the category set, used in later dynamic formulae.
I subtract two from the actual count because this set contains the All member and blank, I am not interested in these.

4. Year Set

This is the set of years in the model, constrained by the slicer.
CUBESET(_model,Slicer_FiscalYear,”Slicer Years”),
CUBESET(_model,”[Date].[FiscalYear].Children”,”Dimension Years”))

As in the previous example, to show all years with no slicer selection (not show All), I check the slicer to see the first set member is All, getting the set from the Date dimsnsion if so, otherwise get the set from the slicer.

6. Year Set Count

A simple count of the items in the year set.

6. List of Category Set Items

A list of the items in the category set, for use in building the sub-category sets.
The name definition uses a dynamic formula
This is a simple dynamic OFFSET formula to build a list of the categories.

7. Sets of Subcategory Items

Sets of sub-category items, one set per category.
This is where the MDX magic kicks in. The EXISTS function … Returns the set of tuples of the first set specified that exist with one or more tuples of the second set specified …, so we can use it within a CUBESET function to get all of the product subcategory names for the category above, in _listCategory.
Using this technique, instead of building a set of all subcategories, we build a set of the subcategories for a particular category; in our case the category passed to the CUBESET function from the cells in _listCategory.
I prefix the text shown with the text Products: for readability
The name definition uses a dynamic formula

8. List of Subcategory Set Counts

A list of counts for the sub-category sets, for use in the layout.
The name definition uses a dynamic formula

9. List of Subcategory Set Count Totals

A list of running totals for the sub-category set counts, for use in the layout.
The name definition uses a dynamic formula

10. Lists of Sub-category Sets Items

Lists of each set sub-category items, within the relevant category.
This is using the same technique to list the set items as in the previous blog.
The name definition uses a dynamic formula
Unlike the other dynamic formulae, this one is multi-column, for the multiple categories.

11. Sales Measure

Finally, we have a cell defining the Sales measure
J7:[_sales]=CUBEMEMBER(_model,”[Measures].[Sum of SalesAmount]”,”Sales”)
This is to save repeated definition within the value formulae.

Figure 2 and Figure 3 below show all of the helper cells populated, and the nanmes defined in the NameManager.

Figure 2 – Populated Helper Cells

Figure 3 – Defined Names

It’s All About The Presentation

All that needs to be done now is to lay it out, allowing for a variable number of categories and a differing variable number of sub-categories within each category.

In my layout, I am repeating each category a number of times equal to to the number of subcategores for that category, but only showing the first instance. The years are shown as column headings, using the year slicer to filter the columns. The values are displayed referencing the category, sub-category, and year headings.

The formulae are as follows:

Category items

This matches the ordinal row number within the list of categories against the running totals for sub-category set counts, so as to determine which of the categories we are listing.


Sub-category items

This uses the same matching technique to determine which sub-category set to list.


Filtered Years

Although the slicer controls the selected years, the year set in _setYear outsorts the data, the filtering is done there.


Sales Totals

This determines the sum of the values for the current row, be that just a category row, or a category and sub-category row, whilst respecting the slicer selection. The IF in the formula caters for that possibility of a category row or a category and sub-category row.


Sales Values

Similar to the sales totals, but using the year heading instead of the slicer, beacuse the year heading has already take the slicewr into account.


Conditional formatting is added to the results to improve the viusual effect and readability.

Product Category Labels

The list of product categories is repeated on every row, changing at the appropriate point of course. This value is needed for the value calculation, but I feel the look is improved if the repeated items are suppressed, so I use conditional formatting to set the font colour to white on repeated items. This uses a simple formula of


Product Category Totals

I list the product category one more time that its associated sub-categories. This is so that I can show category totals as well as category and sub-category totals. These need highlighting so that the totals stand out. To achieve this, I have made them bold with a blue font, borders above and below. The conditional formatting formula for this is


But Is It All Too Rich?

One question that may occur to anyone reading this who is skilled with pivot tables is, why bother? Couldn’t the same effect be achieved by formatting a pivot table showing the product category and sub-category and years with the format set to Show Tabular Form and Repeat All Item Labels? This would give a similar look, but it would only show product categories and sub-categories that have a value, it would not show those that are not selling. Agreed, there is a pivot option to Show items with no data on rows which does address this, but the problem here is that it will show all product sub-categories under EVERY product category, my method only shows the relevant sub-categories.

In Summary

In many ways this is similar to the previous post on CUBE formulae, with an extra level of data and more complex formatting. But what I hope that it shows is that by utilising some fairly straightforward MDX the formulae can do far more complex operations, and extend the data displayed. And who knows how far this can be taken as one’s MDX skills increase?

It is possible that many of the formulas could be replaced with DAX measures in the model, maybe that is a post for another time.

You can download the workbook here


Allan Folting of Microsoft first showed me this technique, I am grateful for his insights.

Chris Webb has a series of excellent blog posts on MDX For PowerPivot Users, which are worth reading if this technique interests you.

PowerPivot BlogRoll – 6th January 2013

Microsoft sneaked a couple of ne

PowerPivot activity announced on Twitter for the week ending 6th January 2013.


Time To Name This Year’s Book! –

Database Pros in Demand –

[Commercial Webinar] Deep Dive into Business Intelligence in Excel 2013’s PowerPivot and Power View –

Creating Linked Tables in PowerPivot for Excel –

Excel 2013, Power View, Top 10 “long tail” and how DAX

PowerPivot BlogRoll – 30th December 2012

Microsoft sneaked a couple of ne

PowerPivot activity announced on Twitter for the week ending 30th December 2012.


Templated Excel workbooks with PowerPivot and Macro’s –

Importing SQL Server Data from Multiple Data Sources into PowerPivot for Excel –

PowerPivot – Importing Data from Different Data Sources into PowerPivot for Excel –

End of 2012 and news in 2013 for #PowerPivot, #ssas #tabular and BI –

Refreshing PowerPivot Data in SharePoint 2013 –

[Video] Using PowerPivot with DataMarket Section of Windows Azure Marketplace –