While rehearsing my demo for my upcoming session at SQL Saturday Boston this weekend, #sqlsat500 where I will be lecturing and demonstrating Scratching the Surface of Power View, I noticed a quirky issue where I wasn’t able to drill through my column data, but was fine with row data.  Further, after some playing, I was able to drill down if I changed the order of my column values, or tried using other fields.  Everyone hates NULL data, and I think this is just another reason why.  As it turns out, there are some NULLs that happen to show up as the first column when you drill down, so the ability drill “up” is lost, and the only way to get back to your top-level data is to close and re-open the report.

The Scenario:

Using the AdventureWorksDW2014 Data, and building a basic example of creating a matrix, then adding the Drill Down properties, my selections look like this:

As you can see.  A fairly simple example, just trying to capture the essence of Drilling Down and back out again.  When I click to drill down in to the “Black” column, I get the following:

Note, the first column heading has no title, and there isn’t an available “Drill Up” arrow either.  At this point, I’m stuck.  I can’t go back up to my original report, and now need to close and re-open to start over.  If I change the order of the columns so that “Style” is listed first, above “Color”, the Drill Down and Drill Up work fine.  In order to workaround the issue, I replaced all the NULLs in the table with “NA” and Viola! Works perfectly.  I’m not sure if this is by design from Microsoft in order to force the data to have values, but since it’s their dataset to begin with, I’m assuming it’s a bug.

Hope this helps anyone else running into this issue!